获取执行计划之10053事件

《10053》

 

10053事件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。

10053事件的trace文件，只能阅读原始的trace文件，不能使用tkprof工具来处理

 

《10053事件级别》

 

Level 2

Column statistics

Single Access Paths

Table Joins Considered

Join Methods Considered

 

 

Level 1

Parameters used by the optimizer

Join Methods Considered (NL/MS/HA)

Column statistics

Single Access Paths

Join Costs

Table Joins Considered

Index statistics

 

《执行步骤》

 

启动10053事件

 

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

 

执行目标SQL

 

select * from emp;

 

确认trace文件

 

SELECT D.VALUE || '\' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||P.SPID || '.trc' AS "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;

 

关闭10053事件

 

ALTER SESSION SET EVENTS '10053 trace name context off';

 

10053    示    例

 

扫码

长按关注

关注

惊喜不断