Today I learnt something new about Oracle 11.2. I found an interesting MOS document: How to Obtain Tracing of Optimizer Computations (EVENT 10053) (Doc ID 225598.1).
Instead of doing the traditional method of:
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
One can now do (in 11G R2 onwards):
- Get a 10053 trace for a cursor in the shared pool
- Get a 10053 trace for a specific SQL ID
Get a 10053 trace for a cursor in the shared pool
If you know the SQL ID of a cursor in the shared pool, you can use the DBMS_SQLDIAG package as follows:
execute DBMS_SQLDIAG.DUMP_TRACE(- p_sql_id=>'cjk13xfm8ybh7', - p_child_number=>0, - p_component=>'Optimizer', - p_file_id=>'TEST');
Note: This procedure is not is the Oracle 11.2 documentation as noted by Maria Colgan at Capturing 10053 trace files continued. Colgan points to Greg Rahn’s post at Creating Optimizer Trace Files for more details.
This puts a 10053 trace file in the trace directory with the trace file identifier set to ‘TEST’.
Get a 10053 trace for a specific SQL ID
The new diagnostic events infrastructure, allows tracing to be enabled for a specific SQL ID instead of turning it on for the entire session. Oracle will then capture a 10053 trace for the corresponding SQL statement when it is issued in that session.
If you know the SQL ID for the specific statement, run the following command:
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:cjk13xfm8ybh7]';