Rem Filename   : redowait.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 19-Dec-2001  Initial creation
Rem 23-Jun-2003  Rewritten, added 8i specific comments.
Rem 09-Jun-2005  Enlarged some columns in redo log buffer event wait 
Rem              statistics.
Rem
Rem ========================================================================================
Rem
Rem Description : Lists an overview of 
Rem               - redo log buffer contention
Rem               - redo allocation latch contention
Rem               - redo copy latch contention
Rem               - redo log buffer and log files related event wait statistics
Rem
Rem               In addition it also shows some hints for corrective actions.
Rem
Rem ========================================================================================

SET PAGESIZE  0
SET TRIMSPOOL ON
SET FEEDBACK  OFF
SET VERIFY    OFF 

@@header_title 101 DCS 'REDO LOG BUFFER WAIT OVERVIEW'

COLUMN Username  FORMAT A12           HEADING "Username"
COLUMN Sid       FORMAT A4            HEADING "Sid"

alter session set nls_numeric_characters = ',.';

PROMPT
PROMPT REDO LOG BUFFER CONTENTION
PROMPT ==========================

SELECT 'The redo log buffer size = ' || value || ' (' || to_char(value)/1024 || ' Kbytes)'
  FROM v$parameter
 WHERE lower(name) = 'log_buffer';

PROMPT

SELECT 'Number of redo log space requests = ' || value || chr(10) || '
    The number of redo log space requests should be close to zero.
    If the number increases consistently during certain periods, then you
    should increase the log buffer size.'
  FROM v$sysstat  
 WHERE name = 'redo log space requests';  

PROMPT
PROMPT REDO LATCH CONTENTION
PROMPT =====================

SET PAGES 9999

COLUMN name             FORMAT A15             HEADING 'Latch'
COLUMN gets             FORMAT 999G999G999     HEADING 'Gets'
COLUMN misses           FORMAT   9G999G999     HEADING 'Misses'
COLUMN miss_ratio       FORMAT     990D999     HEADING 'Miss|Ratio'
COLUMN immediate_gets   FORMAT 999G999G999     HEADING 'Immediate|Gets'
COLUMN immediate_misses FORMAT   9G999G999     HEADING 'Immediate|Misses' 
COLUMN im_miss_ratio    FORMAT     990D999     HEADING 'Im Miss|Ratio'
COLUMN sleeps           FORMAT     999G999     HEADING 'Sleeps'

SELECT name,          
       gets,
       misses,  
       round((misses/(gets+0.00000000001) * 100),3) miss_ratio,
       immediate_gets,
       immediate_misses,
       round((immediate_misses/(immediate_gets+0.00000000001) * 100),3) im_miss_ratio,
       sleeps  
  FROM v$latch  
 WHERE name in ('redo allocation',  'redo copy')  
/  

SET PAGESIZE 0
PROMPT
PROMPT

SELECT 'Ratio of misses to gets =  ' ||  
        ltrim(to_char(round((sum(misses)/(sum(gets)+0.00000000001) * 100),2), '90D99'))
        || '%' || chr(10) || '
    This ratio should be less then 1%, else performance will be affected.
    In Oracle8i you need to consider increasing the size of the LOG_BUFFER or reducing 
    the load of the log buffer using NOLOGGING features when possible.
    In Oracle 7.x decreasing the value of the LOG_SMALL_ENTRY_MAX_SIZE (obsolete in 8i)
    reduces the number of processes copying information on the redo allocation latch, 
    which should have a positive effect on this ratio.'
  FROM v$latch  
 WHERE lower(name) in ('redo allocation', 'redo copy');


SELECT 'Ratio of immediate misses to immediate gets = ' ||  
        ltrim(to_char(round((sum(immediate_misses)/  
              (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2), '90D99'))
        || '%'  || chr(10) || '
    This ratio should be less then 1%, else performance will be affected.
    The redo copy latch is waited for on both single and multi-cpu systems. 
    On multi-cpu systems, contention can be reduced by increasing the 
    value of LOG_SIMULTANEOUS_COPIES (Hidden in Oracle8i) and/or increasing 
    LOG_ENTRY_PREBUILD_THRESHOLD (undocumented in Oracle7).'
  FROM  v$latch  
 WHERE  name in ('redo allocation', 'redo copy');



SET PAGESIZE 9999

COLUMN Event        FORMAT A38            HEADING "Event name"
COLUMN NbrWaits     FORMAT B99G999G999    HEADING "Times|waited"
COLUMN NbrTimeouts  FORMAT 9G999G999      HEADING "Timeouts"
COLUMN TotalTime    FORMAT 9G999G990D99   HEADING "Total time|waited (sec)"
COLUMN AvTime       FORMAT 9G990D99999    HEADING "Average time|waited (sec)"

PROMPT
PROMPT REDO LOG BUFFER AND LOG FILES RELATED EVENT WAIT STATISTICS
PROMPT ===========================================================

SELECT event               "Event",          
       total_waits         "NbrWaits",        
       total_timeouts      "NbrTimeouts",
       time_waited/100     "TotalTime",
       average_wait/100    "AvTime"
  FROM v$system_event 
 WHERE event like '%log file%'
    OR event in ('log buffer space','LGWR wait for redo copy')
 ORDER BY 1;

SET FEEDBACK   ON
SET PAGESIZE   9999