Profiling and Tracing

David Clement

September 2004

Oracle versions 7-8

Cary Millsap, one of the leaders in Oracle optimization and tuning, likes to use the analogy between driving to work and application throughput to show what the most effective tuning methods are like. As he says, the question is where we are spending our time. A faster car will not shorten the commute if the stoplights are what's taking the time.

The most effective ways to see where we are spending our time are tracing and profiling. The definitions of these words overlap, but you could say that tracing is recording the details of execution of a process, transaction, or session, while profiling is reporting the time and resources expended overall for the process, transaction, or session. For example, by tracing a session you might see that it executes a SQL statement that always requires a full table scan when it could access the data by row id; by profiling the same session you might see that it requires too many block reads for the rows it returns. These are two ways of discerning the same problem, namely, that the session is i/o-bound.

It is quicker to get the picture by profiling, but in order to fix the performance problem, it is often necessary to get the details by tracing execution.

The most commonly used profiling tools from Oracle are the Bstat/Estat/Statspack and Tkprof profilers. The most commonly used trace methods are autotrace (see Explaining the Execution Plan) and, for a lower-level trace including timing and the wait events, the level 10046 trace. Less commonly used but worth consideration is the level 10053 trace.