Oracle:Redo Log Waits

From DB Optimizer
Jump to: navigation, search


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

Redo log file sync.PNG

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

Redo separate archive.PNG

Further reading:
Manly-Men Only Use Solid State Disk for Redo Logging - Kevin Closson
[ Tuning Log File Sync Wait Events - Riyaj Shamsudeen}
Hotsos 2010 - Day 4 - Doug Burns (see the comments section)

log buffer space

Redo log buffer.PNG

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)

Redo archiving neededa.PNG

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)

Redo switch completion incomplete.PNG

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

Redo switch completion.PNG

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

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;

Redo incremental checkpoint.PNG

File:Redo incremental checkpoint 2.PNG

File:Redo incremental checkpoint 3.PNG

File:Redo incremental checkpoint 5.PNG