The most common approach to tracing SQR is to use the–DEBUG or –S flag. In this post, I will share the techniques I use to generate the SQL Trace that I can use to troubleshoot and determine the bad SQL.
If I need to trace a SQR in a development environment, I choose to modify the SQR and include the following procedure which will be called at the start of the program.
begin-procedure SetSQLTrace ! Set SQL Trace
ALTER SESSION SET SQL_TRACE = TRUE;
However, in a production or any other environment which is under change control, I need to co-ordinate with the functional analyst to execute the SQR. Here are the steps I follow.
1.Determine the session id as soon as the SQR program starts processing.
Tip – Use the CLIENT_INFO and PROGRAM in V$SESSION to determine the user session.
2.Execute below SQL’s.
exec dbms_system.set_bool_param_in_session(sid, serial#, ‘TIMED_STATISTICS’, TRUE);
exec dbms_system.set_int_param_in_session(sid, serial#, ‘MAX_DUMP_FILE_SIZE’, 2147483647);
— Turn on trace
exec dbms_system.set_ev(sid, serial#, 10046, 8, ”)
— or use below
— Turn off trace
exec dbms_system.set_ev(sid, serial#, 10046, 0, ”)
— Run TKPROF on trace file
I have found the above approaches very useful to identify the problem SQL whenever I receive a ticket from the user complaining about a long running SQR report or process.