 |
|
Oracle wait
class tips
Oracle Tips by Burleson Consulting |
Not All Events Are Created Equal
The information contained in the AWR is substantial and over 800
distinct wait events are tracked. To facilitate the use of these
events, they have been grouped into 12 areas called
Wait Classes. These
classes are listed in Table 16.2.
ADMINISTRATIVE Wait Class Events |
APPLICATION Wait Class Events |
Cluster |
Commit |
Concurrency |
Configuration |
Idle |
Network |
Other |
Scheduler |
System I/O |
User I/O |
Table 16.2:
Oracle10g wait event classes
More detailed ASH information on waits that are occurring in a
specific wait class is available in other areas of the repository.
The following script lists the specific wait events that are part of
each wait class.
break on wait_class skip 1
column event_name format a40
column wait_class format a20
select
wait_class,
event_name
from
dba_hist_event_name
order by
wait_class,
event_name;
With over 800 events, this could be a bit overwhelming, so it may be
advisable to filter this query with a WHERE clause to restrict the
output to the wait class that of the most interest to the DBA. Some
of the events for the System I/O and User I/O are shown below as an
example:
WAIT_CLASS EVENT_NAME
---------- ------------------------------
System I/O db file parallel write
io done
kfk: async disk IO
ksfd: async disk IO
log file parallel write
log file sequential read
log file single write
recovery read
User I/O BFILE read
buffer read retry
db file parallel read
db file scattered read
db file sequential read
db file single write
The listing above is useful if the AWR top five timed events report
indicates a significant amount of time spent on I/O related waits.
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. This shows the "landscape" of
what is happening on the database:
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
One also has to filter out wait events that are not helpful to the
tuning effort. In practice, idle events can be filtered out by
adding
where wait_class <> ‘Idle’
in the ASH queries. Table 16.3 below shows all system idle wait
events that have usually no meaningful information in Oracle
bottleneck analysis. One exception might be a batch program where
idle events may indicates that the program is doing something large
which makes RDBMS wait. Remember, SQL*Net message to client waits
almost always indicates network contention.
dispatcher timer |
lock element cleanup |
Null event |
parallel query dequeue wait |
parallel query idle wait - Slaves |
pipe get |
PL/SQL lock timer |
pmon timer |
rdbms ipc message |
slave wait |
smon timer |
SQL*Net E "SQL*Net" break/reset to client |
SQL*Net message from client |
SQL*Net E "SQL*Net" message to client |
SQL*Net more data to client |
virtual circuit status |
Table 16.3:
Oracle Idle events
The next section will provide a look inside the most useful ASH
tables for time-series wait event tuning.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|