欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-07 23:20:19
10053事件:Event 10053 is an internal event that externalises some of the decisions made by the optimizer in to a trace file. 对于10053更多的介绍,这里不再重复 注:使用100 ......

 

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