使用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 獲取跟蹤結果