Off Site Resources

From DB Optimizer
Jump to: navigation, search

wait events doc backup

Oracle - list with parameter values - with explanation Oracle Wait Interface - the whole book online


   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


   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


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


   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


   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            


   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 Sybase