Oracle:Redo Log Waits
Redo
- 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:
- 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
- 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));
- END LOOP;
- COMMIT;
- Send all the info over the wire to the database, one communication, bulk insert
- -- FORALL
- FORALL i IN 1..i
- INSERT INTO foo VALUES (id(i), data(i));
- COMMIT;
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
- ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
- Commit could be lost if machine crash
- Or IO error
- Further reading: http://forums.oracle.com/forums/thread.jspa?threadID=979421&tstart=0
- Manly-Men Only Use Solid State Disk for Redo Logging - Kevin Closson
- [http://orainternals.wordpress.com/2008/07/07/tuning-log-file-sync-wait-events/ Tuning Log File Sync Wait Events - Riyaj Shamsudeen}
- Hotsos 2010 - Day 4 - Doug Burns (see the comments section)
- http://kerryosborne.oracle-guy.com/2010/03/increasing-priority-of-lgwr-process-using-_high_priority_processes/
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
File:Redo incremental checkpoint 2.PNG
