Question: What does the AWR table
dba_hist_service_wait_class do? How can I use
dba_hist_service_wait_class to see system wait information?
Answer: The script below uses
dba_hist_service_wait_class to display the waits by day for
a tiny Oracle system. Here is another service wait
class script to display waits by day using the AWR
dba_hist_service_wait_class table:
col c1 heading 'end|time' format a10
col c2 heading 'wait|class' format a20
col c3
heading 'time|waited' format 999,999,999,999
break on
c1 skip 2
select
trunc(end_interval_time) c1,
wait_class c2,
sum(time_waited) c3
from
dba_hist_service_wait_class
join
dba_hist_snapshot USING(snap_id)
group by
trunc(end_interval_time),
wait_class
order by
trunc(end_interval_time),
c3
desc;
Here is a sample of the output:
end wait
time
time class
waited
---------- -------------------- ----------------
31-OCT-12 Idle
2,569,604,134
System I/O
21,125,983
Other
134,514
User I/O
126,006
Concurrency
98,622
Commit
24,102
Scheduler
3,458
Configuration
1,273
Application
140
Network
126
01-NOV-12 Idle
3,202,677,185
System I/O
22,725,490
Other
138,425
User I/O
119,588
Concurrency
115,050
Commit
28,105
Configuration
3,991
Scheduler
3,211
Application
130
Network
117
