top of page
  • Writer's picturekyle Hailey

Redo log waits in Oracle



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 Committing 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 and/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)

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






2 views0 comments
bottom of page