Section 8.4. The Oracle Wait Interface | Optimizing Oracle Performance

8.4 The Oracle "Wait Interface"

In conferences held around the turn of this century, it was apparent that the popular fashion in Oracle "tuning" had taken a dramatic turn . In the year 2001, Oracle conference papers about the new "wait interface" equaled or outnumbered papers about the traditional utilization-based approaches. What is the "wait interface?"

Many performance analysts define the wait interface narrowly as the set of four new fixed views introduced to the public in Oracle 7.0.12:

V$SYSTEM_EVENT

V$SESSION_EVENT

V$SESSION_WAIT

V$EVENT_NAME

These fixed views indeed provide significantly important performance information, but they do not replace other information in the database, nor do they constitute a complete new interface to performance measurement. These fixed views merely provide more information to the performance analyst, helping us improve our response time model from the unreliable e = c + D model that we had to use in the 1980s, to the complete response time accounting model that we can use today:

The new fixed views do not contain any information about CPU capacity consumption or an Oracle kernel program's motives for such consumption (LIO calls, sorts, hashes, and so on). But of course that's okay, because this information already exists in V$SESSTAT and V$SYSSTAT . The new fixed views are designed to be used in union with the existing ones.

Defining the wait interface narrowly as the collection of four new V$ tables leads to unfairly restrictive propositions like this one:

You can't find some kinds of performance problems with the Oracle wait interface: CPU consumers like LIO hogs, sessions that wait for CPU, and sessions that wait for paging or swapping.

Of course you can no more find LIO hogs in V$SESSION_EVENT than you can find the names of your online redo log files in V$PROCESS . But, as you have seen, you can find CPU consumers like LIO hogs by using Oracle's fixed views or extended SQL trace data. You can even find sessions that wait for CPU, and sessions that wait for paging and swapping by understanding Oracle's extended SQL trace data.

When you use the term "wait interface," just make sure that you and the person you're talking to both know what you mean. When I use the term , I'm typically thinking about all of the Oracle operational timing data that I describe in Chapter 7. However, if the person you're talking to has a narrower definition, then you might have to do a little extra work to explain that what you mean is really a union of "working" and "waiting" data that can be obtained, for example, either from views like V$SESSTAT and V$SESSION_EVENT , or from extended SQL trace data.

Top