Wait Events Defined

From DB Optimizer
Jump to: navigation, search

Wait Event Docs

Tuning Databases

New Way
Old Way -(under construction
Sampling
ASH
Idle Waits
CPU
IO
Waits
Oracle: Network Waits
Oracle:Enqueues
Oracle:Redo Log Waits
Oracle:Buffer Busy Waits
ASH Family of Tables
ASH Across the Versions
Data Mining Scripts
--> S-ASH
AAS
Tools

Oracle

In the following list is an evolving document originating with my 2 day Oracle 10g Performance Tuning class. I'm only targeting wait events I've seen myself or heard about from others which is a small subset of the 1000+ events in Oracle these days. I have also left out idle events, background events, PQO events and RAC events. RAC events of course are worth documenting but all good things in time.

Oracle Wait Groups and their wait events:

Administrative


Application
SQL*Net break/reset to client - error in SQL statement
SQL*Net break/reset to dblink - error in SQL over DB link , see above
enq: KO - fast object checkpoint used by PQO to clear changes from buffer cache, reduce fast_start_mttr_target
enq: RO - fast object reuse reusable object- clearing cache for object drop/reuse, use gtt if possible, tune DBWR
enq: TM - contention table modification- table (object) lock, often caused by foreign keys lacking index
enq: TX - row lock contention Transaction locks- application issue, more than one users chaning same data
enq: UL - contention user lock- custom application locks. Look into application logic


Commit
log file sync - too many commits and/or log device is slow


Concurrency
buffer busy wait - two users trying to modify the same block at the same time
latch: cache buffers chains - over active datablock access - check SQL involved
os thread startup -
enq: TX - index contention
pipe put - full pipe
latch: In memory undo latch -
Following are library cache related:
cursor: mutex S -
cursor: mutex S cursor: mutex X -
cursor: pin S
cursor: pin X]
cursor: pin S wait on X -
latch: library cache - usually indicates too much hard parsing
latch: library cache lock -
latch: library cache pin -
latch: row cache objects -
latch: shared pool latch - latch used when allocating memory in shared pool, inidcative of too much hard parsing
library cache load lock - multiple users waiting for same SQL to be compiled
library cache lock - user trying to compile code and can't access it in exclusive access
library cache pin - user trying to compile code and can't access it in exclusive access
row cache lock - if on Sequence cache, then find the sequence and increase number cached


Configuration
enq: HW - contention  : High Water- contention on moving up the high water mark - move object to ASSM or add freelists
enq: SQ - contention - sequence, could be logon/logoff problem
enq: ST - contention - space transaction, should not be an issue with with locally manage tablespaces
enq: TX - allocate ITL entry - table or index having contention on # of ITL slots available in the blocks
Oracle: Free buffer wait - waiting for a free buffer in buffer cache to read data of disk into
sort segment request - possible sign of SMON busy
write complete wait - waiting for DBWR to finish writing a dirty block to disk , increase cache size or speed up DBWR
The following are all REDO LOG configuration issues
log buffer space
log file switch (archiving needed)
log file switch (checkpoint incomplete)
log file switch (private strand flush incomplete)
log file switch completion



Idle
SQL*Net message from client - idle event, though it can hide network latency and application processing time


Network
SQL*Net message to client - time to pack a message (no network time included) possibly tune SDU
SQL*Net more data to client - time to pack a message (no network time included) possibly tune SDU
SQL*Net more data from client - possible network issues, possibly tune SDU
DBlink Analogies to the above waits
SQL*Net more data to dblink - see above, over a db link
SQL*Net message to dblink - see above, over a db link
SQL*Net more data from dblink - possible network issues, possibly tune SDU


Other
buffer exterminate - SGA cache is shrinking - add more memory to SGA
enq: CF - contention - control file lock
enq: CI - Cross Instance - indicative of high incremental checkpointing and waiting on blocks being checkpinted - increase fast_start_mttr_target
enq: TX - contention - the "other" TX lock bucket - uses waiting on data file init write, active tx in database going to read only etc.
kksfbc child completion - related to high parsing, running 'insert into t value(1)' in a tight loop produces this (note error "value" instead of values)
latch: cache buffers handles - increase _db_handles_cached
latch: cache buffers lru chain - contention on the data block cache
latch free - catch all for all other latch events with out their own wait event name
SGA: allocation forcing component growth - SGA resizing wait
System I/O
Generally we don't have to worry about system IO. If there is a problem with system IO we will see the effects in USER wait events.
User I/O
data file init write - datafile size is being increased
db file parallel read - parallel read from multiple data files at same time
db file scattered read - multi blick read usuaull a full table scan or fast full index scan
db file sequential read - single block read usually index access or rowid acces, undo is also accessed this way
direct path read - reading into private memory outside of buffer cache, used by PQO
direct path read temp - reading data written to temp , usually a sort
direct path write -direct path writes, like loader, writing above the high water mark
direct path write temp - writing data to temporary tablespace, usually sorts
local write wait - usually from truncating and waiting to clear out buffer cache
read by other session - multiple users waiting for IO read off of disk


Bottleneck but no Waits

What do you do when waits don't seem to show anything?


Parameters

Unsetting (reseting) init.ora/spfile parameters

Off Site Resources:

http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10755/waitevents004.htm - list with parameter values
https://students.kiv.zcu.cz/doc/oracle/server.102/b14211/instance_tune.htm - with explanation

Oracle Wait Interface - the whole book online Other related performance topics on Oracle

Personal tools