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:
- Right now:
v$session_event - For all time:
v$system_eventandv$system_wait_class - In the last hour:
v$waitclassmetric_history - For all time, broken down by hour:
dba_hist_system_event
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
select wait_class, event, total_waits AS bg_total_waits, time_waited_micro / 1000.0 AS time_waited_ms, average_wait * 10 AS average_wait_ms , total_waits_fg AS fg_total_waits , time_waited_micro_fg / 1000.0 AS fg_time_waited_ms , average_wait_fg * 10 AS fg_average_wait_ms from v$system_event where wait_class != 'Idle' order by time_waited_micro desc ; |
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:
|
1 2 3 4 5 6 7 8 9 |
select SID, wait_class, event, total_waits AS bg_total_waits, max_wait * 10 max_wait_ms , time_waited_micro / 1000.0 AS bg_time_waited_ms, average_wait AS bg_average_wait from v$session_event where wait_class != 'Idle' ; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select swc.wait_class, begin_time , end_time , SUM(wcmh.average_waiter_count) avg_waiter_count, SUM(wcmh.dbtime_in_wait) AS dbtime_in_wait , SUM(wcmh.time_waited) * 10 AS time_waited_ms, SUM(wcmh.wait_count) AS wait_count , SUM(wcmh.time_waited_fg) * 10 AS time_waited_fg_ms, SUM(wcmh.wait_count_fg) AS wait_count_fg from V$SYSTEM_WAIT_CLASS swc, v$waitclassmetric_history wcmh where swc.wait_class != 'Idle' AND swc.wait_class_id = wcmh.wait_class_id group by swc.wait_class, begin_time, end_time order by 2 desc, 3 desc, 6 desc ; |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select snap.END_INTERVAL_TIME, dhse2.wait_class, dhse2.event_name , dhse2.TOTAL_WAITS - dhse1.total_waits AS number_of_waits, (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 - 1 order by dhse2.snap_id desc ; |
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.
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…
Damn, you found me out.
I’ll fix that right meow.
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?
Or did you mean to say:
Since hundredths (instead of “microseconds”) of a second are a mindbogglingly tiny duration…
Probably meant this one. I’ll go back and re-read when I’m not on vacation.
Oh god, that’s terrible, i fixed it right now. Thank you!
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
);