Oracle里的执行计划

目录

一、执行计划

二、如何查看oracle数据库的执行计划

1、explain plan命令

2、dbms_xplan包

3、sqlplus的autotrace开关

4、10046事件

三、如何得到真实的执行计划

---

一、执行计划

执行计划是目标SQL在oracle数据库中具体的执行步骤，oracle用来执行目标SQL语句的具体执行步骤的组合被称为执行计划。

二、如何查看oracle数据库的执行计划

oracle数据库中常用的取得目标SQL语句执行计划的方法有以下几种：

（1）explain plan命令

（2）dbms_xplan包

（3）sqlplus中的autotrace开关

（4）10046事件

1、explain plan命令

explain plan命令具体语法：

（1）explain plan for+目标SQL

（2）select * from table(dbms_xplan.display);

例：

SQL> explain plan for select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
​
Explained.
​
SQL> select * from table(dbms_xplan.display);
​
PLAN_TABLE_OUTPUT
​
\--------------------------------------------------------------------------------------------------------------
​
Plan hash value: 844388907
​
\----------------------------------------------------------------------------------------
​
| Id  | Operation                | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
​
\----------------------------------------------------------------------------------------
​
|  0 | SELECT STATEMENT        |        |   14 |  364 |   6 (17)| 00:00:01 |
​
|  1 |  MERGE JOIN             |        |   14 |  364 |   6 (17)| 00:00:01 |  
​
|  2 |  TABLE ACCESS BY INDEX ROWID | DEPT   |   4 |   52 |   2  (0)| 00:00:01 |
​
|  3 |   INDEX FULL SCAN         | PK_DEPT |   4 |    |   1  (0)| 00:00:01 |
​
|*  4 |  SORT JOIN             |        |   14 |  182 |   4 (25)| 00:00:01 |
​
|  5 |   TABLE ACCESS FULL       | EMP   |   14 |   182 |   3    (0)| 00:00:01 |
​
PLAN_TABLE_OUTPUT
​
\---------------------------------------------------------------------------------------------------------------
​
Predicate Information (identified by operation id):
​
\---------------------------------------------------
​
  4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
​
•    filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
​
18 rows selected.

oracle 10g及其以上版本中，如果我们对目标SQL执行explain plan命令，则oracle将解析目标SQL的执行计划写入plan_table$表中，在执行select * from table(dbms_xplan.display);只是将plan_table$表中的执行计划格式化显示出来而已。plan_table$是一个on commit preserver rows的global temporary table，所以这里的oracle的每个session只能看到自己的执行计划并且互不干扰。

SQL> select operation,options,object_name,id,cardinality,cost from sys.plan_table$;
​
OPERATION      OPTIONS                OBJECT_NAME     ID  CARDINALITY    COST
​
-------------------- --------------------------------------------- ------------------------------ ---------- ----------- --------------------------------
​
SELECT STATEMENT                                              0  14        6
​
MERGE JOIN                                                1  14        6
​
TABLE ACCESS       BY INDEX ROWID                 DEPT    2   4        2
​
INDEX          FULL SCAN                      PK_DEPT     3   4        1
​
SORT           JOIN                                   4  14        4
​
TABLE ACCESS       FULL                     EMP       5  14        3
​
6 rows selected.

2、dbms_xplan包

dbms_xplan包根据不同的应用场景主要有以下4种方法：

（1）select * from table(dbms_xplan.display); --结合explain plan命令使用，不再介绍。

（2）select * from table(dbms_xplan.display_cursor(null,null,'advanced/all')); --可以查看刚刚执行过得sql。

（3）select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced/all')); --可以查看还在缓存中的所有sql的执行计划。要结合v$sql或v$sqlarea视图进行目标sql信息定位使用。

（4）select * from table(dbms_xplan.display_cursor('sql_id')); --可以查看不再缓存中的sql执行计划，不包括谓语信息。即查看历史执行计划。

3、sqlplus的autotrace开关

autotrace开关可以在sqlplus下得到目标sql的执行计划，也可以同时得到目标sql的统计信息。autotrace开关的具体语法如下：

set autotrace {off|on|traceonly} [explain] [statistics]

具体使用方法如下：

（1）set autotrace on：显示目标sql的执行结果，执行计划和统计信息。

（2）set autotrace off：只显示目标sql的执行结果，为默认值。

（3）set autotrace traceonly：显示执行结果数量，执行计划和统计信息。

（4）set autotrace traceonly explain:只显示目标sql的执行计划。

（5）set autotrace tranceonly statistics：只显示目标sql的执行结果数量和统计信息。

4、10046事件

这里不做介绍，详见10046事件文档。

三、如何得到真实的执行计划

在上一节一共介绍了4中取得执行计划的方法，这4种方法中除了10046事件外，其他方法取得的执行计划都有可能是不准确的。在oracle数据库中，判断执行计划是否是真实的，就看目标SQL是否被真正执行过，真正执行过得目标sql所对应的执行计划就是准确的，反之就不然。

（1）explain plan命令取得的执行计划可能是不真实的，因为explain plan命令并没有真实的执行过目标SQL.

（2）dbms_xplan包，对于以下4种方法，第一种方法因为与explain plan联合使用，目标sql并没有真正的执行过，所以取得的执行计划是不准确的。其他3种方法目标sql都已经真正的执行过了，所以取得的执行计划是准确的。

1）select * from table(dbms_xplan.display);

2）select * from table(dbms_xplan.display_cursor(null,null,'advanced/all'));

3）select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced/all'));

4）select * from table(dbms_xplan.display_cursor('sql_id'));

（3）sqlplus的autotrace开关，对于autotrace开关而言，采用如下3种方式取得目标sql的执行计划：

1）set autotrace on

2）set autotrace traceonly

3）set autotrace traceonly explain

上述3种方法中，前2种方法已经真实的执行过了目标sql，所以取得的执行计划是准确的，对于set autotrace traceonly explain而言，当目标sql为select语句时，目标sql没有真正的执行，所以所得到的执行计划可能是不准确的，当目标sql为dml语句时，dml语句真实的执行过了，所以取得的执行计划是准确的。

这里需要特殊说明的是：虽然使用部分set autotrace命令后目标sql实际上已经执行过了，但所有使用了set autotrace命令（包括set autotrace on、set autotrace traceonly、set autotrace traceonly explain）所得到的执行计划有可能都是不准确的，因为使用set autotrace所取得的执行计划都取自于explain plan命令。

（4）结论

在进行sql优化过程中，建议使用

select * from table(dbms_xplan.display_cursor(null,null,'advanced/all'));

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced/all'));

这2种方法来查看待优化sql的执行计划。