The OWI Philosophy


The OWI is a performance tracking methodology that focuses on process bottlenecks, which are better known as ‚“wait events ‚½ and lesser known as symptoms. This includes waits for I/O operations, locks, latches, background processes activities, network latencies, and so on. OWI records and presents all the bottlenecks that a process encounters from start to finish. It keeps track of the number of times and the amount of time a process spent on each bottleneck. Removing or even reducing the impact of major bottlenecks improves performance. For example, if you are to improve your travel time to work, you must first identify the major time-consuming elements on your route such as traffic lights, stop signs, school zones, traffic jams, and so on. Once you are able to remove, bypass, or reduce your major bottlenecks, you will automatically improve your travel time.

In the course of processing, a database process may be actively servicing a request on the CPU, it may be off the CPU waiting for a certain resource to be available or delivered, or it may be waiting for a new instruction on what to do next . Each time a process has to wait for something, Oracle collects statistics about the wait. Wait event statistics are made available in several V$ views, which are discussed in detail in Chapter 2. The wait event information lets you quickly identify the major bottlenecks that plague a process so you can find the appropriate solution to solve the problem. For instance, it doesn ‚ t take a rocket scientist to figure out why the following job is running slow if you have these statistics:

 EVENT                        TIME_WAITED 
---------------------------- -----------
latch free 1,142,981
db file sequential read 727,075
enqueue 31,650
db file scattered read 3,712
log file switch completion 2,328
local write wait 801
direct path write 138
free buffer waits 66
control file sequential read 28
direct path read 21
log file sync 20
file open 8

It is a no-brainer. The job performed badly because it spent too much time competing for latches.

The ability to quickly identify the major bottlenecks that slow a process down has revolutionized performance tuning and greatly reduced troubleshooting time. Before OWI, troubleshooting was merely following a checklist. That usually involved running a few queries that calculated some ratios and checked for high resource-consuming SQL statements. The problem with checklist tuning was that you almost always found issues with every item on the list. Since the items were not quantified based on time, you simply didn ‚ t know which one would yield the best return for your tuning effort. So you usually picked one according to your best judgment, and it was a toss-up. Troubleshooting and problem solving was tedious and time consuming. You spent a lot of time tuning a lot of things in vain. But now, OWI clocks every bottleneck and you easily can sort them to find the significant ones.

Pointing the DBA directly to the major bottlenecks not only saves a lot of troubleshooting time, it also, more importantly, helps the DBA not bark up the wrong tree. If you see that a process spent the majority of its time competing for latches and a minor amount of time waiting for I/Os, should you really spend your time redoing your I/O subsystem? With OWI, you can now focus your effort on the items that yield large performance returns. This translates into lower turnaround time for problem solving, lower operational costs, and better customer service.

The OWI philosophy not only brings with it a new way of monitoring, measuring, and tuning your databases, it also gives you a new way of communicating performance. Now, you can sit down with your customers and show them the list of bottlenecks or symptoms that their processes encounter, the amount of time wasted , and discuss the plan of action. For example, you can quantify the characteristic of a job, that it spent 60 percent of its time waiting on full table scans ( db file scattered read ) I/Os, 7 percent on index reads ( db file sequential read ), 5 percent on latch free, and so on. You can also show when a problem is not in the database. OWI lets you describe performance in a way that makes sense to everyone. When all the parties see and understand what the main problem is, it helps to bring about a speedy resolution. It also fosters collaboration between the various business entities.




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net