Wait Event Docs‎ > ‎

Oracle: Redo Log Waits


log buffer space
log file switch (archiving needed)
log file switch (checkpoint incomplete) 
log file switch (private strand flush incomplete)
log file switch completion

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
Batching vs Single row operations
          Note: in PL/SQL the commits aren't respected in the usual since. see :http://www.oracledba.co.uk/tips/lgwr_dilemma.htm

Single row operations, lots of communication , slowest
-- slowest : single row inserts with commit
insert into foo values ...;
insert into foo values ...;
insert into foo values ...;
insert into foo values ...;
          Send all the info over the wire to the database, one communication
-- For Loop
FOR i IN 1..i LOOP  
     INSERT INTO foo VALUES (id(i), data(i));

          Send all the info over the wire to the database, one communication, bulk insert
FORALL i IN 1..i  
     INSERT INTO foo VALUES (id(i), data(i));

Improve IO Speed

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;



Other stuff
   alter system set "_high_priority_processes"="LMS*|LGWR*" scope=spfile sid='*';

#1 - Kevin Closson said:
2010-03-12 18:52 - (Reply)
LGWR, LGWR, when will they ever learn. I don't care who's talking about elevating LGWR priority. It makes no difference on a busy system. Here's a little problem for you all to consider. When a foreground process is waiting for LGWR to post it (signaling the session's redo has been written), what state and mode is it in? It's sleeping in kernel mode. When a kernel mode process becomes runable what gets CPU first? A runable user mode process (with elevated priority) or a runable kernel mode process? Yep, you guessed right.

So, if you have, say, 8 cores and LGWR is servicing a group commit for 11 sessions, uh, what do you think happens when he posts all of them and what do you think an elevated user mode priority for LGWR has to do with any of that?  

Must be time for me to blog all that again.