Oracle 执行计划（Explain Plan）说明

淑慧2672

　　如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。
      
看懂执行计划也就成了SQL优化的先决条件。这里的SQL优化指的是SQL性能问题的定位，定位后就可以解决问题。
 
 
一．         查看执行计划的三种方法

1.1 设置autotrace

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

 
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 |
--------------------------------------------------------------------------
 
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        609  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows 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 |
---------------------------------------------------------------------------------------------
 
统计信息
----------------------------------------------------------
         25  recursive calls
         12  db block gets
        168  consistent gets
          0  physical reads
          0  redo size
        974  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL>
 
1.3 使用Toad,PL/SQL Developer工具
 
 
二．         Cardinality（基数）/ rows
Cardinality值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。   在Oracle 9i中的执行计划中，Cardinality缩写成Card。在10g中，Card值被rows替换。
 
这是9i的一个执行计划，我们可以看到关键字Card：
       执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1Bytes=402)
   1    0   TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2 Card=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）。
 

　　 
　　    对于子查询，它的Cardinality将决定子查询是使用索引还是使用全表扫描的方式访问数据。