An Architect's vision

Query v$session for WAIT_TIME A nonzero value is the session's last wait time. A zero value means the session is currently waiting 0 - the session is currently waiting -2 - duration of last wait is unknown -1 - last wait was 0 - duration of last wait in 1/100th second units (10mS) SECONDS_IN_WAIT When WAIT_TIME = 0 then this shows the number of seconds spent in the current wait Note: We need to check Wait_Tiem = 0 - implies that sessions are waiting currently waiting and Seconds-In_Wait will specify how much time current session is waiting.

Here's an Oracle data dictionary query for these values: select p1 "File #", p2 "Block #", p3 "Reason Code" from v$session_wait where event = 'buffer busy waits'; If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should show the name and type of the segment: select owner, segment_name, segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1;

Log Buffer Space This wait occurs because you are writing Log Buffers fater than LGWR can write to the redo log files. Log Switches are very slow Solution 1. Increase log buffer; 2. Increase Log file size; 3. faster disk for redo logs Logfile Switch All commit requests are waiting for logfile switch (archiving needed) OR logfile switch (checkpoint incomplete) Cause 1. Ensure that archive disk is not full or slow 2. DBWR may be too slow due to I/O 3. Even though a logfile has been archived, it's contents still need to be written to disk via a checkpoint before it can be used again. This insures that instance recovery is possible (you would have to do media recovery after shutdown abort in some cases if this was not so). 4. So, what is happening is that your disks are not keeping up with the checkpoint volume. Until the checkpoints can complete, your database will just stall, waiting. Solution 1. You may need to add more larger redo logs 2. Need to...