Oracle 执行计划（Explain Plan）说明

如果要分析某条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=1 Bytes=402)

   1    0   TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2 Card=1 Bytes=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 将决定子查询是使用索引还是使用全表扫描的方式访问数据。