查看正确的执行计划

【摘要】

       执行计划显示在执行一条SQL语句时需要遵循的详细步骤，这些步骤表示为一组数据库运算符，这些运算符将使用和生成行；运算符及其实施顺序由优化器使用查询转换及物理优化技术的组合来确定。

       执行计划通常以表格的形式显示，但它实际上为树形。

【正文】

如何获得正确的执行计划

获取执行计划有多种方式，但大部分不能得到正确的实际的执行计划，因为SQL语句必须过以后才能得到正确的执行计划，下面展示获得执行计划的方式。

Explain plan 命令

Explain plan for SQL语句结合

Select * from table(dbms_xplan.display);  --不能获得正确的执行计划

实例：

Explain plan for select * from dba_tables;

Select * from table(dbms_xplan.display);

DBMS_XPLAN包

Select * from table(dbms_xplan.display);  --参考a)

Select * from table(dbms_xplan.display_cursor(null,null,’advanced’));

--执行完一条语句后，紧接着执行当条语句，就能得到真实的执行计划

Select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,child_cursor_number,’advanced’));

--通过v$sql视图可以得到sql_id/hash_value和child_cursor_number(对应v$sql视图中的child_number)，此语句可得到真实的执行计划

Select * from table(dbms_xplan.display_awr(‘sql_id’));

--从AWR中拿到真实的执行计划

示例：

Set linesize 500

set termout off

1、alter session set statistics_level = all;

2、执行SQL语句

3、查看执行计划

       select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

SQLPLUS的AUTOTRACE开关

Set autotrace on/off 简写 set autot on/off

--返回结果及完整执行计划

Set autotrace traceonly 简写 set autot trace

--不返回结果，只返回完整执行计划

Set autotrace explain 简写 set autot exp

--不返回结果，返回的执行计划不包括统计信息

Set autotrace statistics 简写 set autot stat

--不返回结果，只返回执行计划的统计信息

10046事件（两种方式）

启用

Alter session set events ‘10046 trace name context forever,level 12’;

执行sql语句

关闭

Alter session set events ‘10046 trace name context off’;

获得trace文件路径

Select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));

启用

Oradebug setmypid  --表示准备对当前session使用oradebug命令

Oradebug event 10046 trace name context forever,level 12;

执行sql语句

关闭

Oradebug event 10046 trace name context off;

Oradebug tracefile_name  --获得trace文件路径

查看trace文件

Tkprof trace文件路径 新生成文件路径

示例：

oradebug setospid 33804
oradebug unlimit 
oradebug event 10046 trace name context forever,level 12;
oradebug tracefile_name
oradebug event 10046 trace name context off

10053事件

--当SQL语句走了错误的执行计划，并且找不到原因时，可以使用10053事件；此事件时用来诊断优化器如何估算成本和选择执行计划的，用它产生的trace文件提供了Oracle如何选择执行计划，为什么得到这样的执行计划

具体用法：

启用

Alter session set events=’10053 trace name context forever,level 1’;

执行sql语句

关闭

Alter session set events ‘10053 trace name context off’;

注意点：

1. 不能使用tkprof格式化工具查看trace文件
2. Sql语句必须被硬解析并且使用CBO优化器模式

参考：

http://www.itpub.net/thread-1766504-1-1.html

http://www.itpub.net/thread-1766506-1-1.html

AWR报告或者Statspack报告

读懂执行计划

先展示一组简单的执行计划；

       ID Operation                            Name

------------------------------------------
0 SELECT STATEMENT
1  HASH GROUP BY
2    HASH JOIN
3     TABLE ACCESS FULL            PRODUCTS
4     PARTITION RANGE ALL
5      TABLE ACCESS FULL           SALES
-------------------------------------------

根据崔华在《基于Oracle的SQL优化》中写到的内容，正确查看执行计划的顺序应为：先从最开头一直连续往右看，直到看到最右边并列的地方；对于不并列的，靠右的先执行；如果看到并列的，就从上往下看，对于并列的部分，靠上的先执行。

因此上例中的执行计划正确顺序为：

3>5>4>2>1>0

【总结】

       正确的获得并读懂执行计划是优化语句的第一步，加油吧。