Generate 10046 and 10053 trace

who didn’t face an issue with database or query and wants to know more about it ? what is going on behind that query or application ?

Oracle provides different method to do that, one of them is to enable and generate trace called 10046, the idea from using this kind of trace is that we can track the execution plan for the session and provide more information such as bin variable, more information about wait time parse and a lot of other information related to performance issues.

to generate the trace you have to follow these steps , you can use “SYS” user or any other user depends on the session, be notice that you should turn off the trace to complete gathering information, same as the below

spool check.out 
set timing on 
alter session set tracefile_identifier='NAME_OF_TRACE'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever, level 12'; 
######
Run the query or the code here
#####
select 'close the cursor' from dual; 
alter session set events '10046 trace name context off'; 
spool off 
exit; 


Important hint :-

  • exit is very important to complete and close the trace.
  • you can change the name of the trace depends on what you want
  • Close the trace after you finished to complete gathering information.
  • We select from dual to ensure ensure the previous cursor is closed.

For Trace 10053 which is also provide information but it’s can be generated only for Hard parse SQL, which mean you should add Oracle Hint to the query to ensure the hard parse will be working.

spool check.out 
set timing on 
alter session set tracefile_identifier='NAME_OF_THE_TRACE'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever'; 

run the problematic statement 

select 'close the cursor' from dual; 
alter session set events '10053 trace name context off'; 
spool off 
exit; 

Important hint :-

  • exit is very important to complete and close the trace.
  • you can change the name of the trace depends on what you want
  • Close the trace after you finished to complete gathering information.
  • We select from dual to ensure ensure the previous cursor is closed.

Now you can use the tkprof to make the trace more readable, tkprof located under $ORACLE_HOME/bin, Run the following command after generate the above trace

tkprof <trace-file> <output file> <Username/password>

cheers

Thank you

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.