Wait Event Docs‎ > ‎

wait events doc backup


Oracle Wait Interface - the whole book online

alter rbs offline                             
buffer pool resize                            
enq: DB - contention                          
enq: TW - contention                         
enq: ZG - contention                        
index (re)build online cleanup             
index (re)build online merge              
index (re)build online start             
switch logfile command                  
switch undo - offline                 
wait for possible quiesce finish

SQL*Net break/reset to client - error in SQL statement
SQL*Net break/reset to dblink - error in SQL over DB link           
Wait for Table Lock                
enq: KO - fast object checkpoint  used by PQO to clear changes from buffer cache, reduce fast_start_mttr_target
enq: PW - flush prewarm buffers        
enq: RO - contention         
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
log file sync - too many commits and/or log device is slow

buffer busy wait  - two users trying to modify the same block at the same time                            
cursor: mutex S                             
cursor: mutex X                            
cursor: pin S wait on X                   
enq: TX - index contention               
latch: In memory undo latch             
latch: MQL Tracking Latch              
latch: Undo Hint Latch                
latch: cache buffers chains - over active datablock access - check SQL involved
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     
os thread startup          
pipe put                    
resmgr:internal state change 
resmgr:internal state cleanup
resmgr:sessions to exit     
row lache lock- if on Sequence cache, then find the sequence and increase number cached
row cache read    

checkpoint completed                         
enq: HW - contention  : High Water- contention on moving up the high water mark - move object to ASSM or add freelists
enq: SQ - contention                       
enq: SS - contention                      
enq: ST - contention                     
enq: TX - allocate ITL entry            
free buffer wait - waiting for a free buffer in buffer cache to read data of disk into
latch: redo copy                      
latch: redo writing    
log buffer space- log buffer too small or log device too slow
log file switch (archiving needed) - all DML hangs, archiver is stuck, usually because of full disk. Make space
log file switch (checkpoint incomplete) - log files too small
log file switch (private strand flush incomplete) - log files too small
log file switch completion - log files too small                                 
sort segment request                          
statement suspended, wait error to be cleared 
undo segment extension                       
undo segment tx slot                        
wait for EMON to process ntfns             
write complete wait - waiting for DBWR to finish writing a dirty block to disk , increase cache size or speed up DBWR
SQL*Net message to client - time to pack a message (no network time included) possibly tune SDU              
SQL*Net message to dblink                   
SQL*Net more data from client - possible network issues, possibly tune SDU
SQL*Net more data from dblink - possible network issues, 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 to dblink            

buffer exterminate - SGA cache is shrinking - add more memory to SGA
enq: CI - Cross Instance- indicative of high incremental checkpointing and waiting on blocks being checkpinted - increase fast_start_mttr_target
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

System I/O

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

SQL Server