Oracle性能优化之Oracle里的执行计划

原创

wcwen1990 2021-08-06 16:16:40 博主文章分类：Oracle ©著作权

文章标签 Oracle性能优化原创文章 sql 执行计划 oracle数据库 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者wcwen1990的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、执行计划

执行计划是目标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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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的执行计划。