In a Nutshell


All Oracle sessions will encounter waits for required resources. You cannot eliminate such waits entirely. OWI enables measurement of all such waits and provides a mechanism to report them. This information is critical to troubleshooting the root cause of such waits and to minimizing them. The less time a session spends waiting for a resource, the faster it may run, improving the response time to the end users, and that is the goal you are after.

The wait event views V$SYSTEM_EVENT, V$SESSION_EVENT, and V$SESSION_WAIT provide various levels of information that you can use to quickly identify sessions contributing to the wait times. The extended SQL trace (trace event 10046) facility nicely supplements these views to report all wait event ‚ related information in a trace file. The wait event information in this trace file can be summarized using the Oracle9 i Database tkprof utility.

Oracle Database 10 g Release 1 enhances OWI by classifying wait events into categories, such as I/O, Network, Application, Configuration, Idle, and so on. These classifications are useful for focusing your troubleshooting efforts on the affected areas. The new views V$SYSTEM_WAIT_CLASS, and V$SESSION_WAIT_CLASS provide wait time information by wait classes. The view V$SESSION_WAIT_HISTORY displays the 10 latest wait events encountered by each session, and the view V$EVENT_HISTOGRAM provides a nice analysis of the wait times for an event by showing how many times the event waited for a particular time interval. A quick glance at this information will tell you which events might be the possible hot spots.

As the number of wait events tracked in the Oracle kernel keeps increasing with each release, OWI will become increasingly more important. Wait event information is now part of V$SESSION view in Oracle Database 10 g Release 1. There is no doubt in our minds that OWI is here to stay, and it is the only way to quickly identify performance bottlenecks.




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