获取执行计划（查看执行计划）的方法、SQL*Trace、10046、10053

常见的获取执行计划的方法

下面是几种常规的获取执行计划的方法。SQL TRACE、10046、10053 见后文。

方法	获取步骤	优缺点	应用场景
explain plan for	步骤1：explain plan for 跟上你要执行的SQL; 步骤2: select * from table(dbms_xplan.display(FORMAT=>'ADVANCED-PROJECTION')); (显示 v$sql_plan 视图中的语句的执行计划，也可以不带参数，该参数可以显示 outline data 等更详细的信息)	1.无需真正执行，快捷方便 2.没有输出运行时的相关统计信息（逻辑读，物理读，递归调用次数等）； 3.无法判断处理了多少行以及表被访问了多少次；	如果某SQL执行很长时间才出结果或返回不了结果
dbms_xplan (真的计划)	select * from table(dbms_xplan.display_cursor('&sq_id')); （从共享池里得到） select * from table(dbms_xplan.display_awr('&sq_id')); （从AWR性能视图里得到）	1.知道sql_id立即可得到执行计划（并且是真实的执行计划），无需执行； 2.没有输出运行时的相关统计信息（逻辑读，物理读，递归调用次数等）； 3.无法判断处理了多少行以及表被访问了多少次	观察某条SQL有多条执行计划的情况
statistics_level=all (真的计划)	步骤1：alter session set statistics_level=all ; 步骤2：在此处执行你的SQL 步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));	1.必须要等到语句真正执行完毕后，才可以出结果，且无法控制输出记录展现与否。 2.可以清晰地从STARTS得出表被访问多少次； 3.可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。 4.A-Time为每一步实际执行的时间，根据这一行可以知道该SQL耗时在哪儿。 5.虽然没有专门的输出运行时的相关统计信息（物理读，递归调用次数），但是执行计划中的BUFFERS就是真实的逻辑读的数值。	要想获取表被访问的次数，只能使用该方法
set autotrace on (从 plan_table 中调的，假的计划)	步骤1:set autotrace xxx 步骤2：在此处执行你的SQL	1.可以输出运行时的相关统计信息（逻辑读，物理读，递归调用次数等）； 2.必须要等语句执行完毕后才可以输出执行计划，但是可以控制输出内容； 3.无法看到表被访问了多少次	想粗略知道recursive calls递归调用次数的方法用这个，详细用 10046 trace 方法
awrsqrpt.sql	步骤1：@?/rdbms/admin/awrsqrpt.sql 步骤2：选择你要的断点（begin snap 和end snap) 步骤3：输入你的sql_id	可以方便地看到多个执行计划	想观察某条SQL多执行计划用该方法

SQL*Trace

追踪当前会话：

SQL> alter session set sql_trace=true;
-- <execute your sql statement>
SQL> alter session set sql_trace=false;

或者：
SQL> exec dbms_session.set_sql_trace(true);
-- <execute your sql statement>
SQL> exec dbms_session.set_sql_trace(false);

追踪其它会话：

exec sys.dhns_system.SET_SQL_TRACE_IN_SESSION(229,1263,TRUE);

实例级跟踪：
将参数 sql_trace 设置为 true 即可。

10046 事件

通过10046事件可以查看到如下信息：

1.可以看出SQL语句对应的等待事件
2.如果SQL语句中有函数调用，SQL中有SQL，都将会被列出，无处遁形；
3.如果SQL中含函数，函数中又套SQL等，即存在多层调用，想准确分析只能用该方法
4.可以方便的看出处理的行数，产生的物理逻辑读；
5.可以方便地看出解析时间和执行时间；
6.可以跟踪整个程序包
7.无法判断表被访问了多少次；
8.执行计划中的谓词部分不能清晰的展现出来

当前会话追踪 10046 事件：

alter session set tracefile_identifier='10046'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever,level 12'; 
-- <execute your sql statement>
alter session set events '10046 trace name context off';

收集10046 trace可能会生成较大的trace file，可能会对性能有一定的影响。如果您的数据库对此比较敏感，请在业务比较空闲的时间收集。

Level 1 等同于SQL_TRACE的功能。
Level 4 在 Level 1 的基础上增加收集绑定变量的信息。
Level 8 在 Level 1 的基础上增加等待事件的信息
Level 12 等同于 Level4+Level8，即同时收集绑定变量信息和等待时间信息。

跟踪其它会话：
dbms_system.set_ev(sid,serial#,10046,0,'username');

10053 事件

当 set auotrace 或者 explain 显示执行计划错误而又找不到原因的时候。可以使用 10053 事件生成 trace 文件，以分析执行计划的生成过程，比如实例当时的参数值是什么，使用的统计信息值是什么，怎样计算的全表扫描和走索引的cost，各种join对应的cost等。

trace 文件中的内容有如下几个部分：

参数区
SQL区
系统信息区--SYSTEM STATISTICS INFORMATION
基本统计信息--BASE STATISTICAL INFORMATION
数据访问---ACCESS PATH
关联查询----JOIN ORDER
代价的最后修正
最终执行计划

当前会话追踪 10053 事件：

alter session set tracefile_identifier='10053'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever, level 1';

对其它会话执行 10053 事件：

exec sys.dbms_system.set_ev(<sid>,<serial#>,10053,{1|2|0},'');

注意：1和2表示打开事件，1的信息更全，0表示关闭

在分析 10053 trace 文件时，我们要关心统计信息是否准确以及各cost值的比较（cost是oracle衡量代价的一个数值，目前，Oracle并没有公布cost的计算方法）。

tkprof

trace 文件晦涩难懂，可以使用 tkprof 也格式化以帮助理解。
最简单地使用方法就是 tkprof tracefile outfile，该命令还支持其它参数，直接执行该命令可以查看帮助。