SQL Trace

How to do SQL Trace on the Oracle Applications 11i?

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)’

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments