Oracle Log File Switch Completion


The Oracle log file switch completion wait event is the amount of time users and applications must wait for a log file switch to complete.

About the log file switch and Oracle redo logs

As users make changes to data, Oracle keeps a running log of these changes within a set of structures that make up what Oracle calls “redo”. These Redo Logs within Oracle are the critical link in the recovery process as they record the changes to data and provide the input to produce sets of archived logs that are used for point in time recovery. As redo logs fill to capacity and are readied for archival a log switch occurs that relinquishes control from the current redo log to the next available. A single log switch entails stopping further acceptance of generated of redo, reading & writing to control files for redo log status and SCN, (System Change Number), information, flushing memory, and the closing & opening of individual redo log files. Since there are many other resources in use when log switching occurs it is imperative that we are not fooled by high resource usage on these erroneous resources.

There is no record in Oracle that pinpoints the amount of time it takes for a log file switch. It is only experienced by users or applications and is recorded as a log file switch completion wait event at a session or system level. Other than individual sessions generating more redo through improper SQL applications this wait event can only be reduced by attacking it from a full system perspective.

Often the performance impact of switching too many redo logs is the culprit. A good rule of thumb is to switch a redo log every 20 to 30 minutes. Listing 1 gives a SQL statement that will show how often log file switching is occurring every hour. You need only increase your redo log sizes to hold more redo and this will reduce the amount of log file switching.

Listing 1: Log switching history

select to_char(first_time,'YYYY-MON-DD') day,
 to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
 to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
 to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
 to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
 to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
 to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
 to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
 to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
 to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
 to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
 to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
 to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
 to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
 to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
 to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
 to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
 to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
 to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
 to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
 to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
 to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
 to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
 to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
 to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
 from v$log_history group by to_char(first_time,'YYYY-MON-DD');

Often times the placement of redo log groups on disk is not optimal for the subsystem. Optimally each group member should be assigned to its own disk. This is the only way to eliminate the contention of writing redo and reading redo for archival purposes. Use the SQL in Listing 2 to determine where on disk the redo log groups are. After you have pinpointed them, use a disk monitoring utility such as iostat during high redo activity to determine if you have any contention for reads and writes. Alternatively you could use a disk cache to speed I/O operations.

Listing 2: Redo Log placement

select group#, status, member 
 from sys.v$logfile ORDER BY group#;

Before diving into disk monitors, you can gain benefit from the SQL in Listing 3. This SQL will display sizes and statuses of the current configuration and use of redo logs. Of importance is the FIRST_CHANGE#. This column shows the first SCN number that was used for a particular redo log group. Use it to verify two redo log groups are not on the same disk that are used next to each other in the redo log rotation.

Listing 3: Redo Log usage order

select group#, bytes, archived, status, first_change#, first_time from v$log order BY first_change#;

During the log file switch operation, the control file is used quite extensively. It has always been recommended that practitioners multiplex control files through Oracle.

Done this way the log file switch operation must keep in sync all the control files defined and thus overhead is increased by a factor of the number of control files multiplexed. Mirroring control files at the operating system level will eliminate the reads and writes for each control file. Use the SQL in Listing 4 to determine the extra amount of I/O you are incurring for each log file switch (and many other operations). If you choose this route, schedule a backup of your control file to trace often enough for recoverability. Alternatively you could use a disk cache to speed I/O operations.

Listing 4: Control File placement

Listing 4 Control File placement

Listing 5: ARCHIVE_LAG_TARGET setting

select name, value 
 from v$parameter
where name = 'archive_lag_target';

Final thoughts

As data is manipulated and altered within an Oracle database, time must be spent recording and archiving these changes through redo log generation. The time spent recording and archiving can be reduced if proper consideration is given to the number of log file switches occurring, the size of the redo logs, how they are cyclically used, and taking a look at supporting structures such as the control file or init.ora settings.

Remembering to check periodically and around major application changes will safeguard your database from wasting time and waiting for a log file switch completion.



Leave a Reply