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>’;
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
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)’
Please post your Ideas and Comments