Oracle 10g’s Redo Logfile Sizing Advisor

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

James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles