Home > Workload Solutions > Oracle > Best Practices > Intel-Based Oracle Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Day One Best Practices > Oracle 19c Database: Increase the Redo Log Size
In this best practice we increased the size of the Oracle redo logs to improve database performance. Proper sizing of the database redo logs can reduce wait events in the database, optimizing the database.
Category | Oracle 19c Database |
Product | Oracle 19c |
Type of best practice | Performance Optimization |
Day and value | Day 1, Highly Recommended |
Overview
The database redo logs consist of at least two files that are responsible for storing updates made to the database. Redo logs are critical as they protect data and the database from failure.
A log file sync event is the time it takes for the log writer (LGWR) to write to the redo log file. The log file sync wait events for the Oracle databases we used to validate the best practices showed a higher than average frequency and duration of average wait times.
The log file sync event is the time it takes for the log writer (LGWR) to write to the redo log file. With the number of wait events being high and the average wait time for each log file sync also high there was an opportunity for performance tuning. We changed the size of the log files from 200 MB to 8 GB to reduce the time and occurrence of wait events and improve performance.
In this case the original size of the log files was 200 MB and they were replaced with log files of 8 GB in size.
Recommendation
Increasing the log file sizes demonstrated improvements in the following metrics:
Increasing the size of the redo log from 200 MB to 8 GB significantly increased performance. Both NOPM and TPM increased by more than 40%. Array IOPS also increased with a corresponding drop in average array read and write times. Overall server utilization increased which inhibited significant CPU increases. Finally, both DB file Sequential Read and Log File Parallel Write times decreased average wait times.
As performance improvements were very strong across all metrics, we recommend this best practice as a Day 1, Highly Recommended practice.
Note: Not every database will show significant performance increases when increasing the size of the redo logs. Customers should evaluate the AWR to determine if the redo logs have an associated high number and duration of wait times. If the redo logs show a possible bottleneck, then increasing the size of the redo logs could improve performance.
Implementation Steps
We recreated five new redo log groups each in 8 GB size and dropped the three original smallest size of redo log groups by performing the following SQL commands with system or sys privilege:
ALTER DATABASE ADD LOGFILE GROUP 4('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 5('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 6('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 7('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 8('+REDO') SIZE 8G;
ALTER DATABASE DROP LOGFILE GROUP 1,
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;