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
Technology Geek 