ORACLE查看执行计划的几种方式

ORACLE查看执行计划的几种方式

目录

ORACLE查看执行计划的几种方式

explain plan命令

优缺点

AUTOTRACE开关

（1） autotrace的语法

（2）autotrace显示执行计划

（3）只显示执行计划，不现实查询结果

 （4）显示执行计划的执行计划内容，不显示统计信息

（5）显示执行计划的统计信息，不显示执行计划内容

（6）AUTOTRACE开关小结

 （7）优点和缺点

DBMS_XPLAN包

（1）使用方法

（2）使用案例

（3）优点和缺点

10046事件

（1）10046事件

（2）激活10046事件

（3）查看10046产生的trc文件名和路径的方法

（4）使用oradebug生成10046事件

（5）10046事件的关闭方法

（6）oradebug和alter session打开10046事件产生trace文件的区别

（7）trc文件的内容

（8）优点和缺点

四种执行计划获取的选择

---

• explain plan命令

PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。   explain plan使用方法：   (1) 执行explain plan for + SQL   (2) 执行select * from table(dbms_xplan.display); 第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。   --举例 --plsql developer中的按下F5显示的执行计划

优缺点

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

• AUTOTRACE开关

（1） autotrace的语法

SQLPLUS中打开AUTOTRACE开关可以得到SQL的执行计划。 从提示可以看到AUTOTRACE有几个选项： SQL> set autotrace Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

（2）autotrace显示执行计划

--实验1 set autot on select * from tl_gameuser.temp where rownum<1; 这种情况既显示查询结果也显示执行计划和统计信息

（3）只显示执行计划，不现实查询结果

-只显示执行计划 set autot traceonly   select * from tl_gameuser.temp where rownum<2;

 （4）显示执行计划的执行计划内容，不显示统计信息

set autotrace traceonly explain;

（5）显示执行计划的统计信息，不显示执行计划内容

set autotrace traceonly statistics;

（6）AUTOTRACE开关小结

(1)OFF：默认选项，当前session执行SQL只会显示结果。   SET AUTOTRACE OFF(SET AUTOT OFF)   (2)ON：除显示执行SQL结果外，还会显示对应的执行计划和资源消耗。   SET AUTOTRACE ON(SET AUTOT ON)   (3)TRACEONLY：只会显示SQL执行结果的数量，不显示执行结果的内容，适用于刷屏的SQL，还会显示执行计划和资源消耗。   SET AUTOTRACE TRACEONLY(SET AUTOT TRACE)   (4)EXPLAIN：只显示SQL执行计划，不显示SQL的资源消耗和执行结果。   SET AUTOTRACE TRACEONLY EXPLAIN(SET AUTOT TRACE EXP)   (5)STATISTICS：只显示SQL的执行结果数量和资源消耗，不显示执行计划。   SET AUTOTRACE TRACEONLY STATISTICS(SET AUTOT TRACE STAT)

 （7）优点和缺点

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

• DBMS_XPLAN包

（1）使用方法

--该方法是从共享池得到，如果SQL已被age out出share pool，则查找不到。 select * from table( dbms_xplan.display_cursor('&sql_id') );   --该方法是从awr性能视图里面获取，查询历史执行计划 select * from table( dbms_xplan.display_awr('&sql_id') );   --如果有多个执行计划，可用以下方法查出： select * from table(dbms_xplan.display_cursor('&sql_id',0)); select * from table(dbms_xplan.display_cursor('&s ql_id',1));

（2）使用案例

set line 300 set pagesize 0 select * from table(dbms_xplan.display_cursor('dr277b6yv83uy'));     select * from table(dbms_xplan.display_awr('dr277b6yv83uy'));

（3）优点和缺点

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

• 10046事件

（1）10046事件

10046事件和之前的explain plan、DBMS_XPLAN包以及AUTOTRACE开关的区别在于，10046事件产生的trc文件中明确显示了目标SQL实际执行计划中每一步所消耗的逻辑读、物理读和花费的时间，执行计划的成本分析，进而可以看出为什么Oracle对于SQL选择了这样的执行计划，而不是那样的执行计划，之所以说是实际的执行计划，从10046事件执行的过程就可以看出来：   (a) 在当前session激活10046事件。   (b) 在此session中执行SQL。   (c) 关闭此session的10046事件。   真正执行的SQL，对应的执行计划可以在trc文件中找到。这个trc文件会记录SQL的执行计划和资源消耗，命名格式“实例名_ora_当前session的spid.trc”。

（2）激活10046事件

(a) alter session set events '10046 trace name context forever,level 12';   (b) oradebug  setospid SPID;  oradebug event 10046 trace name context forever, level 12;

（3）查看10046产生的trc文件名和路径的方法

（1）找到trc文件的路径 show parameter user_dump_dest   （2）获得当前trace文件生成路径 select tracefile from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat));   （3）如果使用oradebug生成trc文件，可使用oradebug tracefile_name得到trc文件名和路径 oradebug tracefile_name

（4）使用oradebug生成10046事件

oradebug首先这是sqlplus特有的命令，在PLSQL Developer中执行会提示无效的SQL语句; 其次它是sysdba角色的命令，使用非sysdba执行会提示ORA-01031权限不足. 尽管oradebug用的时候需要使用sysdba登录，看似有些麻烦，但和第一种alter session的方法相比，最大的好处就是alter session只能针对当前会话或系统级，即alter session或alter system设置，如果设置非本会话的跟踪，此时就可以用oradebug了   使用oradebug设置10046事件之前需要首先设置待跟踪的会话：   (a) 跟踪本会话，使用：oradebug setmypid即可。   (b) 跟踪非本会话，使用：oradebug setospid SPID(来自v$process)   --查看当前会话的spid信息 SELECT spid FROM v$process WHERE addr=(SELECT paddr FROM v$session WHERE SID in (select distinct sid from v$mystat));

--使用oradebug打开10046事件 此时如果需要跟踪spid 为4835这个session执行的SQL，可以用oradebug setospid 4835，然后oradebug event 10046 trace name context forever, level 12;就打开了10046事件。   oradebug setospid 4835; oradebug event 10046 trace name context forever, level 12;   接着可以通过oradebug tracefile_name查看trace文件名和路径，例如： oradebug tracefile_name 或者使用： select tracefile from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat));   --实验截图如下:

（5）10046事件的关闭方法

(a) alter session set events '10046 trace name context off';   (b) oradebug event 10046 trace name context off;

（6）oradebug和alter session打开10046事件产生trace文件的区别

(a) 使用alter session打开10046事件时，如果未执行SQL，则不会产生trace文件。   (b) 使用oradebug event 10046 trace name context forever, level 12;打开10046事件，此时就已经产生trace文件，除基本信息外，主要是一行： 当使用oradebug event 10046 trace name context off;关闭10046事件，会写入一行,比如下面的截图：

（7）trc文件的内容

查看上面生成的5649.trc文件，以其中的某一部分进行解释。 cr代表逻辑读，pr代表物理读，pw代表物理写，time代表消耗的时间。 这里trc文件是一种裸trace文件，内容可看，但不是那么直观，使用oracle server自带的tkprof工具解析trace文件。 tkprof  /U01/app/oracle/diag/rdbms/testdb10/testdb10/trace/testdb10_ora_5649.trc /home/oracle/plain_trc.log --查看解析后的trace文件   先主要看看下面的内容： 执行计划读取口诀： “先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行”。 所以可以得到执行顺序如下: （1）TABLE ACCESS FULL TL_ACTIVE_CODE （2）TABLE ACCESS FULL TEMP （3）HASH JOIN SEMI （4）SORT AGGREGATE 也可以得到每一步物理读写，逻辑读写消耗的时间情况。

（8）优点和缺点

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

• 四种执行计划获取的选择

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