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

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

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:

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.
LOG_CHECKPOINT_INTERVAL: The number of operating system block in the redo log between the checkpoint position and the end of the redo log.
FAST_START_IO_TARGET: The number of I/O operations the database should take to perform crash recovery of a single instance.
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