用EXPLAINPLAN分析SQL语句.doc

用EXPLAIN PLAN 分析SQL语句如何生成explain plan? 解答:运行utlxplan.sql. 建立plan 表针对特定SQL语句，使用 explain plan set statement_id = tst1 into plan_table运行utlxplp.sql 或 utlxpls.sql察看explain planEXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称. 你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行. NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理. 译者按: 通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便. 举例: SQL list 1 SELECT * 2 FROM dept, emp 3* WHERE emp.deptno = dept.deptno SQL set autotrace traceonly /*traceonly 可以不显示执行结果*/ SQL / 14 rows selected. Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE) Statistics - 0 recursive calls 2 db block gets 30 consistent gets 0 physical reads 0 redo size 2598 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed 通过以上分析,可以得出实际的执行步骤是: 1. TABLE ACCESS (FULL) OF EMP 2. INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE) 3. TABLE ACCESS (BY INDEX ROWID) OF DEPT 4. NESTED LOOPS (JOINING 1 AND 3) 注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们. -对于sql执行的小量高低.我们可以通过执行计划的信息基本上可以进行分析查看该SQL语句执行的时间.连接顺序及浪费的数据库资源等信息,从而判断该SQL语句执行的效率如何,下面就简单的介绍一下执行计划的使用2. Explain使用Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所 以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。2.1. 安装要使用执行计划首先需要执行相应的脚本。使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的SQL源程序，例如：ORA_RDBMS: XPLAINPL.SQL (VMS) $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)该脚本后会生成一个表这个程序会创建一个名为plan_table的表，表结构如下：我们简单的介绍一下主要的字段含义：字段名 字段类型 含义STATEMENT_ID VARCHAR2(30) explain PLAN 语句中所指定的最优STATEMENT_ID 参数值, 如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID，那么此值会被设为NULL。 REMARKS VARCHAR2(80) 与被解释规划的各步骤相关联的注释最长可达80 字节OPERATION VARCHAR2(30) 各步骤所执行内部操作的名称在某条语句所产生的第一行中该列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENTOPTIONS VARCHAR2(30) 对OPERATION 列中所描述操作的变种OBJECT_NODE VARCHAR2(128) 用于访问对象的数据库链接database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序OBJECT_OWNER VARCHAR2(30) 对于包含有表或索引的架构schema 给出其所有者的名称OBJECT_NAME VARCHAR2(30) 表或索引的名称OBJECT_INSTANCE INTEGER 根据对象出现在原始original 语句中的次序所给出的相应次序编号就原始的语句文本而论其处理顺序为自左至右自外向内景象扩张viewOBJECT_TYPE VARCHAR2(30) 用于提供对象描述性信息的修饰符例如索引的NON-UNIQUEOPTIMIZER VARCHAR2(255) 当前优化程序的模式ID INTEGER 分配给执行规划各步骤的编号PARENT_ID INTEGER 对ID 步骤的输出进行操作的下一个执行步骤的IDPOSITION INTEGER 对于具有相同PARENT_ID 的步骤其相应的处理次序COST INTEGER 根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值CARDINALITY INTEGER 根据基于开销的方法对操作所访问行数的估计值BYTES INTEGER 根据基于开销的方法对操作所访问字节的估计2.2. 使用2.2.1. 常规使用常规使用语法：explain PLAN SET STATEMENT_ID = INTO FOR 其中： STATEMENT_ID是一个唯一的字符串，把当前执行计划与存储在同一PLAN表中的其它执行计划区别开来。 TABLE_NAME是plan表名，它结构如前所示，你可以任意设定这个名称。 SQL_STATEMENT是真正的SQL语句。如：SQL explain plan set statement_id=test1 for 2 SELECT a.soctermbegin, 3 a.soctermend, 4 a.dealserialno, 5 a.levydataid, 6 a.dealtotal, 7 e.categoryitemcode, 8 row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRow 9 FROM tb_soc_packdealdata a,10 tb_Lvy_TaxDataBillMap c,11 Tb_lvy_BillData d,12 tb_soc_levydetaildata e13 WHERE a.levydataid = c.datafrompointer(+)14 AND c.billdataid = d.billdataid(+)15 AND a.levydataid = e.levydataid16 AND a.packdealstatuscode = 1017 AND (a.datastatus 9 OR a.datastatus is NULL)18 AND (d.billstatus IS NULL OR19 (d.billstatus 2 AND d.billstatus 8)20 AND a.Insurcode = 601095221 ;Explained执行下面语句就可以查看该语句执行的执行计划:SQL SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID 2 FROM PLAN_TABLE a 3 WHERE STATEMENT_ID=test1 4 ORDER BY Id;OPERATION OPTIONS OBJECT_NAME OBJECT_TYPEID PARENT_ID- - - -SELECT STATEMENT 0 WINDOW SORT 1 0FILTER 2 1NESTED LOOPS OUTER 3 2NESTED LOOPS OUTER 4 3NESTED LOOPS 5 4TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5INDEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 9TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3INDEX UNIQUE SCAN TBLVYBILLDATA_BILLDATAID UNIQUE 2.2.2. 自动显示使用在SQLPLUS中自动跟踪显示执行计划及相关信息SQLset timing on -显示执行时间SQLset autorace on C显示执行计划SQLset autorace on C显示执行计划SQLset autotrace traceonly C只显示执行计划即不显示查询出来的数据设置完毕后执行SQL语句就会显示执行计划信息及相应的统计信息(需要设置显示该选项)SQL select nvl(sum(t.taxdue), 0) 2 from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b 3 where a.dossiercode = SB02041108 4 and a.pages = 123 5 and a.remarkid = b.remarkid 6 AND A.REMARKID IS NOT NULL 7 and b.declaredocid = t.declaredocid;NVL(SUM(T.TAXDUE),0)- 0已用时间: 00: 00: 04.07Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (Cost=6 Card=1 Bytes=110) 3 2 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=74) 4 3 TABLE ACCESS (FULL) OF TB_LVY_SBZS100 (Cost=2 Card =1 Bytes=31) 5 3 BUFFER (SORT) (Cost=2 Card=1 Bytes=43) 6 5 TABLE ACCESS (FULL) OF TB_LVY_DECLAREDOC (Cost=2 Card=1 Bytes=43) 7 2 TABLE ACCESS (BY INDEX ROWID) OF TB_LVY_DECLAREDOC (Cost=2 Card=1 Bytes=36) 8 7 INDEX (UNIQUE SCAN) OF TBLVYDECLAREDOC_DECLAREDOCID (UNIQUE)Statistics- 0 recursive calls -循环递归次数 0 db block gets请求的数据块在buffer能满足的个数 6675 consistent gets -逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中 45 physical reads C从磁盘读到Buffer Cache数据块数量 0 redo size C产生的redo日志大小 217 bytes sent via SQL*Net to client 276 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (