top of page
  • Writer's picturekyle Hailey

Oracle Redo Log Waits

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 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:




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


Commit could be lost if machine crash

Or IO error

log buffer space

Wait for space in the redo log buffer in SGA


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


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


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


Seconds, limits lag between primary and standby

Increases log file switches


Seconds to Recovery

Easy and accuracy

Is overridden by FAST_START_IO_TARGET


alter system set fast_start_mttr_target=17 scope=both;





6 views0 comments

Recent Posts

See All

Latch: Cache Buffers Chains

Check out Tanel’s analysis and scripts for cache buffer chains latch contention analysis. Solid stuff:

"kksfbc child completion"

I’ve run into the wait “kksfbc child completion” a few times over the past but found very little written about it. I don’t have an explanation, but I have something that might be as good – a way to re


bottom of page