概述

很多时候我们去优化某条sql时，第一步通常是对sql做执行计划分析，看是走全扫还是索引扫描，是NL、SMJ还是HJ连接，是不是有绑定变量等等...今天不讲分析，主要讲怎么看执行计划。

下面介绍下几种常见的查看执行计划的方式（方法比较多，就不一一演示了）

1、 explain plan(类似plsql中的f5)

这里先使用explain plan命令对目标SQL做explain，在使用"select * from table(dbms_xplan.display)"查看上述使用explain plan命令后得到的执行计划。

PS：PL/SQL Developer中的快捷键F5就是在explain plan命令上的一层封装。

语法：explain plan for + SQL
select * from table(dbms_xplan.display)

优点：无需真正执行，快捷方便

缺点：

a. 没有输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读的情况)

b. 无法判断是处理了多少行

c. 无法判断表被访问了多少次

其他命令

#配合explain plan使用
select * from table(dbms_xplan.display);
#查看刚刚执行过的SQL的执行计划
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
#只要目标SQL的执行计划所在的Child Cursor没有被age out出Shared Pool
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select * from emp%';
#用于查看指定SQL的执行计划
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number, 'advanced'));
#VERSION_COUNT代表有几种不同的执行计划
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select * from emp%';
#查看指定SQL的所有历史执行计划,前提是该SQL的执行计划被采集到AWR Repository中
select * from table(dbms_xplan.display_awr('sql_id'));

2、SQLPLUS中的AUTOTRACE

语法：set autotrace on

类似的有：

Set autotrace on #(得到执行计划，输出运行结果)

Set autotrace traceonly #(得到执行计划，不输出运行结果)

Set autotrace traceonly explain #(得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分)

Set autotrace traceonly statistics #(不输出运行结果和执行计划部分，仅展现统计信息部分)

优点：

a. 可以输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读的情况)

b. 虽然必须要等语句执行完毕后才可输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出

缺点：

a. 必须等到语句真正执行完毕后，才可以出结果

b. 无法看到表被访问了多少次

3、statistics_level=all

语法：

alter session setstatistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

注：

a. 如果用 /*+ gather_plan_statistics */的方法，可以省略alter session setstatistics_level=all;

b. 关键字解读：

Starts：该sql执行的次数。

E-Rows：执行计划预计的行数。

A-Rows：实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出问题。

A-Time：每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。

Buffers：每一步实际执行的逻辑读或一致性读。

Reads：物理读。

OMem：当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的

1Mem：当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过;Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的

User-Mem：语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)

OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

优点：

a. 可以清晰的从starts得出表被访问多少

b. 可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。

c. 虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少

缺点：

a. 必须要等到语句真正执行完毕后，才可以出结果。

b. 无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。

c. 看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）

4、通过dbms_xplan.display_cursor输入sql_id参数直接获取

语法：

select * from table(dbms_xplan.display_cursor('&sq_id')); #从共享池获取

注：

a. select * fromtable(dbms_xplan.display_awr('&sq_id')); 从awr性能视图获取

b. 查看多个sql的执行计划

select * from table(dbms_xplan.display_cursor('4jj76r0vw14zx',0));

select * fromtable(dbms_xplan.display_cursor('4jj76r0vw14zx',1));

优点：

a. 知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；

b. .可以得到真实的执行计划。

缺陷

a. 没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；

b. 无法判断是处理了多少行；

c. 无法判断表被访问了多少次。

5、10046事件

与之前三种查看执行计划方法不同之处在于，所得到的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。执行计划与明细资源消耗会写入此Session所对应的trace文件中，Oracle会在参数USER_DUMP_DEST所代表的目录下生成这个trace文件。

• 首先在当前Session中激活10046事件
• 接着在此Session中执行目标SQL
• 最后在此Seesion中关闭10046事件

#激活10046事件两种方法
alter session set events '10046 trace name context forever,level 12'
oradebug event 10046 trace name context forever,level 12
#关闭10046事件两种方法
alter session set events '10046 trace name conetxt off'
oradebug event 10046 trace name context off
#tkprof分析跟踪文件
tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela （格式化命令）

不过我习惯用oradebug跟10046来做结合，下面简单演示下：

tkprof分析

优点：

a. 可以看出SQL语句对应的等待事件

b. 如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。

c. 可以方便的看出处理的行数，产生的物理逻辑读。

d. 可以方便的看出解析时间和执行时间。

e. 可以跟踪整个程序包

缺陷:

a. 步骤繁琐，比较麻烦

b. 无法判断表被访问了多少次。

c. 执行计划中的谓词部分不能清晰的展现出来。

6. awrsqlrpt.sql

步骤：@?/rdbms/admin/awrsqrpt.sql

选择你要的断点（begin snap 和end snap)

输入sql_id

这样就可以得出结果了。

如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1；其实跟踪某条SQL最简单的方法是方法1，其次就是方法2；

如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；

如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；

要想确保看到真实的执行计划，不能用方法1和方法2；

要想获取表被访问的次数，只能使用方法3；

后面会分享更多关于DBA方面内容，感兴趣的朋友可以关注下！