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:
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.