作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来学习 Oracle 执行计划查看方法汇总及优劣比较

1）执行计划

执行计划是一条 SQL 语句在 Oracle 数据库中的执行过程或访问路径的描述。如下图所示，是一个比较完整的执行计划示意图。

如上执行计划一般包含三部分，SQL_ID,SQL 文本以及执行计划对应的 plan_hash_value。

其次，则是执行计划主体，主要有内部执行步骤、执行顺序、谓词信息、列信息、预估行数 E-Rows、执行时间 Time、Cardinality、Cost 等。

最后 Note 部分，执行计划的额外补充信息，是否动态采用(dynamic sampling)、是否 Cardinality Feedback、是否 SQL Profile 等等。

主体 Header

Id：序号

Operation：当前操作的内容

Rows：当前操作的 Cardinality，Oracle 估计当前操作的返回结果集。

Cost：SQL 执行的代价

Time：Oracle 估计当前操作的时间

Query Block Name

Outline Data

Predicate Information

Access

通过某种方式定位了需要的数据，然后读取出这些结果集，叫做 Access。

表示这个谓词条件的值将会影响数据的访问路径(表还是索引)。

Filter

把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做 filter 。

表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

Column Projection Information

Note

动态采样(dynamic sampling)是 Oracle CBO 优化器的一种特性。如果相关数据表没有收集过统计信息，又要使用 CBO 的机制，就会引起动态采样。下面来看看执行计划的各种获取方法。

步骤 1: @?/rdbms/admin/awrsqrpt.sql 步骤 2: 选择你要的断点（ begin snap 和 end·snap) 步骤 3: 输入你的 sql_id select max(snap_id) from dba_hist_snapshot; Exec dbms_workload_repository.create_snapshot(); --手动生成快照 @?/rdbms/admin/awrsqrpt.sql

步骤 1: alter session set events '10046 trace name context forever,level 12'; （开启跟踪） 步骤 2 执行 SQL 语旬 步骤 3: alter session set events '10046 trace name context off'; （关闭跟踪） 步骤 4 找到跟踪后产生的文件 步骤 5: tkprof trc 文件 目标文件 sys=no sort=prsela,exeela , fchela （格式化命令） 步骤 4: select d.value || '/' || LOWER(RTRIM(i.INSTANCE,CHR(0))) ||'_ora_' || p.spid ||'.trc' trace_file_name from (select p.spid from v$mystat m, v$session s, v$process p where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p, (select t.INSTANCE FROM v$thread t, v$parameter v WHERE v.name='thread' AND(v.VALUE=0 OR t.thread#=to_number(v.value)) ) i, (select value from v$parameter where name='user_dump_dest') d; tkprof /oracle/app/oracle/diag/rdbms/PROD/PRODSTB/trace/PROD_ora_7238.trc /tmp/10046_sql.log sys=no sort=prsela,exeela,fchela cat /tmp/10046_sql.log ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 1.08 1.78 9568 10117 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 1.09 1.79 9568 10117 0 1 Misses in library cache during parse: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 31.01 55.20 db file sequential read 130 0.01 0.13 db file scattered read 274 0.03 1.07 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 2 user SQL statements in session. 0 internal SQL statements in session. 2 SQL statements in session. ******************************************************************************** /* ---优点：1. 可以看出 SQL 语旬对应的等待事件， 2. 如果 SQL 语旬中有函数调用，函数中又有 SQL ，将会被列出，无处遁形， 3.可以方便地看出处理的行数，产生的物理逻辑读， 4 . 可以方便地看出解析时间和执行时间 i 5. 可以跟踪整个程序包。 ---缺陷 1. 步骤烦琐，比较麻烦； 2. 无法判断表被访问了多少次， 3. 执行计划中的谓词部分不能清晰地展现出来。 */

4) 如何选择 选择时一般遵循以下规则： 1.如果 sql 执行很长时间才出结果或返回不了结果，用方法1：explain plan for 2.跟踪某条 sql 最简单的方法是方法1：explain plan for，其次是方法2：set autotrace on 3.如果相关查询某个 sql 多个执行计划的情况，只能用方法4：dbms_xplan.display_cursor 或方法6：awrsqrpt.sql 4.如果 sql 中含有函数，函数中有含有 sql，即存在多层调用，想准确分析只能用方法5：10046 追踪 5.想法看到真实的执行计划，不能用方法1：explain plan for 和方法 2：set autotrace on 6.想要获取表被访问的次数，只能用方法 3：statistics_level = all

获取执行计划的方法

方法

获取步骤

优点

缺点

应用场景

explain plan for

步骤1：explain plan for /跟上你要执行的SQL/ 步骤2:select * from table(dbms_xplan.display());

无需真正执行，快捷方便

1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）； 2.无法判断处理了多少行；3.无法判断表被访问了多少次

如果某SQL执行很长时间才出结果或返回不了结果

set autotrace on

步骤1:set autotrace on 步骤2：在此处执行你的SQL

1.可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；2.虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出

1.必须要等到语句真正执行完毕后，才可以出结果；2.无法看到表被访问了多少次

想粗略知道recursive calls递归调用次数的方法用这个，详细用10046trace 方法

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.可以清晰地从STARTS得出表被访问多少次；2.可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。3.虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的数值

1.必须要等到语句真正执行完毕后，才可以出结果。2.无法控制输出记录展现与否，而autotrace有 traceonly 可以控制不将输出记录打屏。 3.看不出递归调用的次数，看不出物理读的数值

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

dbms_xplan.display_cursor

select * from table(dbms_xplan.display_cursor('&sq_id')); （该方法是从共享池里得到）

1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；2.可以得到真实的执行计划

1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；2.无法判断处理了多少行；3.无法判断表被访问了多少次

观察某条SQL有多条执行计划的情况

事件10046 trace 跟踪

步骤1：alter session set events '10046 trace name context forever,level 12'; （开启跟踪） 步骤2：执行你的语句 步骤3：alter session set events '10046 trace name context off'; （关闭跟踪） 步骤4：找到跟踪后产生的文件 步骤5：tkprof trc文件 目标文件

1.可以看出SQL语句对应的等待事件 2.如果SQL语句中有函数调用，SQL中有SQL，都将会被列出，无处遁形；3.可以方便的看出处理的行数，产生的物理逻辑读；4.可以方便地看出解析时间和执行时间；5.可以跟踪整个程序包

1.步骤繁琐，比较麻烦；2.无法判断表被访问了多少次；3.执行计划中的谓词部分不能清晰的展现出来

如果SQL中含函数，函数中又套SQL等，即存在多层调用，想准确分析只能用该方法

awrsqrpt.sql

步骤1：@?/rdbms/admin/awrsqrpt.sql 步骤2：选择你要的断点（begin snap 和end snap) 步骤3：输入你的sql_id

可以方便地看到多个执行计划

获取的过程比较麻烦

想观察某条SQL多执行计划用该方法

和 explain plan for 一样的还可以使用 PL/SQL developer 工具的 F5 键也可查看执行计划，SQL Monitor 工具也可以查看，当然更高级的 sqlhc 工具，这里顺便说说 sqlhc 工具，这个工具收集的信息非常全面，值得大家尝试。

上传 sqlhc 文件，公众号后台回复【sqlhc】获取，输入 T 和 sqlid 即可生成。sqlhc 是 SQL health check的简称，能够收集sql相关的表、索引、统计信息、优化器参数、SQL执行情况、等待事件等信息，可以帮你检查SQL存在的问题并优化 SQL。