Oracle 执行计划（Explain Plan） 说明
|
如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。 看懂执行计划也就成了SQL优化的先决条件。这里的SQL优化指的是SQL性能问题的定位，定位后就可以解决问题。 一．查看执行计划的三种方法 1.1设置autotrace SQL>set autotrace on SQL>select * from dave; ID NAME ---------- ---------- 8安庆 1 dave 2 bl 1 bl 2 dave 3 dba 4 sf-express 5 dmm 已选择8行。 执行计划 ---------------------------------------------------------- Plan hash value: 3458767806 -------------------------------------------------------------------------- | Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------- |0 | SELECT STATEMENT||8 |64 |2(0)| 00:00:01 | |1 |TABLE ACCESS FULL| DAVE |8 |64 |2(0)| 00:00:01 | -------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0recursive calls 0db block gets 4consistent gets 0physical reads 0redo size 609bytes sent via SQL*Net to client 416bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 8rows processed SQL> 1.2使用SQL SQL>EXPLAIN PLAN FOR sql语句; SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 示例： SQL>EXPLAIN PLAN FOR SELECT * FROM DAVE; 已解释。 SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 或者： SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3458767806 -------------------------------------------------------------------------- | Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------- |0 | SELECT STATEMENT||8 |64 |2(0)| 00:00:01 | |1 |TABLE ACCESS FULL| DAVE |8 |64 |2(0)| 00:00:01 | -------------------------------------------------------------------------- 已选择8行。 执行计划 ---------------------------------------------------------- Plan hash value: 2137789089 -------------------------------------------------------------------------------- | Id| Operation| Name| Rows| Bytes | Cost (%CPU)| Time| --------------------------------------------------------------------------------------------- |0 | SELECT STATEMENT||8168 | 16336 |29(0)| 00:00:01 | |1 |COLLECTION ITERATOR PICKLER FETCH| DISPLAY |8168 | 16336 |29(0)| 00:00:01 | --------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 25recursive calls 12db block gets 168consistent gets 0physical reads 0redo size 974bytes sent via SQL*Net to client 416bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 8rows processed SQL> 1.3使用Toad,PL/SQL Developer工具 二．Cardinality（基数）/ rows Cardinality值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。在Oracle 9i中的执行计划中，Cardinality缩写成Card。在10g中，Card值被rows替换。 这是9i的一个执行计划，我们可以看到关键字Card： 执行计划 ---------------------------------------------------------- 0SELECT STATEMENT Optimizer=CHOOSE (Cost=2Card=1Bytes=402) 10TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2Card=1Bytes=402) Oracle 10g的执行计划，关键字换成了rows： 执行计划 ---------------------------------------------------------- Plan hash value: 2137789089 -------------------------------------------------------------------------------- | Id| Operation| Name|Rows| Bytes | Cost (%CPU)| Time| --------------------------------------------------------------------------------------------- |0 | SELECT STATEMENT||8168 | 16336 |29(0)| 00:00:01 | |1 |COLLECTION ITERATOR PICKLER FETCH| DISPLAY |8168 | 16336 |29(0)| 00:00:01 | --------------------------------------------------------------------------------------------- Cardinality的值对于CBO做出正确的执行计划来说至关重要。如果CBO获得的Cardinality值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致CBO错误的制定出执行计划。 在多表关联查询或者SQL中有子查询时，每个关联表或子查询的Cardinality的值对主查询的影响都非常大，甚至可以说，CBO就是依赖于各个关联表或者子查询Cardinality值计算出最后的执行计划。 对于多表查询，CBO使用每个关联表返回的行数（Cardinality）决定用什么样的访问方式来做表关联（如Nested loops Join或hash Join）。 多表连接的三种方式详解HASH JOIN MERGE JOIN NESTED LOOP http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx 对于子查询，它的Cardinality将决定子查询是使用索引还是使用全表扫描的方式访问数据。 三．SQL的执行计划 生成SQL的执行计划是Oracle在对SQL做硬解析时的一个非常重要的步骤，它制定出一个方案告诉Oracle在执行这条SQL时以什么样的方式访问数据：索引还是全表扫描，是Hash Join还是Nested loops Join等。比如说某条SQL通过使用索引的方式访问数据是最节省资源的，结果CBO作出的执行计划是全表扫描，那么这条SQL的性能必然是比较差的。 Oracle SQL的硬解析和软解析 http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx 示例： SQL>SET AUTOTRACE TRACEONLY;--只显示执行计划，不显示结果集 SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr; 已选择13行。 执行计划 ---------------------------------------------------------- Plan hash value: 992080948 --------------------------------------------------------------------------------------- | Id| Operation| Name| Rows| Bytes | Cost (%CPU)| Time| --------------------------------------------------------------------------------------- |0 | SELECT STATEMENT||13 |988 |6(17)| 00:00:01 | |1 |MERGE JOIN||13 |988 |6(17)| 00:00:01 | |2 |TABLE ACCESS BY INDEX ROWID| EMP|14 |532 |2(0)| 00:00:01 | |3 |INDEX FULL SCAN| PK_EMP |14 ||1(0)| 00:00:01 | |*4 |SORT JOIN||13 |494 |4(25)| 00:00:01 | |*5 |TABLE ACCESS FULL| EMP|13 |494 |3(0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."EMPNO"="B"."MGR") filter("A"."EMPNO"="B"."MGR") 5 - filter("B"."MGR" IS NOT NULL) 统计信息 ---------------------------------------------------------- 0recursive calls 0db block gets 11consistent gets 0physical reads 0redo size 2091bytes sent via SQL*Net to client 416bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 13rows processed SQL> 图片是Toad工具查看的执行计划。在Toad里面，很清楚的显示了执行的顺序。但是如果在SQLPLUS里面就不是那么直接。但我们也可以判断：一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。 3.1执行计划中字段解释： ID:一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。 Operation：当前操作的内容。 Rows：当前操作的Cardinality，Oracle估计当前操作的返回结果集。 Cost（CPU）：Oracle计算出来的一个数值（代价），用于说明SQL执行的代价。 Time：Oracle估计当前操作的时间。 3.2谓词说明： Predicate Information (identified by operation id): --------------------------------------------------- 4 -access("A"."EMPNO"="B"."MGR") filter("A"."EMPNO"="B"."MGR") 5 -filter("B"."MGR" IS NOT NULL) Access:表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。 Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。 在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。 3.3统计信息说明： dbblockgets：从buffercache中读取的block的数量 consistentgets：从buffercache中读取的undo数据的block的数量 physicalreads：从磁盘读取的block的数量 redosize：DML生成的redo的大小 sorts(memory)：在内存执行的排序量 sorts(disk)：在磁盘上执行的排序量 （编辑：鄂州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
