10053 Trace for SQL statements available in cursor cache

Traditionally if we want to capture an optimizer trace for a sql statement then we would issue an

o   Alter session command to switch on a 10053 trace (alter session set events '10053 trace name context forever, level 12'; )

o   Then run the sql you want to capture the trace for

o   Once completed exit the session and look for trace in the USER_DUMP_DEST directory.

But what if the sql statement was actually a part of plsql package got executed and available in the cursor cache? In this case we can make use of the new feature dbms_sqldiag API available in the 11g and above to generate the 10053 trace file for us.

rajesh@ORA11G> create or replace procedure process_data

  2  as

  3  begin

  4     for x in (select owner,count(*) cnt

  5                             from big_table

  6                             group by owner)

  7     loop

  8             null ;

  9     end loop;

 10     $if $$debug $then

 11             for x in (select * from

 12                     table(dbms_xplan.display_cursor))

 13             loop

 14                     dbms_output.put_line(x.plan_table_output);

 15             end loop;

 16     $end

 17  end;

 18  /

 

Procedure created.

 

Elapsed: 00:00:00.24

rajesh@ORA11G> set timing off

rajesh@ORA11G> alter procedure

  2  process_data compile

  3  plsql_ccflags='debug:true';

 

Procedure altered.

 

rajesh@ORA11G> exec process_data;

SQL_ID  9gp5ypq76vhfa, child number 0

-------------------------------------

SELECT OWNER,COUNT(*) CNT FROM BIG_TABLE GROUP BY OWNER

 

Plan hash value: 1753714399

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |       |       |  2714K(100)|          |

|   1 |  HASH GROUP BY     |           |    33 |   198 |  2714K  (1)| 00:09:34 |

|   2 |   TABLE ACCESS FULL| BIG_TABLE |   100M|   572M|  2710K  (1)| 00:09:33 |

--------------------------------------------------------------------------------

 

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> @tkfilename.sql

D:\APP\179818\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_81780.trc

 

rajesh@ORA11G> begin

  2     dbms_sqldiag.dump_trace

  3     ('9gp5ypq76vhfa',0,'Compiler');

  4  end;

  5  /

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Here is what the contents available in ora11g_ora_81780.trc file.

Enabling tracing for cur#=13 sqlid=a94wx68sy8n04 recursive

Parsing cur#=13 sqlid=a94wx68sy8n04 len=80

sql=/* SQL Analyze(70,0) */ SELECT OWNER,COUNT(*) CNT FROM BIG_TABLE GROUP BY OWNER

End parsing of cur#=13 sqlid=a94wx68sy8n04

Semantic Analysis cur#=13 sqlid=a94wx68sy8n04

OPTIMIZER INFORMATION

 

******************************************

----- Current SQL Statement for this session (sql_id=4cyv3hm6p9buu) -----

/* SQL Analyze(70,0) */ SELECT OWNER,COUNT(*) CNT FROM BIG_TABLE GROUP BY OWNER

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

000007FF0C17C418       145  package body SYS.DBMS_SQLTUNE_INTERNAL

000007FF0C17C418     12098  package body SYS.DBMS_SQLTUNE_INTERNAL

000007FF0C1FAF00      1229  package body SYS.DBMS_SQLDIAG

000007FF174D9390         2  anonymous block

*******************************************

Legend

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

....

....