Home > Workload Solutions > Oracle > Best Practices > AMD-Based Oracle Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance 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 system.
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 a minimum of two files that are responsible for storing any updates made to the database. Redo logs are critical to the database as they protect data and the database from a failure.
The log file sync event is the time it takes for the log writer (LGWR) to write to the redo log file.
In the Oracle databases used for validating best practices the log file sync wait events had higher than average frequency and duration of wait times. To improve performance, we changed the log file size 200 MB to 8 GB.
Recommendation
Increasing the size of the redo log from 200 MB to 8 GB significantly increased performance in the following metrics:
Increasing the size of the redo log from 200 MB to 8 GB significantly increased performance. Both NOPM and TPM increase by more than 40%. Array IOPS also increased with a corresponding drop in average array read and write times. Overall server utilization increased which in turn inhibited CPU increases.
Finally, both db file sequential read and log file parallel write times also showed significant decreases in average wait times. As performance improvements were very strong across all metrics, we recommend this best practice as Highly Recommended Day 1 configuration activities.
Not every database will show significant performance increases when increasing the size of the redo logs. Customers should evaluate in the AWR if the redo logs have associated high number of waits and wait times. If the redo logs do show a possible bottleneck, then increasing the size of the redo logs could improve performance.
Implementation Steps
Use the following steps to update redo log size and files:
SQL> alter database add logfile group 4 '+REDO' size 8G; SQL> alter database add logfile group 5 '+REDO' size 8G; SQL> alter database add logfile group 6 '+REDO' size 8G; SQL> alter database add logfile group 7 '+REDO' size 8G; SQL> alter database add logfile group 8 '+REDO' size 8G;
SQL> alter system switch logfile; Based on the current redo log state and need make the old groups inactive SQL> alter system archive log group 1; SQL> alter system archive log group 2; SQL> alter system archive log group 3;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1; SQL> ALTER DATABASE DROP LOGFILE GROUP 2; SQL> ALTER DATABASE DROP LOGFILE GROUP 3; SQL> Exit
SQL> select group#,sequence#,bytes,archived,status from v$log;
Follow these steps for all the eight database nodes/VMs (rp2vm2, rp2vm3 to rp2vm9)
Additional Resources