Oracle tune waits - log file sync

 

1.  Increase the Size of Log Buffer.

--Check the Size of log buffer

Show parameter log_buffer

--Increase the size of log_buffer if dynamic allocation then set minimum size 

alter system set log_buffer=4194304 scope=spfile;

--Restart the Oracle Database

Shutdown immediate;

Startup;

2. Increase the Archive Process for faster writing

--Check the current log archive process

show parameter log_archive_max_process

--It show active status of process get in above parameter

select process,status from v$archive_processes;

--Increase the archive process

alter system set log_archive_max_processes=10;

3. Reduce redo generation of unneeded tables like Logs table

--Disable logging

alter table scott.emp nologging;

--Enable logging

alter table scott.emp logging;

 

4. Tune the following parameter also help

LOG_CHECKPOINT_TIMEOUT

LOG_CHECKPOINT_INTERVAL 

FAST_START_IO_TARGET

FAST_START_MTTR_TARGET

5. Increase the size of redo logs

Online redo log files should be sizes to perform a log switch no more than twice per hour.

A high value of redo buffer allocation retries indicates that you may want to increase the size of the online redo log files.

--Value should be less than 1 otherwise you need to tune redo log files

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';