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

latch:row cache objects

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 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 lache 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        

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             

Idle

Network

TCP Socket (KGAS)

LGWR wait on full LNS buffer

LGWR wait on LNS

LGWR-LNS wait on channel

LNS wait on SENDREQ

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

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