How to Estimate the Size of Redo Logs
The best way to determine the optimal size of redo log files is to look into the alert.log file for messages about how frequently the log sequence# are changing, log check pointing and any incomplete redo log switch completion messages. When you examine your alert.log for log switches, you should do it when there are heavier loads in the database.
Every time Oracle does a log switch, it is recorded in the alert.log. The alert.log will have entries similar to the following:
Fri Feb 27 09:57:40 2019
ARC0: Evaluating archive log 1 thread 1 sequence 174
ARC0: Beginning to archive log 1 thread 1 sequence 174
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u02/app/oracle/product/64bit/9.2.0/dbs/arch/arc_1_174.src'
ARC0: Completed archiving log 1 thread 1 sequence 174
Fri Feb 27 09:57:52 2019
Thread 1 advanced to log sequence 176
Current log# 3 seq# 176 mem# 0: /u02/oradata/xxxx/redo03.log
Fri Feb 27 09:57:52 2019
ARC1: Evaluating archive log 2 thread 1 sequence 175
ARC1: Beginning to archive log 2 thread 1 sequence 175
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u02/app/oracle/product/64bit/9.2.0/dbs/arch/arc_1_175.src'
ARC1: Completed archiving log 2 thread 1 sequence 175
Fri Feb 27 09:58:07 2019
Redo log switching should occur approximately every 20-30 minutes to get better database performance. If they switch too frequently, you will need to check the current size of redo log files from V$LOG and based on how frequently it is switching on an average, you will need to create redo log groups with bigger size and then switch the redo log a few times with “ALTER SYSTEM SWITCH LOGFILE;” so that the CURRENT redo log is one of the bigger redo log file and then drop the redo log groups with smaller size with ALTER DATABASE DROP LOGFILE … command.
The above snippet from alert.log file shows that the current redo logs are not big enough. Good advice/TIP would also be to spread the redo log files over multiple physical disks in order to improve performance during log switches. Please keep in mind that too frequent checkpoints and log file switch completion problems will affect database performance.
If there are waits on log file switch completion, you will need to add one or more redo log groups also to resolve this wait event in the Statspack report.