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.
