Session Trace to find the reason in Oracle DB

Sometimes the same query that ran earlier with less time may take much more time now. To find out the reason, we can follow the below steps.

To find out what is happening you need to do a session trace of a session running this SQL.

• Open up SQL*Plus and connect to your database.
• Enable timed statistics if it not already enabled.

alter session set time_statistics=true;

• Turn on a level 8 SQL Trace of your session. A level 8 trace will capture your wait events. The alter session set events statement below is what enables the SQL Trace of your session.

alter session set tracefile_identifier='Target_Trace';
alter session set events '10046 trace name context forever, level 8';

• Run your Select and then exit SQL*Plus.

• On your database server, go to the $UDUMP directory and find your trace file. It will have 'EMP_Select' as part of the name.

• Using your trace file as input use the tkprof utility to format your trace file into a more readable format. If you invoke tkprof with no arguments, it will give you a help screen with the valid options to use. Make sure you specify the EXPLAIN_PLAN and WAITS=YES options.

Using the output of the TKProf utility you should be able to identify the problem. You would need to especially review the Wait events and Explain plan output.