Oracle SQL: the Essential Reference

8.2. Using Oracle's SQL Trace Facility

The information from EXPLAIN PLAN is useful, but it tells only half the story. Knowing the execution plan for a statement is one thing, but properly tuning a query requires correlating that execution plan with resource usage, and Oracle's built-in SQL Trace facility allows this action. Using SQL Trace, statistics can be generated showing the resources consumed by SQL statements that are executed. SQL Trace tracks the following information for each SQL statement that is executed:

• The number of executions

• The number of times the statement was parsed

• The number of rows returned

• The number of physical reads

• The number of logical reads

• The elapsed time spent executing the statement

• The CPU time spent executing the statement

This information is valuable for two reasons:

1. It allows you to identify those statements consuming the most resources. Those statements are the ones you need to tune.

2. It provides concrete data by which you can measure your tuning efforts. You can see the effect that a changed execution plan has on physical I/O counts and CPU time immediately.

From a high level, the process of using SQL Trace looks like this:

1. Set key initialization file parameters, notably TIMED_STATISTICS.

2. Enable tracing for the database session.

3. Run the SQL statements that are being tuned.

4. Disable tracing for the session.

5. Find the trace files.

6. Format the trace file using the tkprof utility.

7. Review the tkprof output.

You can iterate these steps as often as necessary, making ...