Optimizing Oracle Performance

Chapter 6. Collecting Extended SQL Trace Data

The process for collecting extended SQL trace data is somewhat of a moving target. For a while, Oracle appeared committed to the excruciatingly inefficient Oracle Trace mechanism. However, the Oracle9i release 2 documentation states flatly that Oracle trace will be deprecated in favor of SQL trace (presumably extended SQL trace) [Oracle (2002)]. Oracle describes the use of SQL trace in its standard documentation, but if you want to use extended SQL trace, you have to work to find the information. This chapter helps to solve that problem for Oracle releases 7 through 9. The architects of the Oracle kernel understand the value of response time data that can be attributed accurately to end-user business actions. Look for Oracle release 10 to contain several features that will simplify your data collection challenges.

Warning

Note that when you generate SQL trace data, you are recording data about your application in an ASCII file. Each SQL trace file contains application SQL text. Many SQL trace files also contain application data. The use of this information is probably subject to strict rules within your company. You must ensure that your use of SQL trace files does not breach confidentiality or leak sensitive data to those who should not see it.

Understanding Your Application

As you learned in Chapter 3, you want to be able to trace exactly the actions motivated by a carefully specified user or batch program for a carefully specified ...