How to Obtain Tracing of Optimizer Computations (EVENT 10053)


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):

  1. Get a 10053 trace for a cursor in the shared pool
  2. 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]'; 

Colgan explains How do I capture a 10053 trace for a SQL statement called in a PL/SQL package?.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s