Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

redo log sizing tips

Oracle Tips by Burleson Consulting

Don Burleson

Every DBA knows that the size of their redo logs is very important.  Too small, and the frequent log switches tie-up the LGWR, ARCH and DBWR background processes.  Too large and you risk losing data during an instance crash.

As a general rule, you size your online redo logs not to switch more then 5 times per hour during peak DML times.  Here is a script that measures redo log sizing and log switches:

Here are some sample scripts to display the log switch frequency:

set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
see code depot for full script
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD');

This log switch script is handy because it displays the log switch activity as a two-dimensional table, showing log switches by hours of the day and log switches by date:

LOG SWITCH FREQUENCY REPORT

DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18
----- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --
01/04 0 0 0 0 0 0 0 1 2 0 0 0 5 11 1 0 0 1 0
01/05 0 0 0 15 0 0 0 0 4 1 6 5 5 32 0 1 0 3 3
01/06 1 0 0 0 0 0 0 0 1 4 6 1 3 9 10 5 0 1 1
01/07 1 0 0 0 0 0 0 0 2 1 2 0 7 14 0 1 1 0 0
01/08 1 0 0 0 0 0 0 0 0 0 0 0 0 21 0 0 0 0 0
01/09 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
01/10 0 0 0 0 0 0 0 0 0 1 9 4 1 60 1 2 0 0 0
01/11 1 0 0 14 0 0 0 0 2 1 1 1 1 1 0 0 1 0 0
12/12 0 0 0 0 2 0 0 2 1 0 35 1 1 37 31 10 17 0 1


There are many ways to display the log switching frequency using v$log_history. This script display log switches in a linear format, useful for redo log sizing:

select
b.recid,

to_char(b.first_time,?dd-mon-yy hh24:mi:ss?) start_time,
a.recid,

to_char(a.first_time,?dd-mon-yy hh24:mi:ss?) end_time,
round(((a.first_time-b.first_time)*25)*60,2) minutes
from
See code depot for full script
v$log_history a,

v$log_history b
where
a.recid = b.recid+1

and
a.first_time between to_date(?2010-04-14:00:00:00′,?yyyy-mm-dd:hh24:mi:ss?)
and
to_date(?2010-04-15:00:00:00′,?yyyy-mm-dd:hh24:mi:ss?)

order by
a.first_time asc;


This script will show the hourly count of log switches:

col day format a15;
col hour format a4;
col total format 999;

select
to_char(first_time,?yyyy-mm-dd?) day,
to_char(first_time,?hh24′) hour,
count(*) total
from
See code depot for full script
v$log_history
group by
to_char(first_time,?yyyy-mm-dd?),to_char(first_time,?hh24′)
order by
to_char(first_time,?yyyy-mm-dd?),to_char(first_time,?hh24′)
asc;

 


Using the redo log sizing advisor

Oracle 10g introduced an advisory utility that allows you to specify your optimal mean time to recovery (MTTR) recovery interval and uses this to suggest the optimal redo log size.  In Oracle 10g the fast_start_mttr_target parameter is used.

Oracle recommends using the fast_start_mttr_target initialization parameter to control the duration of startup after instance failure. With 10g, the Oracle database can now self-tune check-pointing to achieve good recovery times with low impact on normal throughput. You no longer have to set any checkpoint-related parameters.

This method reduces the time required for crash recovery and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint. Administrators specify a target (bounded) time to complete the cache recovery phase of recovery with the fast_start_mttr_target initialization parameter, and Oracle automatically varies the incremental checkpoint writes to meet that target.

The target_mttr field of v$instance_recovery contains the MTTR target in effect. The estimated_mttr field of v$instance_recovery contains the estimated MTTR should a crash happen right away.

 For example,

SELECT
   TARGET_MTTR,
   ESTIMATED_MTTR,
   CKPT_BLOCK_WRITES
FROM
   V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES

----------- -------------- -----------------

         37             22            209187

Whenever you set fast_start_mttr_target to a nonzero value, and while MTTR advisory is ON, Oracle Corporation recommends that you disable (set to 0) the following parameters:

 LOG_CHECKPOINT_TIMEOUT

 LOG_CHECKPOINT_INTERVAL

 FAST_START_IO_TARGET


Querying the advisor

In addition to the MTTR information in v$instance_recovery we also have an important column called optimal_logfile_size, and we can query for this value at any time.  The value for optimal_logfile_size is expressed in megabytes and it changes frequently, based on the DML load on your database.  For example,

                                      
select
   optimal_logfile_size
from
   v$instance_recovery; 

OPTIMAL_LOGFILE_SIZE

256         

If your database is relatively stable, then you can use this suggested size and rebuild your online redo log files to match the value.  We would expect that a future version of Oracle will automate this and allow for dynamic re-sizing of online redo log files, but this is an issues because many Oracle systems expect the archived redo log files to always be the same size.


The sizing redo log files can influence performance because DBWR, LGWR and ARCH are all working during high DML periods.

A too small online redo log file size can cause slowdowns from excessive DBWR and checkpointing behavior.  A high checkpointing frequency and the "log file switch (checkpoint incomplete) can cause slowdowns.

Also see my notes on fixing frequent log switches

 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.