Wait Class
- A wait class is a broad categorization of wait events. Oracle categorizes similar wait events under a common wait class, making it easier to focus on groups of waits rather than individual wait events.
- Common Wait Classes:
- User I/O: Waiting for I/O operations initiated by user processes, such as reading blocks from disk.
- System I/O: I/O waits that are not directly user-initiated, often for background tasks like logging.
- Application: Waits caused by application design, such as enqueues (locks).
- Concurrency: Waits due to resource contention, such as library cache and row-level locks.
- Configuration: Waits that suggest possible configuration issues, like
log buffer space.
- Administrative: Waits related to database management tasks like backups or statistics gathering.
- Network: Waits for network operations, typically involving data transfers across network links.
- Idle: Waits for tasks waiting for an event that may not directly impact performance, like
SQL*Net message from client.
Common Wait Classes in Oracle:
SQL> select event,WAIT_CLASS from v$SYSTEM_EVENT order by WAIT_CLASS;
EVENT WAIT_CLASS
---------------------------------------------------------------- --------------------
index (re)build lock or pin object Administrative
OFS operation completion Administrative
SQL*Net break/reset to client Application
enq: RO - fast object reuse Application
log file sync Commit
row cache mutex Concurrency
row cache lock Concurrency
cursor: pin S Concurrency
cursor: pin S wait on X Concurrency
library cache load lock Concurrency
buffer busy waits Concurrency
latch: shared pool Concurrency
latch: cache buffers chains Concurrency
log buffer space Configuration
Space Manager: slave idle wait Idle
lreg timer Idle
jobq slave wait Idle
PX Idle Wait Idle
SQL*Net message from client Idle
Streams AQ: load balancer idle Idle
heartbeat redo informer Idle
LNS ASYNC dest activation Idle
Data Guard: Timer Idle
Data Guard: Gap Manager Idle
DIAG idle wait Idle
pman timer Idle
pmon timer Idle
watchdog main loop Idle
class slave wait Idle
wait for unread message on broadcast channel Idle
VKRM Idle Idle
OFS idle Idle
rdbms ipc message Idle
smon timer Idle
PL/SQL lock timer Idle
Streams AQ: waiting for time management or cleanup tasks Idle
Streams AQ: qmn coordinator idle wait Idle
Streams AQ: qmn slave idle wait Idle
AQPC idle Idle
SQL*Net message to client Network
latch free Other
latch: call allocation Other
enq: PR - contention Other
PGA memory operation Other
latch: messages Other
rdbms ipc reply Other
latch: enqueue hash chains Other
asynch descriptor resize Other
resmgr:plan change Other
reliable message Other
KSV master wait Other
enq: PV - syncstart Other
oracle thread bootstrap Other
os thread creation Other
DLM cross inst call completion Other
control file heartbeat Other
enq: CF - contention Other
latch: cache buffers lru chain Other
enq: CR - block range reuse ckpt Other
get branch/thread/sequence enqueue Other
Redo Transport Attach Other
Redo Transport Detach Other
Redo Transport Open Other
LGWR wait for redo copy Other
latch: redo allocation Other
datafile move cleanup during resize Other
enq: TX - contention Other
instance state change Other
Compression analysis Other
ADR file lock Other
ADR block file read Other
ADR block file write Other
CRS call completion Other
AQPC: new master Other
AQ Background Master: slave start Other
Data Guard Broker Wait Other
enq: RF - synch: DG Broker metadata Other
AWR Flush Other
enq: JG - queue lock Other
resmgr:cpu quantum Scheduler
db file async I/O submit System I/O
control file sequential read System I/O
Log archive I/O System I/O
control file parallel write System I/O
log file parallel write System I/O
log file single write System I/O
log file sequential read System I/O
db file scattered read User I/O
db file single write User I/O
db file parallel read User I/O
direct path read User I/O
direct path write User I/O
direct path write temp User I/O
utl_file I/O User I/O
external table read User I/O
external table write User I/O
db file sequential read User I/O
read by other session User I/O
local write wait User I/O
Data file init write User I/O
DG Broker configuration file I/O User I/O
direct path sync User I/O
external table open User I/O
Parameter File I/O User I/O
Disk file operations I/O User I/O
105 rows selected.
SQL> !cat event.sql
select event,count(*) from v$session_wait group by event order by 2
/
SQL>
SQL> @event.sql
EVENT COUNT(*)
---------------------------------------------------------------- ----------
pman timer 1
wait for unread message on broadcast channel 1
SQL*Net message to client 1
lreg timer 1
VKRM Idle 1
OFS idle 1
Data Guard: Gap Manager 1
Streams AQ: qmn coordinator idle wait 1
heartbeat redo informer 1
AQPC idle 1
Streams AQ: waiting for time management or cleanup tasks 1
VKTM Logical Idle Wait 1
Data Guard: Timer 1
Streams AQ: qmn slave idle wait 1
smon timer 1
watchdog main loop 2
DIAG idle wait 2
LNS ASYNC dest activation 2
class slave wait 3
pmon timer 3
Space Manager: slave idle wait 8
rdbms ipc message 23
Find Top Wait Classes
User below query to get the top wait classes in Oracle database
select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
Sum_Waits
From v$system_wait_class
Group by wait_class
Order by 3 desc;
Find Top Wait Events in a Wait Class
From the above query, supply each wait class into below query to get the top wait events in database with respect to particular wait class
Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
And c.wait_class = '&Enter_Wait_Class'
order by average_wait desc;
No comments:
Post a Comment