LifeSaver of the Week -3

Trace SQR

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

begin-SQL

ALTER SESSION SET SQL_TRACE = TRUE;

end-SQL

end-procedure

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

exec dbms_system.set_sql_trace_in_session(sid,serial#,Y)

– 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.