Search Results


Monday, January 05, 2009

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.
From Trace 1


Copy the path displayed in the alert window

Form Trace 2


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
Reports Trace
  • 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
oaf trace

  • 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
oaf trace2
  • 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