Oracle Tips by Burleson

Wait Interface

Wait Interface is the name that has been given to the mechanism in an Oracle database that allows the Remote DBA to look into the internal workings.  This allows the Remote DBA to see what various components are doing by looking at where they are spending their time waiting.  While some wait-related information can be gathered by other views, the three main views that examine the Wait Interface are:

• v$system_event

• v$session_event

• v$session_wait

Why are these three views the key?  A review of what information they each contain and a sample of their output may help illustrate their importance. Remember to enable timed statistics in the database by using the timed_statistics=TRUE parameter in the init.ora file or spfile or by altering the system using:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

Otherwise, there will be no timing information.  Oracle says there is a small impact on the system when this is turned on, but most agree that anyone on any recent Oracle version should generally leave this on all the time.  In theory, there may be a performance penalty for a mechanical speedometer in a car, but that doesn’t mean people looking for optimum performance remove their speedometer.  The information provided by the speedometer more than offsets its small performance impact.  Likewise, the performance impact of enabling timed_statistics compared to the availability of better information on how the database is performing. 

Before Oracle 9i, the default setting for this was FALSE, but by 8i, the majority of Remote DBA’s agreed that the cost was negligible and set this value to TRUE everywhere, including in their production environments.  As of Oracle 9i, it seems that Oracle Corporation agrees since the default is now TRUE.

v$system_event

The highest-level view is v$system_event.  Its structure is shown in Figure 2.1 below: 

SQL > desc v$system_event 

Name                 Null?    Type

 -------------------- -------- ---------------

 EVENT                         VARCHAR2(64)

 TOTAL_WAITS                   NUMBER

 TOTAL_TIMEOUTS                NUMBER

 TIME_WAITED                   NUMBER

 AVERAGE_WAIT                  NUMBER

 TIME_WAITED_MICRO             NUMBER

Figure 2.1 – Describe v$system_event 

Here are explanations of some of the more important fields:

• EVENT is the name of the event that is taking time.

• TOTAL_WAITS represents the total number of times this event has caused a process to wait.

• TIME_WAITED is the total number of centiseconds that processes have waited for this event.

• AVERAGE_WAIT is effectively the result of TIME_WAITED divided by TOTAL_WAITS.

In addition, there are a few pieces of information to be aware. 

First of all, not all events are worthy of attention in this view.  Some of the wait events that occur in a database can be ignored when looking at the whole system.  These are usually referred to as idle events since they usually do not occur unless the database is idle or waiting for work.  Theoretically, these events are not preventing any work from getting done and therefore could not possibly be performance bottlenecks.  An important thing to remember about idle events is that although sometimes they are treated as idle or not important, it is possible to increase the accuracy of measurement of these events so that it is possible to determine if they are truly idle or if they might be contributing or pointing out a performance problem.

The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Interface Analysis 

ISBN 0-9745993-7-9  

Stephen Andert 

http://www.rampant-books.com/book_2004_2_wait_tuning.htm