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 获取跟踪结果