||Oracle Tips by Burleson
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
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
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.
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
Figure 2.1 – Describe v$system_event
Here are explanations of some of the more
EVENT is the name of the event that is
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