Redo Log Buffer Tuning

SQL> select retries.value/entries.value  "redo buffer retries ratio"

           from v$sysstat retries,

          v$sysstat entries

          where retries.name='redo buffer allocation retries'

          and entries.name='redo entries';

 

redo buffer retries ratio

-------------------------

                        0

Redolog buffer retry ratio should be <1% otherwise need tuning.

 

SQL> select sid,wait_time from v$session_wait;

       SID  WAIT_TIME

---------- ----------

        11         -1

         1          0

         2          0

         3          0

         6          0

         7          0

        10          0

         9          0

         4          0

         5          0

         8          0

11 rows selected.

 

If wait_time for accessing redolog buffer is gathering than 0 second and less than 1 second then, the value of wait_time will be -1.

 

Tuning Redo Log Buffer steps:

 1.make it bigger

2.improve efficiency of checkpoints.

3 Speed up archiving

4:Reduce redo generation

5: Add more redolog file

 

Sloution 1.make it bigger

Oracle 10g the log_buffer parameter is not to be directly set anymore. Instead Oracle automatically calculates internally how big this buffer should be. So implement it previous version of Oracle 10g.


SQL> show parameter log_buffer; 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_buffer                           integer     524288

 

Note: Log_buffer is not dynamic

 

SQL> alter system set log_buffer=4194304 scope=spfile;

 System altered.

 

SQL> startup force;

ORACLE instance started.

 Total System Global Area  139008884 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                4337664 bytes

Database mounted.

Database opened.

 

 SQL> show parameter log_buffer;

 NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_buffer                           integer     4194304

 

Solution 2.improve efficiency of checkpoints.

The maximum length of the checkpoint queue is determined by the parameter from the following list that provides the shortest recovery time:

  1. LOG_CHECKPOINT_TIMEOUT: The number of seconds that has passed between the checkpoint position and the last write to the redo. No block in the buffer cache will be dirty longer than this time.
  2. LOG_CHECKPOINT_INTERVAL: The number of operating system block in the redo log between the checkpoint position and the end of the redo log.
  3. FAST_START_IO_TARGET: The number of I/O operations the database should take to perform crash recovery of a single instance.
  4. FAST_START_MTTR_TARGET: The average number of seconds the database should take to perform crash recovery of a single instance.

 

 SQL> show parameter log_checkpoint_timeout;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_timeout               integer     1800

 

SQL> show parameter log_checkpoint_interval;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_interval              integer     0

 

SQL> alter system log_checkpoint_timeout=30;

System altered.

 

SQL> alter system set log_checkpoint_interval=600;

 

System altered.

 

SQL> show parameter log_checkpoint_timeout;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_timeout               integer     30

 

SQL> show parameter log_checkpoint_interval;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_interval              integer     600

 

SQL> show parameter fast_start_mttr_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fast_start_mttr_target               integer     300

 

SQL> show parameter FAST_START_IO_TARGET;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fast_start_io_target                 integer     0

 

solution 3 Speed up archiving

10 destination 10 archive process

SQL> show parameter log_archive_max_process

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_max_processes            integer     2

 

SQL> select process,status from v$archive_processes;

 

   PROCESS          STATUS

----------------------------------------

         0                  ACTIVE

         1                 ACTIVE

         2                 STOPPED

         3                 STOPPED

         4                 STOPPED

         5                 STOPPED

         6                 STOPPED

         7                 STOPPED

         8                 STOPPED

         9                 STOPPED

 

SQL> alter system set log_archive_max_processes=10;

 

System altered.

 

SQL> select process,status from v$archive_processes;

 

   PROCESS         STATUS

---------- ----------------------------

         0                ACTIVE

         1                ACTIVE

         2                ACTIVE

         3                ACTIVE

         4                ACTIVE

         5                ACTIVE

         6                ACTIVE

         7                ACTIVE

         8                ACTIVE

         9                ACTIVE

 

10 rows selected.

 

Solution 4:Reduce redo generation

 

SQL> alter table scott.emp nologging;

 

Table altered.

 

SQL> alter table scott.emp logging;

 

Table altered.

 

SQL> alter tablespace users nologging;

 

Tablespace altered.

 

SQL> alter tablespace users logging;

 

Tablespace altered.

 

Solution 5: Add more redolog file

If required add more redolog file.

Comments