There are at least three distinct ways to access Oracle's operational timing data:
Querying Oracle fixed views using SQL (fixed views are the views whose names begin with the prefix V$ , GV$ , or X$ ).
Polling Oracle shared memory segments directly to obtain the same V$ data (that is, accessing the same V$ data without using SQL).
Activating Oracle's extended SQL trace facility to emit the complete historical timing activity of an Oracle session to a trace file.
Although V$ data and extended SQL trace data look like quite different things, it's all the same data, just presented through different user interfaces. In Chapter 7, I describe where the base information comes from.
After devoting three years full-time to studying Method R and its data collection requirements, my personal opinion on the merits of these three approaches is as follows :
Using SQL to acquire data from V$ fixed views is an excellent way to compile information about resource consumption (that is, to acquire information about how many times various resources have been visited). See Tom Kyte's excellent example at http://asktom.oracle.com/~tkyte/runstats.html for more information. V$ data are especially valuable during application development. Using SQL to acquire timing data through the V$ fixed views, it's easy to get started experimenting with Oracle's operational timing data. But for several reasons listed in Chapter 8, the timing data you will obtain from this data source are unreliable for several problem types. Using SQL to acquire timing data from V$ fixed views provides much less capability than the other two approaches.
|
If you already own a tool that allows you to properly manipulate the time scope and action scope of your diagnostic data, then high-frequency polling directly from shared memory is probably an excellent approach for you. High-frequency polling gives you diagnostic data that reliably help you solve many types of performance problem. However, attaching to shared memory and then storing gigantic masses of data requires either a lot of study and hard work, or a financial investment in a tool to do it for you. Such tools are expensive.
The extended SQL trace facility also offers outstanding diagnostic reliability, but without the research or investment pain that high-frequency polling requires. The principal disadvantage of using extended SQL trace is that you'll be able to collect diagnostic data only for those user actions that you can expect beforehand to exhibit suboptimal performance behavior. This can require some patience when a performance problem occurs only intermittently. With polling, you'll be able to construct properly scoped diagnostic data for any historical user action that you might like to analyze, but only if you have invested into enough un-aggregated diagnostic data storage. Extended SQL trace data provides an excellent low-cost substitute for high-frequency polling.
In Table 3-5, I've tried to translate my opinion into a numerical format for your convenience.
|
I believe that extended SQL trace data offers the best performance optimization value of the three diagnostic data sources identified in this chapter. In the past three years, my colleagues and I at www.hotsos.com have helped to diagnose and repair production performance problems in well over 1,000 cases using only extended SQL trace data. Our field testing has shown that, when used properly, the extended SQL trace feature is a stunningly reliable performance diagnostic tool.
Diagnostic data source | |||
---|---|---|---|
Attribute | V$ fixed views | Oracle shared memory | Extended SQL trace data |
Ease of getting results now | 9 | 1 | 8 |
Ease of storing the retrieved data | 7 | 1 | 10 |
Ease of parsing the retrieved data | 8 | 1 | 7 |
Minimal invasiveness upon Oracle kernel | 2 | 10 | 7 |
Minimal invasiveness upon other resources | 8 | 4 | 7 |
Capacity for historical drill-down analysis | 1 | 8 | 7 |
Cost to develop tools to assist in analysis | 9 | 1 | 6 |
Diagnostic reliability | 3 | 9 | 9 |
Total | 45 | 35 | 61 |
Top |