 |
|
Oracle Tips by Burleson |
Background
The Wait Interface started to appear in the
Oracle RDBMS in version 7. However, the Wait Interface first
started to become more widely known with Anjo Kolk’s YAPP (Yet
Another Performance Profiling) Method paper published in 1999.
Since that time, many more people have written about it in technical
white papers and articles and spoken about it at user groups and
conferences.
So, What is the 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.
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 |
