分布式数据库系统原理中的查询过程与通过Oracle执行计划推测其优化策略.docx

分布式数据库系统原理中的查询过程一、定义与说明1 优化为了减少优化消耗,查询过程的优化的目标为得到一个较优解,而不是最优解.其中解为一个消耗较小的关系运算序列或操作序列.2片段片段是指被分散并保存在不同位置的(全局)关系的子集(局部关系集合)二、查询优化过程1 查询语句分解1.1 解析查询语句1.2 判断查询语句语义正确性1.3 消除重复出现的谓词1.4 将查询语句重构为较优的关系运算序列2 数据定位2.1 将分布式(全局)查询映射为片段查询2.2 简化和重构碎片上的查询，优化片段查询3 全局查询优化3.1 结合片段上的信息(包括所查询的属性元素数等)进行优化,得到较优片段查询操作与通讯操作的序列。其中很重要的一方面则是联结操作的执行顺序。很多现代分布式数据库都使用半联结的方式进行优化。3.2 半联结：只将表中那些将会实际参与联结的元组进行传输，并在站点上对这些元组和相关表进行联结操作。4 局部查询优化每个站点上针对本地查询进行优化。通过Oracle执行计划推测优化策略一、测试前提：1 如何查看Oracle执行计划第一步：登入sql/plus 执行命令（无先后顺序） set time on; (说明：打开时间显示） set autotrace traceonly; (说明：打开自动分析统计，不显示SQL语句的运行结果)。第二步：输入你要查看的sql 执行第三步：查看结果2 用户、库表说明2.1 用户1用户名：XUQIUORG004密码：XUQIU2.2 用户2用户名：需求分析定制网站 ORACLEDB_15密码：XUQIU2.3 用户3用户名：PADISTESTORAGS7_密码：apple2.3 库表说明用户1、用户2所对应的库都是需求库且具有相同的库表结构，且每个对应的表中具有相同的数据;用户3所对应的库为仿真库。二、测试过程1 登录SQL/PLUS、开启执行计划并创建DBLINK1.1 SQL/PLUS记录Microsoft Windows XP 版本 5.1.2600(C) 版权所有 1985-2001 Microsoft Corp.C:Documents and SettingsAdministratorsqlplusSQL*Plus: Release .0 - Production on 星期二 1月 18 14:45:04 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.请输入用户名: xuqiuorg004输入口令:连接到:Oracle9i Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release .0 - ProductionSQL set time on;14:45:35 SQL set autotrace traceonly;14:45:46 SQL create database link mopishv0link connect to 需求分析定制网站 identified by XUQIU usingORACLEDB_15;数据库链接已创建。14:47:52 SQL select * from WORK_INFOmopishv0link;已选择1161行。执行计划- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF WORK_INFO ORACLEDB .US.ORAC LE.COM统计信息- 0 recursive calls 1 db block gets 0 consistent gets 0 physical reads 212 redo size 172850 bytes sent via SQL*Net to client 1219 bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1161 rows processed15:35:37 SQL create database link mopishv1link connect to PADISTEST identified by apple usingORAGS7_;数据库链接已创建。16:44:09 SQL select * from ACTIVITYmopishv1link;已选择452行。执行计划- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF ACTIVITY ORAGS7.U S.ORACLE .COM2 有条件查询本地库表2.1 SQL/PLUS记录14:48:18 SQL select work_id from WORK_RULE_INFO where RULE_LEV=操作级;执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF WORK_RULE_INFO统计信息- 0 recursive calls 0 db block gets 32 consistent gets 0 physical reads 0 redo size 378 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed14:49:43 SQL2.2 记录分析由TABLE ACCESS (FULL) OF WORK_RULE_INFO我们可以看到在对本地查询的时候虽然有条件限制，但也没有做过多的优化而是直接访问整个WORK_RULE_INFO表。3 无条件查询远程库表3.1 SQL/PLUS记录14:49:43 SQL select work_id from WORK_RULE_INFOmopishv0link;已选择1161行。执行计划- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF WORK_RULE_INFO ORACLEDB .US.ORAC LE.COM统计信息- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 18183 bytes sent via SQL*Net to client 1219 bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1161 rows processed14:53:57 SQL3.2 记录分析与3.1比较可以发现，访问远程库表时，会在操作后加入远程数据库的全局数据库名(Global Database Name)表示操作发生在远程数据库服务上。4 不同库中的不同表的表间查询4.1 SQL/PLUS记录115:02:39 SQL select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFOmopishv0link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and RULE_LEV=操作级;执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 TABLE ACCESS (BY INDEX ROWID) OF WORK_RULE_INFO 4 3 INDEX (UNIQUE SCAN) OF WORK_RULE_INFO_PK (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT WORK_ID FROM WORK_INFO WORK_INFO 统计信息- 0 recursive calls 0 db block gets 3118 consistent gets 0 physical reads 0 redo size 390 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1 rows processed4.2 SQL/PLUS记录215:09:21 SQL select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFOmopishv0link where RULE_LEV=操作级 and WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID;执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 TABLE ACCESS (BY INDEX ROWID) OF WORK_RULE_INFO 4 3 INDEX (UNIQUE SCAN) OF WORK_RULE_INFO_PK (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT WORK_ID FROM WORK_INFO WORK_INFO 统计信息- 0 recursive calls 0 db block gets 3118 consistent gets 0 physical reads 0 redo size 390 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1 rows processed4.2 SQL/PLUS记录315:10:12 SQL select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFOmopishv0link where RULE_LEV=操作级 and WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and RULE_LEV=操作级;执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 TABLE ACCESS (BY INDEX ROWID) OF WORK_RULE_INFO 4 3 INDEX (UNIQUE SCAN) OF WORK_RULE_INFO_PK (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT WORK_ID FROM WORK_INFO WORK_INFO 统计信息- 0 recursive calls 0 db block gets 3118 consistent gets 0 physical reads 0 redo size 390 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed15:24:25 SQL4.4 记录分析 1通过对比4.1、4.2与4.3我们可以发现条件的颠倒与重复都对执行计划没有影响，这说明在联结之前Oracle会对查询进行优化。这点符合分布式数据库系统原理中所描述的查询语句分解的特点。4.5 SQL/PLUS记录415:24:25 SQL select WORK_INFO.WORK_NAME from WORK_RULE_INFO,WORK_INFOmopishv0link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID;已选择1161行。执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 INDEX (UNIQUE SCAN) OF WORK_RULE_INFO_PK (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT WORK_ID,WORK_NAME FROM WORK_INFO WORK_INFO统计信息- 0 recursive calls 0 db block gets 1240 consistent gets 0 physical reads 0 redo size 32377 bytes sent via SQL*Net to client 1219 bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1161 rows processed15:35:37 SQL4.6 记录分析 2通过对比4.1、4.2、4.3与4.5可以发现，Oracle只从远程库表中查询参与联结的元组，而不是查询所有列。这与分布式数据库系统原理中所描述的半联结相符。4.7 SQL/PLUS记录516:57:00 SQL select WORK_INFO.WORK_NAME,FLOWNAMES2.FLOW_NAME from WORK_RULE_INFO,WORK_INFOmopishv0link,FLOWNAMES2mopishv1link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and FLOWNAMES2.WORK_ID=WORK_RULE_INFO.WORK_ID and trim(COMPANY_CODE)=高法;已选择327行。执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 NESTED LOOPS 4 3 REMOTE* MOPISHV1 LINK.US. ORACLE.C OM 5 3 INDEX (UNIQUE SCAN) OF WORK_RULE_INFO_PK (UNIQUE) 6 1 SORT (JOIN) 7 6 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 4 SERIAL_FROM_REMOTE SELECT COMPANY_CODE,FLOW_NAME,WORK_ID FROM FLOWNAMES2 FLOWNAMES2 WHERE 7 SERIAL_FROM_REMOTE SELECT WORK_ID,WORK_NAME FROM WORK_INFO WORK_INFO统计信息- 0 recursive calls 0 db block gets 329 consistent gets 0 physical reads 0 redo size 27050 bytes sent via SQL*Net to client 603 bytes received via SQL*Net from client 46 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 327 rows processed17:30:08 SQL4.8 SQL/PLUS记录617:31:33 SQL select WORK_INFO.WORK_NAME,FLOWNAMES2.FLOW_NAME from WORK_RULE_INFO,WORK_INFOmopishv0link,FLOWNAMES2mopishv1link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and FLOWNAMES2.WORK_ID=WORK_RULE_INFO.WORK_ID and WORK_INFO.SUBSYS_ID=55259;执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 NESTED LOOPS 4 3 REMOTE* MOPISHV1 LINK.US. ORACLE.C OM 5 3 INDEX (UNIQUE SCAN) OF WORK_RULE_INFO_PK (UNIQUE) 6 1 SORT (JOIN) 7 6 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 4 SERIAL_FROM_REMOTE SELECT FLOW_NAME,WORK_ID FROM FLOWNAMES 2 FLOWNAMES2 7 SERIAL_FROM_REMOTE SELECT WORK_ID,WORK_NAME,SUBSYS_ID FRO M WORK_INFO WORK_INFO WHERE SUB统计信息- 0 recursive calls 0 db block gets 329 consistent gets 0 physical reads 0 redo size 814 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 5 rows processed17:32:12 SQL4.9 记录分析 3通过对比4.7与4.8可以发现：Oracle并没有优先执行可以减少数据传输与联结记录数的查询。例如4.8中并没有优先执行WORK_INFO上的查询。而从WHERE字句可以发现，Oracle并没有利用之前查询所得到的结果。这有两种可能：1 优化得到的是较优的结果而不是最优的，因此没有利用之前查询所得的结果。2 Oracle的优化不会动态利用查询所得到的结果。4.10 SQL/PLUS记录717:30:08 SQL select WORK_INFO.WORK_NAME,FLOWNAMES2.FLOW_NAME from WORK_RULE_INFOmopishv0link,WORK_INFOmopishv0link,FLOWNAMES2mopishv1link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and FLOWNAMES2.WORK_ID=WORK_RULE_INFO.WORK_ID and trim(COMPANY_CODE)=高法;已选择327行。执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 REMOTE* MOPISHV1 LINK.US. ORACLE.C OM 5 2 SORT (JOIN) 6 5 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 7 1 SORT (JOIN) 8 7 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 4 SERIAL_FROM_REMOTE SELECT COMPANY_CODE,FLOW_NAME,WORK_ID FROM FLOWNAMES2