Home > DB_LEVEL > How to generate 10053 trace for any sql.

How to generate 10053 trace for any sql.

March 30, 2014

As a part of our tunning activity we may need to generate trace for different SQLs. Following sqls may be handy during the same.

alter system set trace_enabled=true;
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier=’10053′;
ALTER SESSION SET events ‘10053 trace name context forever, level 1’;
EXPLAIN PLAN FOR
<Your Query Here. Remember to force hard parse !>
EXIT;

the output will be in user_dump_dest

 

Note1:-

1) Please note <level 1> can be changed as per your requirement.

2) Please note in 11g, trace will not be generated becuase of bug, trace_enabled will be false. It should be allways true.

 

 

 

 

Advertisements
Categories: DB_LEVEL
%d bloggers like this: