Use several methods of event tracking 10053 and several methods of event tracking 10053

Use several methods of event tracking 10053 and several methods of event tracking 10053

 

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

For more information about 10053, I will not repeat it here

 

Note: Make sure that the value of the TRACE_ENABLED parameter is TRUE before using 10053 event tracking.

 

The following are three ways to use 10053 event tracking:

1. Use Traditional alter session

2. Use oradebug

3. Use the DBMS_SQLDIAG package

 

 

Method 1: use the traditional alter session Method

 

-- Clear the Shared Pool

SQL> alter system flush shared_pool;

-- Set unlimited dump file size

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

-- Enable tracking 10053

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

Session altered.

-- Execute the SQL statement to be tracked

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

Explained.

-- Find the path of the trace file

SQL> SELECT value FROM v $ diag_info WHERE name = 'default Trace file ';

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

SQL> exit

View the trace file/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_18679.trc to obtain the trace result.

Example:

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

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

Note: The trace file tracked by 10053 cannot be processed by tkprof. You can only read the original trace file. The tkprof tool can only be used to process trace files generated by SQL _trace and 10046 events.

 

Other session tracing methods:

Sqlplus scott/oracle

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

Sid serial #
--------------------
22 339

 

-- Enable 10053 session tracking for sid to 22

Sqlplus/as sysdba

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

PL/SQL procedure successfully completed.

 

-- Scott user performs related operations

SQL> select e-mapreduce, sal from e-mapreduce where e-mapreduce = 7900;

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

 

-- Disable tracking

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

PL/SQL procedure successfully completed.

 

-- View the trace file and obtain the trace result.

SQL> SELECT value FROM v $ diag_info WHERE name = 'default Trace file ';

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

 

 

 

Method 2: Use oradebug

 

-- Clear the Shared Pool

SQL> alter system flush shared_pool;

System altered.

 

-- Search for the OS PID of the process to be tracked

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

-- Enable the tracking of a specified session

Sqlplus/as sysdba

-- Set the OS PID

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

-- Do not limit the tracking File Size
SQL> oradebug unlimit;
Statement processed.

-- Enable event tracking 10053
SQL> oradebug event 10053 trace name context forever, level 1
Statement processed.

-- Execute related SQL statements to be tracked

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

-- Disable tracking

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

-- Display the trace file path
SQL> oradebug tracefile_name
/U01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_19003.trc

 

View/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_19003.trc to obtain 10053 trace results

 

 

 

Method 3: Use the DBMS_SQLDIAG package (the database version must be above 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 '% 100' 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
Bytes ------------------------------------------------------------------------------------------------------------
/U01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_rj52_ SQL _trace_10053.trc

 

View the file/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_rj52_ SQL _trace_10053.trc to obtain the trace result.