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