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';
This entry was posted in Oracle on by .

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.