| Previous | Table of Contents | Next |
The SQL Trace facility and the Oracle program TKPROF are designed to give performance information about individual SQL statements. You can use this information to determine the characteristics of those statements.
You can enable SQL Trace for a session or for an entire instance. Of course, because this facility gathers an abundance of information about SQL statement functionality and performance, SQL Trace has an effect on the performance of the system. If you use SQL Trace on a single session, the effect is fairly minimal, but if you use SQL Trace on an entire instance, you will see a substantial effect on the performance of the system. Avoid running SQL Trace on an entire instance for this reason.
SQL Trace Initialization
Before you run SQL Trace, you must make sure that certain Oracle initialization parameters are set:
| Parameter | Description |
|---|---|
| TIMED_STATISTICS | Setting TIMED_STATISTICS=TRUE enables SQL Trace and some of the dynamic performance tables to collect timed statistics such as CPU and elapsed times. Enabling timed statistics incurs significant overhead, because most Oracle operations are now being timed, and should be avoided except when necessary. |
| MAX_DUMP_FILE_SIZE | Specifies the maximum size of trace file dumps in OS blocks. Set this fairly low to avoid filling up the file system with trace files. If the SQL Trace output files are being truncated, increase this value. |
| USER_DUMP_DEST | This parameter specifies the destination for the trace file. The default destination is the same as for system dumps on your OS. |
You can turn SQL Trace on and off on a per-session basis by using the ALTER SESSION command or on an entire instance by setting the Oracle initialization parameter SQL_TRACE to TRUE. By setting SQL Trace on a session, you can see the resources of a particular task. By setting SQL Trace on for the entire instance, you can view information on all tasks.
SQL Trace Functionality
Once SQL Trace is enabled, it gathers the following information:
SQL Trace puts this information into a trace file in an unreadable form. You then use the Oracle program TKPROF to format the trace information into useful, understandable data.
Interpreting SQL Trace
This section looks at some of the statistics available from SQL Trace and how to interpret them. For each SQL statement executed, SQL Trace provides the following information:
| Parameter | Description |
|---|---|
| count | Number of times the OCI procedure was executed. (The OCI interface is the standard set of calls used to access the Oracle database.) |
| cpu | CPU time in seconds executing. This value is the amount of time Oracle uses to process the statement. |
| elapsed | Elapsed time in seconds executing. This value is equivalent to the user’s response time. |
| disk | Number of physical reads of buffers from disk. This value tells you how many reads actually missed the buffer cache and had to go to physical disk. |
| query | Number of buffers gotten for consistent read. This value represents the number of buffers retrieved in consistent mode. Consistent mode guarantees consistent reads throughout the transaction; it is used for most queries. |
| current | Number of buffers gotten in current mode (usually for update). In current mode, the data blocks gotten reflect the value at that instant in time. |
| rows | Number of rows processed by the fetch or execute call. This value gives you an idea of how many instructions have been executed. |
By looking at each of these parameters, you can get an idea of how your SQL statements are being processed and which statements are taking the most time. By analyzing which statements are taking the longest, you may be able to find some inefficiencies you can correct.
| Previous | Table of Contents | Next |