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:
It allows you to identify those statements consuming the most resources. Those statements are the ones you need to tune.
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:
Set key initialization file parameters, notably TIMED_STATISTICS.
Enable tracing for the database session.
Run the SQL statements that are being tuned.
Disable tracing for the session.
Find the trace files.
Format the trace file using the tkprof utility.
Review the tkprof output.
You can iterate these steps as often as necessary, making ...