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

http://sites.google.com/site/embtdbo

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