oracle 执行计划

• 解释计划

 

      语句EXPLAIN PLAN用来显示优化器为SQL语句所选择的执行计划。需要说明的是当你得到了解释计划输出的时候，你其实是得到了当SQL语句执行的时候应该采用的预期执行计划。你并没有得到实际的执行计划以及与其相关的数据源执行统计信息。你所得到的只是估计值，而不是实际值。将估计的信息称为解释计划输出 ，而把实际信息称为执行计划输出 来区分实际和预期的执行计划。

 

解释计划例子：

 

SQL> explain plan for
  2  select * from small_customers;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1819012793
--------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     6 |   156 |     3   (0)| 00:00
|   1 |  TABLE ACCESS FULL| SMALL_CUSTOMERS |     6 |   156 |     3   (0)| 00:00
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
 
12 rows selected

 

缩进格式：

select id, parent_id, operation
  from (select level lvl,
               id,
               parent_id,
               lpad(' ', level) || operation || ' ' || options || ' ' ||
               object_name as operation
          from plan_table
         start with id = 0
        connect by prior id = parent_id)
 order by lvl desc, id;

 

      解释计划输出最令人郁闷的地方就是它与语句实际执行时所使用的计划可能是不一致的。使用解释计划的时候以下3点可能导致计划输出与实际执行计划不一致的地方：

1. 解释计划是基于你使用它的时候的环境来生成的
2. 解释计划不考虑绑定变量的数据类型（所有的绑定变量都是VARCHAR2的）
3. 解释计划不"窥视"绑定变量的值

      对于解释计划来说，数据类型都被认为是一样的（都是字符串）。然而，当语句真正执行的时候所准备的执行计划却要考虑数据类型，ORACLE隐式地将字符串数据类型转换为数值类型来匹配绑定变量的数据类型。

 

      如何学会阅读计划：

1. 学会识别和分割父子组
2. 掌握计划中运算执行的顺序
3. 学会以途述的形式来阅读计划

按照执行顺序显示的计划运算：

explain plan for select * from test_1;
select * from table(dbms_xplan.display);
select id, parent_id, operation
  from (select level lvl,
               id,
               parent_id,
               lpad(' ', level) || operation || ' ' || options || ' ' ||
               object_name as operation
          from plan_table
         start with id = 0
        connect by prior id = parent_id)
 order by lvl desc, id;

 

• 执行计划

 

      当一条SQL语句执行的时候将会生成该语句的实际执行计划。在语句被硬解析之后，所选的执行计划会被存到库高速缓存中以便以后重用。可以通过查询V$SQL_PLAN来查看计划运算。

select sql_id from v$sql where sql_text like '%TEST%';
select * from v$sql_plan where sql_id='7dfcmhhhxjspn'  and child_number=0;

或者用以下语句来代替v$sql_plan 
select * from table(dbms_xplan.display_cursor('7dfcmhhhxjspn',null,'ALLSTATS LAST'));

也可以通过一条语句：
SELECT * FROM TEST;
SELECT XPLAN.*
  FROM (SELECT MAX(SQL_ID) KEEP(DENSE_RANK LAST ORDER BY LAST_ACTIVE_TIME) SQL_ID,
               MAX(CHILD_NUMBER) KEEP(DENSE_RANK LAST ORDER BY LAST_ACTIVE_TIME) CHILD_NUMBER
          FROM V$SQL
         WHERE UPPER(SQL_TEXT) LIKE '%SELECT * FROM TEST%'
           AND UPPER(SQL_TEXT) NOT LIKE
               '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %') SQLINFO,
       TABLE(DBMS_XPLAN.display_cursor(SQLINFO.SQL_ID,
                                       SQLINFO.CHILD_NUMBER,
                                       'ALLSTATS LAST')) XPLAN;