Oracle执行计划查看宝典，超实用

作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
B站主播Oracle、Mysql、PG实战课程，请搜索：jeames007

擅长Oracle数据库运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。

前言

在性能调优中,SQL的执行计划查看至关重要，总结以下方法

---

概要

六种执行计划
（1）explain plan for
（2）set autotrace on
（3）statistics_level=all
（4）dbms_xplan.display_cursor获取
（5）事件10046 trace跟踪
（6）awrsqrpt.sql

Oracle提供了6种执行计划获取方法，各种方法侧重点不同。

1.explain plan for

SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
  2  select *
  3  from employees,jobs
  4  where employees.job_id=jobs.job_id
  5  and employees.department_id=50;

已解释。
SQL> select * from table(dbms_xplan.display());

/*
优点：无需真正执行，快捷方便；
缺点：1.没有输出相关统计信息，例如产生了多少逻辑读，多少次物理读，多少次递归调用的情况；
          2.无法判断处理了多少行；
          3.无法判断表执行了多少次
*/

2.set autotrace on

set autotrace off --关闭
set autotrace on
开启AutoTrace，显示AUTOTRACE信息和SQL执行结果
set autotrace traceonly  /  set autot trace explain  
开启AutoTrace，仅显示AUTOTRACE信息
set autotrace  on explain 
开启AutoTrace，仅显示AUTOTRACE的EXPLAIN信息
set autotrace on statistics
开启AutoTrace，仅显示AUTOTRACE的STATISTICS信息

SQL> set autotrace on
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;

/*
优点：1.可以输出运行时的相关统计信息（产生多少逻辑读、多少次递归调用、多少次物理读等）；
          2.虽然要等语句执行完才能输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出；
缺点：1.必须要等SQL语句执行完，才出结果；
          2.无法看到表被访问了多少次；
*/

3.statistics_level

步骤一：ALTER SESSION SET STATISTICS_LEVEL=ALL;
步骤二：执行待分析的SQL
步骤三：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
注释：
1.第3步还有1种方法，select * from table(dbms_xplan.display_cursor(null,null,'advanced'));  --得到的信息更详细
例子：
SQL> alter session set statistics_level=all;
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果
--...
已选择45行。
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

关键字解读：
    1.starts：SQL执行的次数；
    2.E-Rows：执行计划预计返回的行数；
    3.R-Rows：执行计划实际返回的行数；
    4.A-Time：每一步执行的时间(HH:MM:SS.FF)，根据这一行可知SQL耗时在哪些地方；
    5.Buffers：每一步实际执行的逻辑读或一致性读；
    6.Reads：物理读；
    7.OMem：OMem为最优执行模式所需的内存评估值， 这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的；
    8.1Mem：1Mem为one-pass模式所需的内存评估值，当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi-Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
    9.Used_Mem：Used-Mem则为当前操作实际执行时消耗的内存，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示0)

4.dbms_xplan.display_cursor获取

步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') );    
--该方法是从共享池得到，如果SQL已被age out出share pool，则查找不到
注释：
1.还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') );  
--该方法是从awr性能视图里面获取(直接在命令窗口执行)
2.如果有多个执行计划，可用以下方法查出：
select * from table(dbms_xplan.display_cursor('&sql_id',0));
select * from table(dbms_xplan.display_cursor('&sql_id',1));
例子：
SQL>  select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));

如何查看1个sql语句的sql_id，可直接查看v$sql
select * from v$sql where sql_text like '%LOG%';

/*
优点：1.知道sql_id即可得到执行计划，与explain plan for一样无需执行；
     2.可得到真实的执行计划
缺点：1.没有输出运行的统计相关信息；
     2.无法判断处理了多少行；
     3.无法判断表被访问了多少次；
*/

5.事件10046 trace跟踪

--开启追踪
步骤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：select tracefile from v$process 
where addr=(select paddr from v$session 
where sid=(select sid from v$mystat where rownum<=1));      
 --找到跟踪后产生的文件
步骤5：tkprof trc文件     目标文件  sys=no sort=prsela,exeela,fchela         
--格式化命令
SQL> alter session set events '10046 trace name context forever,level 12';
执行sql语句
SQL> alter session set events '10046 trace name context off';
解析trc文件
tkprof rac1_ora_27998.trc lijiaman_10046.txt sys=no sort=prsela,exeela,fchela

/*
优点：1.可以看出sql语句对应的等待事件；
          2.如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；
          3.可以方便的看处理的行数，产生的逻辑物理读；
          4.可以方便的看解析时间和执行时间；
          5.可以跟踪整个程序包
缺点：1.步骤繁琐；
          2.无法判断表被访问了多少次；
          3.执行计划中的谓词部分不能清晰的展现出来
*/

6.awrsqrpt.sql

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

总结

选择时一般遵循以下规则：
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

如果觉得文章对你有帮助，点赞、收藏、关注、评论，一键四连支持
您的批评指正是我写作的最大动力!

❤️ 技术交流可以 关注公众号：IT邦德 ❤️