Oracle Wait Interface, online !
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:
enq: TM - contention
table modification- table (object) lock, often caused by foreign keys lacking index
buffer busy wait - two users trying to modify the same block at the same time
pipe put - full pipe
latch: In memory undo latch -
Following are library cache related:
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: ST - contention
- space transaction, should not be an issue with with locally manage tablespaces
free buffer wait
- waiting for a free buffer in buffer cache to read data of disk into
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
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
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 free -
catch all for all other latch events with out their own wait event name
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
local write wait
- usually from truncating and waiting to clear out buffer cache
Bottleneck but no Waits
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)
UOWEXEC - Workload occurrence is processing a request.
UOWWAIT - Workload occurrence is waiting for a request from the client.