Quiz Post #7: Get sql trace without reexecuting it – 11g DBMS_SQLDIAG

In general you will need to set the SQL trace or events (10046,10053) and run the problematic query and can obtain the sql execution statistics to trace files.

or

You set the trace to the corresponding session/module/program/application (note whole session/module/program/application.)

But problem with this approaches is that you have execute the intended statements or second approach is that you may get undesired also captured in trace files.

To particularly set event/trace for sql_id 11g comes with up new diagnostic event mechanism which for this case can be used in two ways

Option 1:- Read here

Option 2:- DBMS_SQLDIAG.DUMP_TRACE procedure

Oracle Database 11g, introduced a new diagnostic events infrastructure, which greatly simplifies the task of generating a 10053 trace for a specific SQL statement.
Starting in 11g Release 2, you can use this to generate an Optimizer trace for any SQL statement in the cursor cache without having to execute it. The DBMS_SQLDIAG package has been extended to include a procedure called DUMP_TRACE. This procedure, just requires SQL_ID of the statement you wish to generate an Optimizer trace for but it does not require you to re-execute the statement. The procedure will automatically trigger a hard parse of the statement to generate the trace.

To use this Package, first find the SQL_ID:

SQL> column sql_text format a30

SQL> select '10053_test' from dual;

SQL>  select sql_id, child_number, sql_text from v$sql 
      where sql_text like '%10053_test%';

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------
cjk13xfm8ybh7            0 select '10053_test' from dual

Then you can use the following syntax using the SQL_ID for your SQL (in my case ‘cjk13xfm8ybh7' above):

SQL> execute DBMS_SQLDIAG.DUMP_TRACE(

p_sql_id=>'cjk13xfm8ybh7',

p_child_number=>0,

p_component=>'Compiler',

p_file_id=>'TEST');

PL/SQL procedure successfully completed.

Note: p_component should compiler, where you can find options or diagnostic collection using

SQL> oradebug doc component sql_compiler

The above will generate a trace files in your trace directory.

-Thanks

Geek DBA