188
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:
- 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
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINT_INTERVAL
- FAST_START_IO_TARGET
- FAST_START_MTTR_TARGET
5. Increase the size of redo logs
<https://smarttechways.com/tag/steps-to-change-size-of-redo/>
Note:
- 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';