SET ECHO off; REM *********************************************************************************** REM FILENAME : sc_tuning_redologs.sql REM AUTHOR : Joe Dunster REM REM REQUIREMENTS: REM Oracle 9i, SELECT on DBA_ and V$ views REM REM PURPOSE: REM The script provides a report showing information required to tune redo logs REM *********************************************************************************** set feed off; set pagesize 10000; set wrap off; set linesize 400; set heading on; set tab on; set scan on; set verify off; Prompt Prompt 1). Redolog Buffer Contention Prompt Prompt misses/gets must be < 1% Prompt immediate_misses / (immediate_gets + immediate_misses) must be < 1% Prompt (some DBA's recommend tuning when it reaches 0.01%) Prompt SELECT SUBSTR(name,1,20) "Name",gets,misses, immediate_gets,immediate_misses FROM v$latch WHERE name in ('redo allocation', 'redo copy'); Prompt Prompt 2). Waits on Redo Log Buffer Prompt Prompt The value of 'redo log space requests' reflects the number Prompt of times a user process waits for space in the redo log buffer. Prompt Optimal is if the value is near 0 (Oracle Manual says this ...) Prompt If it is not close to 0 increase the size of the log_buffer. Prompt You may get a high value when hot backups are occuring as whole blocks Prompt are written to the redo logs. Prompt If this is occuring during hot backups look into the parameter _LOG_BLOCKS_DURING_BACKUP Prompt SELECT name,value FROM v$sysstat WHERE name = 'redo log space requests'; Prompt Prompt 3). Redolog File status and size Prompt select group#, sequence#, Members, archived, status, first_time, bytes from v$log; Prompt Prompt 4.) Parameter values and recommendations Prompt Prompt LOG_CHECKPOINT_INTERVAL Prompt This should be be greather than redo log file size or set to 0, so that checkpoints only occur at redo switch. Prompt In versions prior to 9i set to a value greater than the redo log size. Prompt set heading off select 'log_checkpoint_interval = ', substr(value,1,20) from v$parameter where name = 'log_checkpoint_interval'; Prompt Prompt LOG_CHECKPOINT_TIMEOUT Prompt Triggers a checkpoint, if one has not occured for the specified number of seconds, default is 1800 (30min) on enterprise edition. Prompt Oracle recommends not setting to 0, as this disables it. Prompt In versions prior to 9i set to 0. Prompt select 'log_checkpoint_timeout = ', substr(value,1,20) from v$parameter where name = 'log_checkpoint_timeout'; Prompt Prompt LOG_BUFFER Prompt The amount of memory, in bytes, used to buffer redo log entries. Prompt A large log buffer reduces I/O on the redo log files, in a busy system a value of 65536 or higher is reasonable. Prompt select 'log_buffer = ', substr(value,1,20) from v$parameter where name = 'log_buffer'; Prompt Prompt LOG_CHECKPOINTS_TO_ALERT Prompt Specifies if the checkpoints are being written to the alert file. Prompt select 'log_checkpoints_to_alert = ', substr(value,1,20) from v$parameter where name = 'log_checkpoints_to_alert'; set heading on Prompt Prompt 5). No of log switches per hour Prompt select to_char(first_time,'YYYY.MM.DD') day, to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'99') "00", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'99') "01", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'99') "02", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'99') "03", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'99') "04", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'99') "05", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'99') "06", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'99') "07", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'99') "08", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'99') "09", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'99') "10", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'99') "11", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'99') "12", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'99') "13", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'99') "14", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'99') "15", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'99') "16", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'99') "17", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'99') "18", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'99') "19", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'99') "20", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'99') "21", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'99') "22", to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'99') "23" from v$log_history group by to_char(first_time,'YYYY.MM.DD'); set feed on; set wrap on; set linesize 200; set heading on; set tab on; set scan on; set verify on; set echo on;