Off Site Resources
wait events doc backup
Oracle
http://cs.scu.edu.cn/~zuojie/oracle/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
Administrative
alter rbs offline buffer pool resize enq: DB - contention enq: TW - contention enq: ZG - contention index (re)build online cleanup index (re)build online merge index (re)build online start switch logfile command switch undo - offline wait for possible quiesce finish
Application
SQL*Net break/reset to client - error in SQL statement
SQL*Net break/reset to dblink - error in SQL over DB link
Wait for Table Lock
enq: KO - fast object checkpoint used by PQO to clear changes from buffer cache, reduce fast_start_mttr_target
enq: PW - flush prewarm buffers
enq: RO - contention
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
cursor: mutex S cursor: mutex X cursor: pin S wait on X enq: TX - index contention latch: In memory undo latch latch: MQL Tracking Latch latch: Undo Hint Latch latch: cache buffers chains - over active datablock access - check SQL involved 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 os thread startup pipe put resmgr:internal state change resmgr:internal state cleanup resmgr:sessions to exit row lache lock- if on Sequence cache, then find the sequence and increase number cached row cache read
Configuration
checkpoint completed enq: HW - contention : High Water- contention on moving up the high water mark - move object to ASSM or add freelists enq: SQ - contention enq: SS - contention enq: ST - contention enq: TX - allocate ITL entry free buffer wait - waiting for a free buffer in buffer cache to read data of disk into latch: redo copy latch: redo writing log buffer space- log buffer too small or log device too slow log file switch (archiving needed) - all DML hangs, archiver is stuck, usually because of full disk. Make space log file switch (checkpoint incomplete) - log files too small log file switch (private strand flush incomplete) - log files too small log file switch completion - log files too small
sort segment request
statement suspended, wait error to be cleared
undo segment extension
undo segment tx slot
wait for EMON to process ntfns
write complete wait - waiting for DBWR to finish writing a dirty block to disk , increase cache size or speed up DBWR
Network
SQL*Net message to client - time to pack a message (no network time included) possibly tune SDU SQL*Net message to dblink SQL*Net more data from client - possible network issues, possibly tune SDU SQL*Net more data from dblink - possible network issues, 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 to dblink
Other
buffer exterminate - SGA cache is shrinking - add more memory to SGA enq: CF - contention enq: CI - Cross Instance- indicative of high incremental checkpointing and waiting on blocks being checkpinted - increase fast_start_mttr_target 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
System I/O
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
SQL Server
http://support.microsoft.com/kb/822101 http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc Sybase
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0/title.htm
DB2