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.
SQL for Log Switching Distribution
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
A few notes about setting FAST_START_MTTR_TARGET
- Specify a value in seconds (0-3600) that you wish Oracle to perform recovery within.
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.
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.
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.