Optimizing Oracle Performance

Chapter 5. Interpreting Extended SQL Trace Data

To succeed, a performance analyst must understand the language in which a system communicates information about its performance. Unfortunately, for over a decade, the domain of Oracle time statistics has been one of the most misunderstood areas of the Oracle kernel. To understand the response time instrumentation that the Oracle kernel provides, you must understand how the Oracle kernel interacts with its host operating system. It is this operating system that allocates resources to the Oracle kernel process itself, and it is the operating system that actually supplies the timing statistics that Oracle uses to describe its own performance.

Trace File Walk-Through

I believe that the best way to begin the study of Oracle operational data is with a tour of Oracle’s extended SQL trace output. SQL trace output is unsurpassed as an educational and diagnostic aid, because it presents a linear sequential recorded history of what the Oracle kernel does in response to an application’s demands upon the database.

The SQL trace feature has been a part of the Oracle kernel since Version 6, which should be older than any version of Oracle that you are currently running. In 1992, with the release of the kernel Version 7.0.12, Oracle Corporation significantly enhanced the value of SQL trace data by adding information about the durations of non-CPU-consuming instructions that the Oracle kernel executes.

Let’s begin our study with the “Hello, world” of ...