Recent site activity

Wait Event Docs

Comment and ask questions by signing up  

Tuning Databases

     New Way
     Old Way - (under construction)
     Sampling  
     ASH
      -->  S-ASH
      -->  ASHMON
     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:


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

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

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

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

Bottleneck but no Waits

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

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:
I/O
PAGEIOLATCH_EX
Lock
Memory
Buffer
Other
WRITELOG
RESOURCE_SEMAPHORE_QUERY_COMPILE
RESOURCE_SEMAPHORE
EXECSYNC


Sybase


DB2
States from APPL_STATUS

CLOSE
COMMIT_ACT
COMP
DESCRIBE
EXECUTE
FETCH
OPEN
UOWEXEC -  Workload occurrence is processing a request.
UOWWAIT -  Workload occurrence is waiting for a request from the client.



Subpages (52): View All

Comments

kyle Hailey - Feb 8, 2010 6:08 PM

testing commments

kyle Hailey - Feb 18, 2010 10:18 PM

To use these comments at the bottom of the page, I have to register you on the site. Send me your email at kylelf@gmail.com and I'll add you to the contributors.