REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_log_stats.sql
REM
REM <SQLDIR_GRP>LOG</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Redo Log STATS
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_log_stats.sql
REM 
REM Example:
REM	s_log_stats.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

prompt +----------------------------------------------------------+
prompt | VALUE should be CLOSE to ZERO                            |
prompt | If not increase LOG_BUFFER parameter in the INIT.ORA     |
prompt +----------------------------------------------------------+


select  name
,       value
from    v$sysstat
where   name = 'redo buffer allocation retries'
or      name = 'redo log space requests'
/

prompt
prompt
prompt +----------------------------------------------------------+
prompt | "Ratio" of Requests to Entries should be < 1             |
prompt |if more increase LOG_BUFFER (4*OS_BLOCK_SIZE)<-- max size |
prompt +----------------------------------------------------------+

col name format a30
col value format a15

select (req.value*5000)/entries.value "Ratio"
from   v$sysstat req
,      v$sysstat entries
where  req.name        = 'redo log space requests'
and    entries.name    = 'redo entries'
/

select name, value
from v$system_parameter
where name like '%log_buff%'
/


prompt
prompt
prompt 
prompt /*
prompt | You can get same INFO from SRVMGR> SHOW PARAMETER LOG_CHECK 
prompt | ===========================================================
prompt |                                                             
prompt | COMMENTS:
prompt |	to see when check points occure set in the INIT.ORA         
prompt |	  LOG_CHECKPOINTS_TO_ALERT=TRUE                             
prompt |	  you'll get it in the ALERT FILE !!!                       
prompt |	to turn EXTRA checkpoints OFF set:                          
prompt |	   LOG_CHECKPOINT_INTERVAL=<LARGEST LOG SIZE> + SOME        
prompt |	   LOG_CHECKPOINT_TIMEOUT=0                                 
prompt |
prompt | EXAMPLE:
prompt |	# logfile_size/512 + some to avoid check points
prompt |	# 160*1024*1024/512 = 327680 (make it 330000)
prompt |	log_checkpoint_interval = 330000
prompt |	
prompt |	# get rid of unwanted CKP
prompt |	log_checkpoint_timeout = 0
prompt |
prompt */

select name, value
from v$system_parameter
where name like '%log_check%'
/

set lines 132
set trims on

select * from v$log;