SQL Trace

In this article, I am going to explain what to get trace for various technology components of the Oracle applications.

Hence we are going to see

• What is sql trace?
• How to take sql trace for a session?
• How to do sql trace for a Form?
• How to do sql trace for a Report?
• How to do sql trace for a OAF Page?

What is sql trace?

• SQL Trace is a diagnostic tool for sql runtime and it gives a raw dump of SQL queries executed in the session and this dump can be read using tkprof command.
• Statements are displayed in the order they are processed.
• Every statement excuted will be displayed with statistics and optimizer routing.
• You can see what values are being bound at runtime.
• If you are getting a runtime error like ORA-942 or ORA-904, you can find out which statement is causing it.

How to take sql trace for a session?

Enabling trace for the current session

• alter session set sql_trace=true;
• alter session set events ‘10046 trace name context forever, level <x>’;
• dbms_session.set_sql_trace(true);
• dbms_support.start_trace(waits=>true,binds=>true);

Enabling trace for a different session

• dbms_system.set_sql_trace_in_session (SID,SERIAL#,TRUE);
• DBMS_SUPPORT.START_TRACE_IN_SESSION( SID , SERIAL#, waits=>TRUE, binds=>TRUE )

How to do sql trace for a Form?

• Help -> Diagnostics -> Trace and then Choose the trace level.
• Note the path of the trace file displayed in the Dialog box
• Do the transcation that is causing the performance problme
• Help -> Diagnostics -> No Trace, to disable the trace
• Trace file XXX.trc is returned in the path displayed in the dialog box.

Copy the path displayed in the alert window



How to do sql trace for a Report?

Add the following statement in the before report trigger

1. SRW.DO_SQL ('ALTER SESSION SET SQL_TRACE=TRUE');
2. Upload the report in Oracle Apps
3. Goto Application Developer Resposibility
Select Concurrent -> Program
4. Query for the Concurrent Program that executes the report
5. Select "Enable Trace" checkbox and save the record


6. Now run the report to get the trace file. (don't forget to disable the trace after running the report)

How to do sql trace for a OAF Page?

• Set profile FND : Diagnostics to Yes at user level
• Login to Self Service as the above user
• Click on Diagnostics icon at the top of page

Select ‘Set Trace Level’ and click Go

It Displays following options

• Disable Trace
• Trace (regular)
• Trace with binds
• Trace with waits
• Trace with binds and waits
• Select the desired trace level and click Save

• Perform the activity that you want to trace
• Disable the Trace using Diagnostics Page.
• Exit application

To determine where the raw trace file is located.
From SQLPlus execute following query:

SELECT value FROM v$parameter WHERE name = 'user_dump_dest'

How do I read the trace file or .trc file?

• tkprof <tracefile> <outputfile> explain=username/password sort='(sorting options)'
• Eg: tkprof POSTDI9837.trc output.prf explain=apps/apps sort=‘(prsela, exeela, fchela)’ 

Challa.