Oracle Wait Events

Oracle
7 Comments

Knowing what a query has been waiting on helps you figure out where you need to tune that system. Oracle’s wait interface provides a significant amount of information to help a DBA decide where to put tuning resources. There are multiple ways to see what Oracle has been waiting on:

Wait Classes

Oracle waits are split into multiple classes. We can review total instance-wide totals for each wait class by using the v$system_wait_class view. This view aggregates information about an entire instance of Oracle since start up. The view only shows events at an event class level – this can make it valuable to determine if a problem is related to I/O or some other portion of the system.

This screen shot shows a relatively idle Oracle instance that isn’t up to much at all. It is, however, immediately apparent that waits are broken up into multiple wait classes. An explanation of the different wait classes is available in the Oracle Database Reference – Appendix C – Classes of Wait Events. Most scripts ignore events in the Idle wait class and we’re going to be doing the same.

System Waits using v$system_event

It’s possible to get more details about waits by using the v$system_event view. This view contains wait event timing across the system since the last restart. Although this only givings timings and average since the system started up, this information helps discover immediate trends:

Wait, why are we multiplying by 10 for the average wait columns? Well, the AVERAGE_WAIT columns are stored in hundredths of a second, and we’d like everything to be in one uniform number. We also have to divide microseconds by 1,000 since microseconds is a mindbogglingly tiny duration, I’ve gone and converted everything to milliseconds here.

There are more columns reported by v$system_event, but the ones in this query are likely to be the most interesting to beginners. The FG columns display the waits from foreground (user facing) sessions. Other waits are happening elsewhere in Oracle. They may be slowing down the system, but they are not directly caused by end users.

Single Session Waits

What if we want to know what’s going on within one session? What then?

The v$session_event view contains information about currently executing sessions. It’s remarkably similar to the v$system_event view:

v$session_event displays all of the waits for each active session. Obviously, we want to filter out the Idle waits since they’re idle and, for our purposes, ignorable.

v$waitclassmetric_history: A History of Waiting

What if we want finer granularity about how an Oracle instance has been performing? v$system_event lets us review waits since the beginning of database time. v$session_event lets us see waits for currently running queries. What else is there?

The view v$waitclassmetric_history gives us some insight into what’s happened over the last hour, but only by wait class. This isn’t as limiting as you might think – sometimes it’s only necessary to know that we were waiting on disk or concurrency. The data in v$waitclassmetric_history is broken down into one minute buckets.

Once again, we’re multiplying time_waited by 10 to get the time in milliseconds. These times are stored as floating point numbers, so we aren’t losing observable fidelity in our data.

The last minute of waiting
The last minute of waiting

Reviewing the screen shot, we can see a lot of information. This screen shot only shows the previous minute (which you could get from v$waitclassmetric), but when you query your own systems, you’ll see what I mean.

dba_hist: The Long View

If you’ve been licensed for the Oracle tuning and diagnostic packs, you have another tool at your disposal – the dba_hist views. Specifically, the dba_hist_system_event view. This contains a history of wait stats, summarized at one hour intervals, since you cleared out the data. This is part of Oracle’s AWR (Automatic Workload Repository).

The values stored in dba_hist_system_event are a running total of wait information. You can query it, but remember that these are cumulative values. To get helpful metrics for each hour, you have to do something like this:

Remember – this feature costs money, so don’t run it on your production machines unless you’re positive that you’re already licensed for it.

Putting it all together

By choosing the right view (and maybe the right licensing), you can review Oracle wait stats and start getting to the bottom of performance issues. Wait events aren’t the be all end all of Oracle performance tuning, but they are a good first step into determining where the problems lie.

Previous Post
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)
Next Post
What is the PREEMPTIVE_DEBUG Wait in SQL Server?

7 Comments. Leave new

  • Nice article (especially as I occasionally need to help our Oracle DBA sometimes) but it looks like your first screenshot (that should show wait classes) is something to do with Always-On Groups / Replication in SQL…

    Reply
  • Hi Jeremiah,

    I’ve only given this article a cursory reading so far, but this statement has me confused: “Wait, why are we multiplying by 10 for the average wait columns? Well, the AVERAGE_WAIT columns are stored in hundredths of a second, and we’d like everything to be in one uniform number. Since microseconds is a mindbogglingly tiny duration, I’ve gone and converted everything to milliseconds here.”

    Wouldn’t you need to multiply by 1000, to convert between micro and milliseconds?

    Reply
  • Or did you mean to say:
    Since hundredths (instead of “microseconds”) of a second are a mindbogglingly tiny duration…

    Reply
  • I wanted to see more of a train over a week period and see the avg_ms per wait.
    So here it is. Thanks for the query it’s super useful.

    select day, wait_class, number_of_waits, time_waited_ms, to_char(case when number_of_waits = 0 then 0 else time_waited_ms/number_of_waits end,’999999999D99′) avg_ms
    from
    (
    select trunc(snap.END_INTERVAL_TIME) as day,
    dhse2.wait_class,
    sum(dhse2.TOTAL_WAITS – dhse1.total_waits) AS number_of_waits,
    sum((dhse2.time_waited_micro – dhse1.time_waited_micro)) / 1000 AS time_waited_ms
    FROM DBA_HIST_SYSTEM_EVENT dhse1
    JOIN DBA_HIST_SYSTEM_EVENT dhse2 ON dhse2.snap_id = dhse1.snap_id + 1
    AND dhse2.wait_class_id = dhse1.wait_class_id
    AND dhse2.event_id = dhse1.event_id
    JOIN DBA_HIST_SNAPSHOT snap ON dhse2.snap_id = snap.snap_id
    WHERE snap.END_INTERVAL_TIME > SYSDATE – 7
    group by trunc(snap.END_INTERVAL_TIME),dhse2.wait_class
    order by wait_class, day desc
    );

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.