Section 4.3. Making Sense of Your Diagnostic Data

4.3 Making Sense of Your Diagnostic Data

Part II contains all the information you will need to respond to properly scoped performance diagnostic data. As you'll see, the Method R pathway through your diagnostic data is deterministic. Therefore, assembly of raw Oracle trace data into something you can analyze conveniently is a task that can be automated. Some form of automation is essential if your job includes analyzing several megabytes of raw trace data. At the time of this writing, I am aware of three tools that Oracle Corporation provides to help:

tkprof: tkprof is a trace file formatter that takes raw Oracle trace data as input and emits a text file that shows performance statistics aggregated by SQL statement. Different command-line options allow you to select the order in which the SQL statements are shown. tkprof was designed for unit-level performance testing of SQL applications, and it does an excellent job in that role. Oracle9 i is the first release in which tkprof processes the Oracle "wait event" data required by Method R. Prior versions of tkprof simply ignore the wait data. (Chapter 5 explains the significance of Oracle "wait events.") For more information about tkprof , see the Oracle Performance Tuning Guide and Reference (http://technet.oracle.com) and MetaLink documents 41634.1, 29012.1, and 1012416.6.
trcsummary: trcsummary is a tool that Oracle Corporation advertises as "not available for general customer use." It uses awk and nawk to parse an Oracle trace file and provide similar output to that produced by tkprof . It was apparently designed to overcome some of the deficiencies of early tkprof releases. For more information about trcsummary , see Oracle MetaLink document 62160.1.
Trace Analyzer: Trace Analyzer is a set of SQL*Plus scripts and PL/SQL code that reads a raw SQL trace file, loads its content into a database, and then prints a detailed report. Trace Analyzer is capable of processing Oracle "wait event" data. For more information about Trace Analyzer, see Oracle MetaLink document 224270.1.

Of these three options, Oracle's Trace Analyzer is the newest and most comprehensive, but it is also the most cumbersome to use. It is very slow, and the prodigious quantity of un-prioritized output that it emits can require days of analysis to decipher.

The trace file analyzer I use is a commercial product in which I meddled while Jeff Holt did all the real work, called the Hotsos Profiler ^TM . We built the Hotsos Profiler because no other tool on the market took us from data collected to problem solved as fast as we needed. The Hotsos Profiler takes just a few seconds to convert a multi-megabyte extended SQL trace file into an HTML document that reveals the root cause of virtually any performance problem within two mouse clicks. With Hotsos Profiler output, I expect to understand the net payoffs of all my best performance improvement options within one hour of acquiring a properly scoped trace file. You can read about the Hotsos Profiler at http://www.hotsos.com.

Once you have assembled your diagnostic data into a format that you can analyze, your next job is to determine how you might go about improving the performance of your targeted user action. Your work at this point becomes a brief iterative process that looks something like this:

Use the resource profile to identify the components of response time that appears to offer the best net payoff opportunity. Then find the diagnostic data elements that will illustrate why the components account for so much response time.
Assess ideas that you believe will best reduce time spent in the response time component targeted in step 1. To do this, you'll typically test a performance improvement idea on a testing system. The result of such a test provides the data you need to forecast the net payoff of a project to implement an idea. Assess enough ideas to convince yourself that you're not overlooking any high-payoff performance improvement opportunities.

I defer the technical details of how to execute these steps to Part III. The remainder of this chapter is devoted to the task of forecasting the net payoff of a project.

Top