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:
- For all time:
- In the last hour:
- For all time, broken down by hour:
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
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:
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
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?
v$session_event view contains information about currently executing sessions. It’s remarkably similar to the
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
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?
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.
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:
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.