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 事件

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

  2. 执行目标 SQL

    1
    select * from emp;

  3. 确定 trace 文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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 事件

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

10053