 |
|
Oracle wait
classes
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.
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_2005_1_awr_proactive_tuning.htm
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
