使用10053事件跟踪的几种方法

10053事件：Event 10053 is an internal event that externalises some of the decisions made by the optimizer in to a trace file.

对于10053更多的介绍，这里不再重复

注：使用10053事件跟踪之前确保 TRACE_ENABLED 参数的值是TRUE

下面例举三种使用10053事件跟踪的方式

1.使用传统alter session 

2.使用oradebug

3.使用DBMS_SQLDIAG 包

方法一：使用传统alter session 的方式

 -- 清空共享池

SQL> alter system flush shared_pool;

--设置dump文件大小无限制

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

--开启10053跟踪

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

--执行需要跟踪的SQL语句

SQL> EXPLAIN PLAN FOR select empno,deptno,sal from emp where empno >= 7788;

Explained.

--查找trace文件路径

SQL> SELECT value FROM v$diag_info  WHERE name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18679.trc

SQL> exit

查看 trace 文件  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18679.trc 获取跟踪结果

示例如下：

PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
sqlstat_enabled = true
is_recur_flags = 8
Bug Fix Control Environment

*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0

..................................................

注：10053跟踪的trace文件，不能使用tkprof工具来处理，只能阅读原trace文件。tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。

跟踪其它会话的方式：

sqlplus scott/oracle

SQL> select sid,serial# from v$session where sid=(select sid from v$mystat where rownum <=1);

　SID　 SERIAL#
---------- ----------
   22          339

--开启对sid 对22的10053会话跟踪

sqlplus / as sysdba

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV (22, 339, 10053,1, '');

PL/SQL procedure successfully completed.

--scott用户执行相关操作

SQL> select empno,sal from emp where empno=7900;

EMPNO   SAL
---------- ----------
 7900       8000

--关闭跟踪

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV (22, 339, 10053,0, '');

PL/SQL procedure successfully completed.

--查看trace 文件，获取跟踪结果

SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18940.trc

方法二：使用oradebug 的方式

-- 清空共享池

SQL> alter system flush shared_pool;

System altered.

--查找需要被跟踪用户进程的OS PID

sqlplus scott/oracle

SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum <=1));

SPID
------------
19003

--开启对指定会话的跟踪

sqlplus / as sysdba

--设置OS PID

SQL> oradebug setospid 19003
Oracle pid: 32, Unix process pid: 19003, image: oracle@prim (TNS V1-V3)

--不限制跟踪文件大小
SQL> oradebug unlimit;
Statement processed.

--开启10053事件跟踪
SQL> oradebug event 10053 trace name context forever,level 1
Statement processed.

--执行需要跟踪的相关SQL

SQL> select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

--关闭跟踪

SQL> oradebug event 10053 trace name context off;
Statement processed.

--显示trace文件路径
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19003.trc

查看 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19003.trc 获取10053跟踪结果

方法三：使用DBMS_SQLDIAG 包的方式（此方式要求数据库版本在11gR2之上）

sqlplus scott/oracle

SQL> select empno,deptno,sal from emp where empno >= 7788;

SQL> select sql_id, child_number, sql_text from v$sql
  where sql_text like '%7788%' and sql_text not like '%v$sql%' ;

   SQL_ID             CHILD_NUMBER             SQL_TEXT
----------------              ------------             ----------------------------------------------------------------------------------
5s4ny8pxtdkyf                 0                       select empno,deptno,sal from emp where empno >= 7788

SQL> execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'5s4ny8pxtdkyf', p_child_number=>0, p_component=>'Optimizer', p_file_id=>'SQL_TRACE_10053');

PL/SQL procedure successfully completed.

SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19052_SQL_TRACE_10053.trc

查看文件/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19052_SQL_TRACE_10053.trc 获取跟踪结果