WAY BACK IN 1992, Oracle introduced a new means of extracting detailed response time information from a database: the extended SQL trace mechanism. In 1995, I began to learn much more about this feature when my colleagues at Oracle Corporation used it to remove the guesswork from Oracle performance improvement projects. The magic of this new extended SQL trace feature was that it allowed analysts to predict the exact response time impact of a proposed system change. At the time, this predictive ability was revolutionary. Predictability and the resulting notion of "fully informed decision making" has been the foundation of my career ever since.

In 1999, I left Oracle Corporation to work full time creating a new performance improvement method that I hoped would far surpass the reliability and accuracy of traditional Oracle methods. In our book, Optimizing Oracle Performance (Millsap and Holt, O'Reilly & Associates, 2003), Jeff Holt and I have published the full technical details of our new method and the extended SQL trace tool itself. Here, I describe the story behind Oracle's extended SQL trace capability, its history, the people who helped develop it, and how and why you might consider using it.

Before the Microscope

In the days before the microscope, the fight against infectious diseases was brutally difficult. In the fourteenth century, for example, bubonic plague ravaged Europe and Asia. Between 1347 and 1352, the Black Death (as the disease came to be called) killed 25 million Europeans-one third of the region's total population. Medieval medicine provided no cure for the plague, but men and women of the time tried everything they could think of to combat the disease. The dominant response to plague symptoms was bloodletting practiced with extraordinary aggressiveness. When a patient lost consciousness from too much blood loss, the "caregiver" would revive the patient with cold water. When the patient reawakened, the bleeding would continue.

The list of responses to plague symptoms would sound like comedy if not for the magnitude of the suffering that motivated them. Wives nudged their husbands in their sleep, prompting a roll from one side of the body to the other to "prevent the liver from heating too much on one side." Scents were big. People would inhale virtually anything they could think of that might have a chance of "replacing" the plague-bearing air in their bodies with plague-free air, from aromatic spices to their own sewage pits. Neighbors exterminated cats and those cats' masters on the premise that perhaps it was witchcraft at the root of the epidemic. There wasn't much that a medieval neighbor wouldn't try, if he thought he could save himself or his family from the horrors he had seen befall other families.

Medieval medicine failed to stop the plague because medieval medicine failed to identify the plague's root cause. The root cause, we know today, was a bacterium called Yersinia pestis, which was transported and inoculated into humans with exceptional efficiency by fleas ( ... turns out that cat killing probably caused positive harm). Medieval "doctors" didn't know that Y. pestis existed. They didn't even know that bacteria existed-they couldn't see them. You can't see plague bacteria without a microscope, and the microscope wouldn't be invented until a Dutchman by the name of Anton van Leeuwenhoek did the job late in the seventeenth century.

The history of Oracle performance analysis looks a lot like the history of humanity's war on infectious disease. The nouns are different, but the story is familiar. Instead of people, it's Oracle systems that die before their time. Instead of leeches, cat killing, and vapor wafting, it's hit ratios, database rebuilds, and disk rebalancing. Instead of Yersinia pestis, it's latch free, buffer busy waits, SQL*Net message from client, and the hundreds of other code paths in which an Oracle system can consume response time. And instead of the microscope, it's extended SQL trace data.

Our story even has its own Dutch guy. We will get to that in a little bit.