Wait Event Docs

Tuning Databases


Other wait docs

Oracle Wait Groups and their wait events:

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
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 
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: pin X                         
cursor: pin S wait on X  -                  
latch: library cache - usually indicates too much hard parsing
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

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             

SQL*Net message from client - idle event, though it can hide network latency and application processing time
TCP Socket (KGAS)
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      

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

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.

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

Other Stuff

Bottleneck but no Waits

         Unsetting (reseting) init.ora/spfile parameters - alter system reset parameter <scope=memory|spfile|both> sid=’sid|*’

Other related performance topics on Oracle
statspack top 5 events examples: 
More things you can get from statspack/AWR, the profile section, for example

A few things immediately stand out on this load profile:
        • this is not a really busy system
        • there is virtually nil I/O to/from the datafiles
        • it is using bind variables (hard parses is low)
        • it is still making parse calls for every statement (parses=executes)
        • it commits or rollbacks very aggressively (transactions high compared to executes)

SQL Server

Off Site documentation:


States from APPL_STATUS

UOWEXEC -  Workload occurrence is processing a request.
UOWWAIT -  Workload occurrence is waiting for a request from the client.

Subpages (52): View All