获取执行计划之10053事件

10053

10053事件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。
10053事件的trace文件，只能阅读原始的trace文件，不能使用tkprof工具来处理。

10053事件级别

• Level 2
  Column statistics
  Single Access Paths
  Join Costs
  Table Joins Considered
  Join Methods Considered (NL/MS/HA)
• Level 1
  Parameters used by the optimizer
  Index statistics
  Column statistics
  Single Access Paths
  Join Costs
  Table Joins Considered
  Join Methods Considered (NL/MS/HA)

执行步骤

1. 启用10053事件

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

2. 执行目标SQL

select * from emp;

3. 确定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;

4. 关闭10053事件

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