In an ongoing effort to document the main Oracle wait events, here is my current effort at documenting Oracle’s redo log related wait events. The original article is at
and I will be making additional change and adding content as I have time.
Redo
Redo is written to disk when
User commits
Log Buffer 1/3 full (_log_io_size)
Log Buffer fills 1M
Every 3 seconds
DBWR asks LGWR to flush redo
Sessions Commiting wait for LGWR
Redo Log Wait Events
Log file Sync
Wait for redo flush upon:
Commit
Rollback
Arguments
P1 = buffer# in log buffer that needs to be flushed
P2 = not used
P3 = not used
Commit less
Often possible in loops that commit every loop
Commit every 50 or 100 instead
Put redo on dedicated disk
Use Raw Device or Direct IO
More Radical
Consider Ram Disks
Can stripe if redo writes are comparable to stripe size
Striping shouldn’t hurt
Striping can help
Ex: imp – can have large redo writes – can improve by 10-30%
Alternate disks for redo and archiving of redo
Possibly 10gR2
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
Commit could be lost if machine crash
Or IO error
log buffer space
Wait for space in the redo log buffer in SGA
Solution
Increase log_buffer parameter in init.ora
Above 3M log_buffer little affect, if still a problem then backup is at disk level
Improve disk IO for redo
Faster disk
Raw file
Direct IO
Dedicated disk
p1, p2, p3 – no values
log file switch (archiving needed)
No p1,p2,p3
Database “hangs” for transactions
archive log stop;
— make room in log_archive_dest
archive log start;
log file switch (checkpoint incomplete)
No p1,p2,p3 args
Wait for checkpoint to complete because all log files are full
Solutions
Add more log files
Increase size of log files
log file switch (private strand flush incomplete)
New wait 10g
Like a “log file switch Completion”
log file switch completion
No p1,p2,p3
Wait for lgwr to switch log files when generating redo
Solution:
Increase redo log file size
Reduces frequency of switches
What happens when a log file switch occurs:
Get next log file from control file
Get Redo Copy and Redo Allocation latch
Flush redo
Close File
Update Controlfile
Set new file to Current
Set old file to Active
If in Archivelog mode add file to archive list
Open all members of new logfile group
Write the SCN to the headers
Enable redo log generation
DBWR makes a list of blocks that need to be written out in order to over write the Redo log file a list of blocks that need to be written out in order to over write the Redo log file
switch logfile command
Same as log file switch completion but the command is executed by the dba
Alter system switch logfile;
Redo Log Sizing Concerns and Considerations
What happens to recovery time if I change my redo log file sizes
Larger Redo Log size can increase recovery time but
There are init.ora parameters to limit this
Standby DB: ARCHIVE_LAG_TARGET
Seconds, limits lag between primary and standby
Increases log file switches
FAST_START_MTTR_TARGET
Seconds to Recovery
Easy and accuracy
Is overridden by FAST_START_IO_TARGET
Is overridden by LOG_CHECKPOINT_INTERVAL
alter system set fast_start_mttr_target=17 scope=both;
SQL> select ESTIMATED_MTTR from V$INSTANCE_RECOVERY;
ESTIMATED_MTTR
--------------
21
