#Enbarcadero db optimizer full#

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. 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 SQL*Net message from client - idle event, though it can hide network latency and application processing time

#Enbarcadero db optimizer full#

Look into application logicД¬ommit log file sync - too many commits and/or log device is slowД¬oncurrency 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Д¬onfiguration 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 log buffer space log file switch (archiving needed) log file switch (checkpoint incomplete) log file switch (private strand flush incomplete) log file switch completion

Oracle Wait Groups and their wait events:ДЄpplication 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. RAC events of course are worth documenting but all good things in time. I have also left out idle events, background events, PQO events and RAC events. 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. In the following list is an evolving document originating with my 2 day Oracle 10g Performance Tuning class.

Wait Event Docs Tuning Databases New Way Old Way -( under construction Sampling ASH Idle Waits CPU IO Waits Oracle: Network Waits Oracle:Enqueues Oracle:Redo Log Waits Oracle:Buffer Busy Waits ASH Family of Tables ASH Across the Versions Data Mining Scripts -> S-ASH AAS Tools Oracle