The New Fashion of Oracle Performance Optimization


Oracle debuted the Wait Interface in Release 7.0.12 with only 104 wait events. The number of wait events quickly grew to about 140 in Oracle8.0, about 220 in Oracle8 i Database, about 400 in Oracle9 i Database, and more than 800 in Oracle Database 10 g Release 1. (The actual number of wait events depends largely on your RDBMS configuration and the options that are installed.)

For those of you who have never used the OWI, the growth in the number of wait events with each new release should convince you that the OWI is the preferred tuning methodology. Don ‚ t forget this is not a cheap proposition for Oracle. OWI instrumentation requires the kernel developers to insert counters or sensors in strategic places within the kernel code to collect statistics whenever a process has to wait for resources. If you are still not convinced, then see if you can find the buffer cache-hit ratio (BCHR) in Oracle Database 10 g Enterprise Manager Performance screens. It is gone. Oracle has removed it. We ‚ d say that is the nail in the coffin for the cache-hit ratio based tuning method.

So if the OWI is so good, why has the methodology spread so slowly among Oracle DBAs? Why are there so few publications about it? Why doesn ‚ t Oracle aggressively promote the OWI? We believe documentation was the main factor that limited the popularity of OWI in the beginning. Initially, there wasn ‚ t any documentation, and OWI knowledge was pretty much limited to those in the kernel group at Oracle Corporation. OWI information wasn ‚ t made available in the Oracle documentation until version 8.0. Back then, whenever you mentioned OWI, most DBAs said, ‚“What ‚ s that? ‚½

However, in the mid-1990s, a group of individuals championed the teaching of OWI to the public with useful white papers, presentations, and training. Anjo Kolk and Shari Yamaguchi wrote the famous YAPP-Method (Yet Another Performance Profiling Method) white paper. Others who have contributed significantly include Juan Loaiza, Mogens N ƒ rgaard, Virag Saksena, Craig Shallahamer, Steve Adams, and Cary Millsap, just to name a few.

The OWI methodology continues to spread and increase in popularity as more DBAs find it to be extremely useful. At the turn of the century, OWI topics popped up all over the place, from local Oracle users groups to the International Oracle Users Group (IOUG), as well as in various magazines and articles. Statspack, introduced with Oracle 8.1.6, proudly displays the Top Wait Events on its summary page. Its documentation and papers published in Oracle magazine in November 2000 proposed the wait event-based tuning methodology.




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