Friday, January 29, 2010

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





3 comments:

  1. Kyle,
    thank you for this great set of posts.
    In this particular, you comment to commit less in PL/SQL to reduce "Log file Sync". But from 10g onwards (or before?) Oracle seems to do internally a 'commit nowait' in pl/sql until the end of the procedure.
    http://www.oracledba.co.uk/tips/lgwr_dilemma.htm
    best regards,
    Martin

    ReplyDelete
  2. yes, AFAIK the PL/SQL idiosyncrasy has always been there.
    Yes, the commit less often only applies to non-PL/SQL code. I'll correct the text - thanks.
    As mentioned, one can take "advantage" of this pl/sql peculiarity out side if pl/sql with
    ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;

    ReplyDelete
  3. "ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;"

    On many busy systems ... this is like riding a tiger.
    Damir Vadas
    http://damir-vadas.blogspot.com

    ReplyDelete