Wait Events Defined
Wait Event Docs
Tuning Databases
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: 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
- 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.
- 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
- 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