Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 24, 2004

Oracle 10g's Redo Logfile Sizing Advisor

By James Koopmann

Improperly sized redo logs can impede the performance of your database and hamper recovery time in the event of a database crash. Oracle has given us yet another advisory that helps us to properly size redo logs, taking yet another tedious task from us.

Sizing Redo Logs in the Past

In the past, it was the responsibility of the DBA to understand the amount of redo that his/her system was going to generate at any given time and to limit the amount of switching to be once every 15 or 20 minutes during peak activity within the database. There was no concrete mechanism to determine the proper size of the redo logs, so in order to accomplish this DBAs would issue the following SQL in Table 1 to see the distribution of log switches that occurred within the different hours of the day. After looking at the output of the script, a DBA would blindly modify the sizes of the redo logs and rebuild the redo log groups, trying to make them a size that would give them a good distribution of log switches. The monitoring and tuning of log switches for a 15 to 20 minute interval would give DBAs the security of being able to perform crash recovery of the instance in a similar amount of time.

Table 1
SQL for Log Switching Distribution

col day for a10
select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'99') "23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')
/
DAY        00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
---------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
2004-07-30  0  0  0  0  0  0  2  0  0  0  1  0  1  0  0  1  0  0  0  0  1  0  0  0
2004-08-01  0  0  0  0  0  0  0  0  0  2  1  0  1  0  0  1  0 76 ##  1  6  0  0  0
2004-08-02  0  0  0  0  0  0  0  1 17  1  0  0  1  0  0  0  0  0  0  0  0  0  0  0
2004-08-03  0  0  0  0  0  0  0  3  1  1  1  0  0  0  1  0  0  0  1  0  0  0  0  0
2004-08-04  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 19  0  0  0  0  0  0  0  0
2004-08-05  0  0  0  0  0  0  0  0  0  0  0  2  0  1  0  0  0  0  1  0  0  0  0  0
2004-08-06  0  0  0  0  0  0  0  3  0  0  1  0  0  1  0  0  0  0  0  0  0  0  0  0
2004-08-09  0  0  0  0  0  0  0  0  0  0  0  0  0  2  0  0  0  0  0  0  0  0  0  0
2004-08-11  0  0  0  0  0  0  0  1  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0

Sizing Redo Logs with Oracle 10g

Oracle has introduced a Redo Logfile Sizing Advisor that will recommend a size for our redo logs that limit excessive log switches, incomplete and excessive checkpoints, log archiving issues, DBWR performance and excessive disk I/O. All these issues result in transactions bottlenecking within redo and performance degradation. While many DBAs' first thought is throughput of the transaction base, not very many give thought to the recovery time required in relation to the size of redo generated or the actual size of the redo log groups. With the introduction of Oracle's Mean Time to Recovery features, DBAs can now specify through the FAST_START_MTTR_TARGET initialization variable just how long a crash recovery should take. Oracle will then try its best to issue the proper checkpoints during normal system operation to help meet this target. Since the size of redo logs and the checkpointing of data have a key role in Oracle's ability to recover within a desired time frame, Oracle will now use the value of FAST_START_MTTR_TARGET to suggest an optimal redo log size. In actuality, the setting of FAST_START_MTTR_TARGET is what triggers the new redo logfile sizing advisor, and if you do not set it, Oracle will not provide a suggestion for your redo logs. If you do not have any real time requirement for recovery you should at least set this to its maximum value of 3600 seconds, or one hour and you will then be able to take advantage of the advisory. After setting the FAST_START_MTTR_TARGET initialization parameter a DBA need only query the V$INSTANCE_RECOVERY view for the column OPTIMAL_LOGFILE_SIZE value, in MEG, and then rebuild the redo log groups with this recommendation.

Simple query to show the optimal size for redo logs

SQL>  SELECT OPTIMAL_LOGFILE_SIZE
        FROM V$INSTANCE_RECOVERY
/ 
OPTIMAL_LOGFILE_SIZE 
--------------------
64          

A few notes about setting FAST_START_MTTR_TARGET

  • Specify a value in seconds (0-3600) that you wish Oracle to perform recovery within.
  • Is overridden by LOG_CHECKPOINT_INTERVAL:
    Since LOG_CHECKPOINT_INTERVAL requests Oracle to checkpoint after a specified amount of redo blocks have been written, and FAST_START_MTTR_TARGET basically attempts to size the redo logs in such a way as to perform a checkpoint when they switch, you can easily see that these two parameters are of conflicting interest. You will need to unset LOG_CHECKPOINT_INTERVAL if you wish to use the redo log sizing advisor and have checkpoints occur with log switches. This is how it was recommended to be done in the v7 days and really I can't quite see any reason for anything else.
  • Is overridden by LOG_CHECKPOINT_TIMEOUT:
    LOG_CHECKPOINT_TIMEOUT controls the amount of time in between checkpoints if a log switch or the amount of redo generated has not yet triggered a checkpoint. Since our focus is now on Mean Time to Recovery (MTTR) this parameter is no longer of concern because we are asking Oracle to determine when to checkpoint based on our crash recovery requirements.
  • Is overridden by FAST_START_IO_TARGET:
    Actually, the FAST_START_IO_TARGET parameter is deprecated and you should switch over to the FAST_START_MTTR_TARGET parameter.

As you can quickly see, the new redo log sizing advisor takes quite a bit of work away from the DBA. We do not have to think too hard about what size redo log files we should have, how often they should be checkpointing, or if we can recover in a pre-determined amount of time. We now need only set one parameter, FAST_START_MTTR_TARGET, and all our research is done for us. We now need only re-build the redo logs to the suggested size and maybe watch a bit to make sure that all service levels are being met.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM