Optimize / tune the log buffer space in Oracle

 Tune log buffer space or log file sequential read waits in Oracle

In AWR report, we are getting waits for log event.

Event	                   Waits  TotalWaitTime(s) Avg wait  Waits/txn %bgtime
log file parallel write	 695,202              579   833.17us      1.05	 47.39
log file sequential read  10,401                2   202.78us      0.02	  0.17

For tuning the LOG buffer, we have some tips:

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

--30 archive process in 18c
--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

1. LOG_CHECKPOINT_TIMEOUT
2. LOG_CHECKPOINT_INTERVAL
3. FAST_START_IO_TARGET
4. FAST_START_MTTR_TARGET

5. Increase the size of redo logs

<https://smarttechways.com/tag/steps-to-change-size-of-redo/>

Note:

1. Online redo log files should be sizes to perform a log switch no more than twice per hour.
2. 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';