RAC Tuning for redo logs

Tuning Online Redo Logs in a RAC environment

The Online Redo Log (ORL) for Oracle RAC is slightly different than a single-instance database. Most database administrators know that it is good practice to have at least three ORL groups for the database. What may not be obvious is that in a single-instance database, it is good practice to have at least three redo log groups per thread. Many people incorrectly think of threads as an Oracle RAC concept but even in a single-instance database, there is one thread of redo. All Oracle databases have one thread per instance.

Database administrators working on single-instance databases should strive for three to four redo log switches per hour. Switching too frequently can cause performance problems.

The same is true for Oracle RAC databases except that each instance should have three to four redo log switches per hour. The following script can be used in Oracle RAC databases to determine the max number of log switches in any one hour.

 declare

     cursor c1 is

     select

        inst_id,

        count(*)

     from

        gv$log

     group by

        inst_id,

        to_char(first_time,'YYYY-MM-DD HH24')

     order by

        inst_id;

     v_inst number;

     v_curr number;

     v_cnt  number;

     v_max  number;

     v_name varchar2(16);

  begin

     open c1;

     v_curr:=0;

     loop

        fetch c1 into v_inst,v_cnt;

        exit when c1%notfound;

        if (v_curr<>v_inst) then

           if (v_curr<>0) then

              select

                 instance_name into v_name

              from

                 gv$instance

              where

                 inst_id=v_curr;

           dbms_output.put_line('Instance: '||v_name||

                       '   Max Hourly Switches: '||v_max);

           end if;

           See code depot for full script

           v_max := -1;

           v_curr := v_inst;

        end if;

        if (v_max < v_cnt) then

           v_max := v_cnt;

        end if;

     end loop;

     select

        instance_name into v_name

     from

        gv$instance

     where

        inst_id=v_curr;

     dbms_output.put_line('Instance: '||v_name||

                 '   Max Hourly Switches: '||v_max);

  end;

/

Instance: orcl1   Max Hourly Switches: 5

Instance: orcl2   Max Hourly Switches: 8

Instance: orcl3   Max Hourly Switches: 3

PL/SQL procedure successfully completed.

The script above will work for any number of instances supporting the Oracle RAC database. Since each instance has its own thread of redo, we need to examine the maximum number of log switches per hour in each instance.

As previously stated, the online redo logs should be sized to see three or four log switches per hour at peak DML load. In the sample output above, there was at least one hour in one instance that experienced eight log switches. It may be a good idea to resize the online redo logs to twice the size so that the target of four log switches in an hour can be obtained.

Unfortunately, it is very common to see some Oracle RAC deployments that are experiencing sixty or more log switches in their busiest hours! Databases exhibiting this behavior should have the redo logs resized to be larger.   It is a best practice to have all redo logs sized the same for each thread and for each group in that thread.

Sizing the redo logs to meet the target of three or four log switches works well for the hour that has the highest redo generation rate. But what about the hour timeframe that has a very low redo generation rate? Some database administrators translate the three or four log switches per hour to a requirement that the redo logs should switch every fifteen or twenty minutes. As such, they often set the archive_lag_target parameter to 900 or 1200.

From a performance perspective, there is little reason to ensure the redo logs switch every fifteen or twenty minutes even during period of low redo generation. The only reason to force a log switch regularly, even when the redo log group is not full, is for archiving the redo on a regular schedule, not to improve performance.

If the services are configured differently for each instance, then it is likely each instance experiences different redo generation rates. For example, one instance may have 20GB of redo generated in one hour and another instance only has 1GB of redo generated in one hour. The database administrator may create redo logs of 5GB each for the first thread and 300MB each for the second thread.

Note that having different redo log group sizes for each thread can be problematic when the service fails over due to instance failure. If the first instance terminates, the second instance will find itself trying to handle 21GB of redo generated in one hour and will perform approximately 70 log switches in that hour. It is a good practice for the redo logs to be sized to handle the maximum redo generation rate imparted on it by any application workload taking into account service relocations due to instance failures.

This also means that all threads would have the same size redo logs groups. If You are worried that the second instance with 1GB of redo in its busiest hour would need five hours with the 5GB redo log groups, then turn to the archive_lag_target parameter to ensure the logs switch more often. Again, this parameter is not needed for performance reasons. It is needed for backup and recovery reasons to ensure an archived copy of the redo log is written to a backup location for disaster recovery purposes.

For those databases configured in archive log mode, the redo log switch will prompt the archiver process to copy the redo log to the archive log destination. We need to keep in mind that for Oracle RAC, there are multiple threads of redo, and there will be multiple archives being generated simultaneously. There is a natural tendency to use only one archive log destination for all threads, but for databases that have a high rate of redo generation (high DML activity), a single archive log destination for all threads may become a bottleneck. It may be more beneficial if each thread has its own archive log destination but that is not always the case.