Guessing redo log file sizes
I know that it sounds unscientific to say that I guess at redo log file sizes, but I'm not making random guesses, pulling values out of thin air.
When I create a new database, I look at other, working, databases where the size and transaction rate is about the same. I use those working databases as a pattern when I create the new one.
Oracle's starter database is created with only two redo log groups. I've never found that to be adequate, even for lightly used databases (< i.e., more than 10 users) databases. At a minimum, I use at least four redo log groups with a file size of 1MB each.
If I see
checkpoint not complete
messages in my database alert log, I will add even more groups.
For larger databases, with more users, I will increase the redo log file size as well as add more groups.
Determining the Optimal Size of Online Redo Log Groups
Try to size the online redo logs so that they switch anywhere from two to six times per hour. The V$LOG_HISTORY contains a history of how frequently the online redo logs have switched. Execute this query to view the number of log
switches per hour:
select count(*)
,to_char(first_time,'YYYY:MM:DD:HH24')
from v$log_history
group by to_char(first_time,'YYYY:MM:DD:HH24')
order by 2;
Here is a snippet of the output:
COUNT(*) TO_CHAR(FIRST
---------- -------------
1 2012:10:23:23
3 2012:10:24:03
28 2012:10:24:04
23 2012:10:24:05
68 2012:10:24:06
84 2012:10:24:07
15 2012:10:24:08
From the previous output, you can see that a great deal of log switch activity occurred from approximately 4:00 am to 7:00 am.
This could be due to a nightly batch job or users in different time zones updating data.
For this database the size of the online redo logs should be increased. You should try to size the online redo logs to accommodate peak transaction loads on the database.
The V$LOG_HISTORY derives its data from the control file. Each time there is a log switch, an entry is recorded in this view that details information such as the time of the switch and the system change number (SCN). As stated,
a general rule of thumb is that you should size your online redo log files so that they switch approximately two to six times per hour. You do not want them switching too often because there is overhead with the log switch.
Oracle initiates a checkpoint as part of a log switch. During a checkpoint the database writer background process writes modified (also called dirty) blocks to disk, which is resource intensive.
Then again, you do not want online redo log files never to switch, because the current online redo log contains transactions that you may need in the event of a recovery. If a disaster causes a media failure in your current online
redo log, you can lose those transactions that have not been archived.
Use the ARCHIVE_LAG_TARGET initialization parameter to set a maximum amount of time (in seconds) between log switches. A typical setting for this parameter is 1,800 seconds (30 minutes). A value of 0 (default) disables this feature.
This parameter is commonly used in Oracle Data Guard environments to force log switches after the specified amount of time elapses.
You can also query the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view to determine if your online redo log files have been sized correctly:
SQL> select optimal_logfile_size from v$instance_recovery;
Here is some sample output:
OPTIMAL_LOGFILE_SIZE
--------------------
349
This column reports the redo log file size (in megabytes) that is considered optimal, based on the initialization parameter setting of FAST_START_MTTR_TARGET. Oracle recommends that you configure all online redo logs to be at least the value of OPTIMAL_LOGFILE_SIZE.
However, when sizing your online redo logs, you must take into consideration information about your environment (such as the frequency of the switches).