DEBUGGING APPLICATIONS WITH SQL*TRACE

I. How To Run SQL*Trace and Format the Trace File
The following document explains how to run SQL*Trace, and how to format the trace file that is created. This document can also be found in GSX by querying on the key words ‘trace’, ‘applications’ and ‘tkprof’.

SQL*Trace is a database tool analysts or customers can use to debug certain problems in Oracle Applications. SQL*Trace creates a file containing all SQL statements, and statistics for each statement executed during a user’s session.
This file can later be reviewed to determine:
· What SQL statements were executed.
· How long and how much CPU time did each script take
· How the database was accessed by each statement (Explain option)
SQL*Trace is especially useful for performance issues. Oracle developers WILL ask for 'trace output' for performance problems in order to find out exactly what the process is doing at the database level.
SQL*Trace may also help identify the SQL script that is causing other problems such as: · A process suddenly terminates with an ORA error. · Quickpick is not returning an expected value. · Query in a form is not returning an expected value.
SQL*Trace for form level processes can be turned on from the application menu. For processes run by the concurrent manager, such as executables and Oracle Reports, SQL*Trace must be turned on at the database level.
NOTE: It is possible to run SQL*Trace on an Oracle Report without shutting down the database; however, it requires modification of the report. Another bulletin is available which explains how to do this, query the problem repository on ‘r20conv’ and ‘trace’.
Running SQL*Trace at the Database Level
Following are the steps to turn SQL*Trace on at the database level. NOTE: This process should be performed by your database administrator, if you have one.
1. Edit your INIT.ORA or CONFIG.ORA (Server Administration Guide, Appendix A )
- This file resides under $ORACLE_HOME/dbs
- The INIT.ORA is read when the database is started.
- The following INIT.ORA parameters must be set:
SQL_TRACE = TRUE
USER_DUMP_DEST = TIMED_STATISTICS = TRUE
MAX_DUMP_FILE_SIZE =
2. Shutdown Concurrent Manager and Database.
- Shutdown the concurrent manager first. Then shut down the database.
- Make sure that no users are on the system.
3. Restart the Database and the Concurrent Manager.
4. Run the process.
SUGGESTION: Do not have other users on the system while trace is on. Only run the process in question. This will make finding the problem much easier.
5. Check the USER_DUMP_DEST directory for the trace output.
NOTE: An easy way of determining the directory a trace file is written to is to run the following SQL:
Select value from v$parameter where name = 'user_dump_dest';
6. Find the trace file for your process.
- If there are multiple trace files use the creation date to help determine which file is yours. In UNIX, use 'ls -lrt'.
- If there are multiple files for the same session, they may all be relevant.
- The file names will be *.trc.
7. Shutdown and restart the database with the original INIT.ORA.
Using TKPROF to Format the Trace File
The trace file that is created contains statistics on how long each SQL script took, but this information needs to be formatted to be readable. Use tkprof on the trace file to format it into readable performance statistics. Tkprof also formats the SQL scripts making them easier to read.
Before running tkprof on the trace file:
- Make sure the table plan_table exists.
- If not, find the command to create the table from the Server Application Developer's Guide, page B-11.
TKPROF Format:
tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
Examples:
1. tkprof ora_11598.trc myfilename
2. tkprof ora_11598.trc /tmp/myfilename
3. tkprof ora_11598.trc /tmp/myfilename explain=apps/apps
4. tkprof ora_23532.trc myfilename explain=apps/apps sort=execpu
NOTES: · In order to create the tkprof file, you must have write access in the directory where you are creating the file. If you do not have write access in the trace directory, then specify a directory where you do have write access. The /tmp directory is a possible directory to use since users should have write access to this directory.
Example #2 shows how to do this. · The values listed in [ ] are optional; however, explain should always be used if the issue is a performance problem. Explain should be followed by the userid/passwd of the product that SQL*Trace was executed from.
See Example #3. · For more information, type and hit return at the operating system prompt. This will return an explanation on the usage of tkprof, as well as the optional parameters. ·
Example #4 shows how to sort the SQL scripts in order of the CPU time spent executing. This will list the SQL scripts that use the most CPU time at the top of the file. This is useful for identifying performance problems in large trace files.
Setting Up Form Level Trace
Following are the steps to set up SQL*Trace from your application menu. This is used to turn SQL*Trace on while in a form. (It is not necessary to shut down the database when running SQL*Trace from a form).
1. Go to the Application Developer responsibility.
2. Navigate to the Define Menu form. This will vary slightly with Application version.
3. Query the Menu for which you would like to add Trace. (This is typically added to the menu OTHER.)
4. Add the following entry in the Menu Entries zone.
Prompt Description Type Application Name Arguments
Trace SQL Trace Menu Appl. Object Library TRACE
5. Commit.