Redo Waits Kyle Hailey Kyle haileyhotmail com 2

  • Slides: 25
Download presentation
Redo Waits Kyle Hailey Kyle. hailey@hotmail. com

Redo Waits Kyle Hailey Kyle. hailey@hotmail. com

#. 2 Log File Waits Ø Redo is written to disk when Ø User

#. 2 Log File Waits Ø Redo is written to disk when Ø User commits Ø Log Buffer 1/3 full (_log_io_size) Ø Log Buffer fills 1 M Ø Every 3 seconds Ø DBWR asks LGWR to flush redo Ø Sessions Commiting wait for LGWR Copyright 2006 Kyle Hailey

Log File Waits : #. 3 Diag SGA Log Library Buffer Cache Log Buffer

Log File Waits : #. 3 Diag SGA Log Library Buffer Cache Log Buffer DBWR LGWR User 1 User 2 User 3 REDO Log Files Data Files Copyright 2006 Kyle Hailey

#. 4 Typical Log Waits Ø Log Buffer Space Ø Log File Sync Ø

#. 4 Typical Log Waits Ø Log Buffer Space Ø Log File Sync Ø Log File Switch Completion Ø Log File Switch (checkpoint incomplete) Ø Log File Switch (archiving needed) Ø Switch Log File Command Copyright 2006 Kyle Hailey

#. 5 log buffer space Ø #11 Top Wait Ø No p 1, p

#. 5 log buffer space Ø #11 Top Wait Ø No p 1, p 2, p 3 parameters Ø Waiting for space in the redo log buffer in SGA Copyright 2006 Kyle Hailey

#. 6 Log Buffer Waits SGA Log Library Buffer Cache Log Buffer DBWR LGWR

#. 6 Log Buffer Waits SGA Log Library Buffer Cache Log Buffer DBWR LGWR User 1 User 2 User 3 Insert REDO Log Files Update delete Copyright 2006 Kyle Hailey Data Files

#. 7 log file sync Ø #2 Top Wait Ø Wait for redo flush

#. 7 log file sync Ø #2 Top Wait Ø Wait for redo flush upon: Ø Commit Ø Rollback Ø Arguments Ø P 1 = buffer# in log buffer that needs to be flushed Ø P 2, P 3 not used Copyright 2006 Kyle Hailey

#. 8 log file sync Ø Arguments Ø P 1 = buffer# in log

#. 8 log file sync Ø Arguments Ø P 1 = buffer# in log buffer that needs to be flushed Ø P 2 = not used Ø P 3 = not used Copyright 2006 Kyle Hailey

#. 9 Log File Sync SGA Log Library Buffer Cache DBWR LGWR User 1

#. 9 Log File Sync SGA Log Library Buffer Cache DBWR LGWR User 1 User 2 User 3 Commit; REDO Log Files wait for LGWR Copyright 2006 Kyle Hailey Data Files

#. 10 Log file switch completion Ø #12 Ø No p 1, p 2,

#. 10 Log file switch completion Ø #12 Ø No p 1, p 2, p 3 Ø Wait for lgwr to switch log files when generating redo Copyright 2006 Kyle Hailey

#. 11 Log File Switch steps Ø Ø Ø Get next log file from

#. 11 Log File Switch steps Ø Ø Ø 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 Ø Copyright 2006 Kyle Hailey

log file switch completion SGA Log Library Buffer Cache Log Buffer DBWR LGWR User

log file switch completion SGA Log Library Buffer Cache Log Buffer DBWR LGWR User 1 User 2 User 3 Commit; REDO Log Files Wait for LGWR Wait : log file switch completion Copyright 2006 Kyle Hailey Data Files #. 12 Block xxxx Block xxxx

#. 13 log file switch (checkpoint incomplete) Ø #22 Ø No p 1, p

#. 13 log file switch (checkpoint incomplete) Ø #22 Ø No p 1, p 2, p 3 args Ø Wait for checkpoint to complete because all log files are full Copyright 2006 Kyle Hailey

#. 14 log file switch (checkpoint incomplete) : D Checkpoint a SGA Log Library

#. 14 log file switch (checkpoint incomplete) : D Checkpoint a SGA Log Library Buffer Cache Log Buffer Checkpoint b Block xxxx Block Checkpoint xxxx c Block xxxx Block Block xxxx xxxx Block xxxx Block xxxx Block xxxx DBWR LGWR User 1 User 2 User 3 Commit; REDO Log Files Wait for LGWR Copyright 2006 Kyle Hailey Data Files

Log Archiving : Diag SGA Log Library Buffer Cache Log Buffer LGWR ARCH REDO

Log Archiving : Diag SGA Log Library Buffer Cache Log Buffer LGWR ARCH REDO Log Files Archive Dest Copyright 2006 Kyle Hailey #. 15

#. 16 log file switch (archiving needed) Ø #33 Ø No p 1, p

#. 16 log file switch (archiving needed) Ø #33 Ø No p 1, p 2, p 3 Ø Database “hangs” for transactions Copyright 2006 Kyle Hailey

#. 17 log file switch (archiving needed) : Diag SGA Log Library Buffer Cache

#. 17 log file switch (archiving needed) : Diag SGA Log Library Buffer Cache Log Buffer LGWR ARCH REDO Log Files User 2 1 User 3 Commit; Archive Dest Copyright 2006 Kyle Hailey Wait for LGWR

#. 18 switch logfile command Ø #31 Ø Same as log file switch completion

#. 18 switch logfile command Ø #31 Ø Same as log file switch completion but the command is executed by the dba Ø Alter system switch logfile; Copyright 2006 Kyle Hailey

#. 19 Summary Log Waits Ø Log Buffer Space : increase log buffer Ø

#. 19 Summary Log Waits Ø Log Buffer Space : increase log buffer Ø Log File Sync : reduce commits Ø Log File Switch Completion : increase log size Ø Log File Switch (checkpoint incomplete) : add log files Ø Log File Switch (archiving needed) : make room on archive log dest Ø Switch Log File Command Copyright 2006 Kyle Hailey

Recovery and Checkpoints SGA Log Library Buffer Cache DBWR LGWR Data Files Current Position

Recovery and Checkpoints SGA Log Library Buffer Cache DBWR LGWR Data Files Current Position Needed for Recovery Incremental Checkpoint REDO Log Files Copyright 2006 Kyle Hailey #. 20

#. 21 DBWR dirty List and LGWR Buffers Checkpoint a Current Position LGWR Incremental

#. 21 DBWR dirty List and LGWR Buffers Checkpoint a Current Position LGWR Incremental Checkpoint Block xxxx DBWR Checkpoint Blocks can be scattered about LRU list Copyright 2006 Kyle Hailey

#. 22 DBWR dirty List and LGWR MRU - Hot Buffer Headers Dirty List

#. 22 DBWR dirty List and LGWR MRU - Hot Buffer Headers Dirty List Block xxxx LRU - Cold Session Shadow DBWR Dirty Blocks are from the cold end Copyright 2006 Kyle Hailey

#. 23 DBWR dirty List and LGWR Buffer Headers MRU - Hot Checkpoint a

#. 23 DBWR dirty List and LGWR Buffer Headers MRU - Hot Checkpoint a Dirty List Block xxxx Block xxxx LRU - Cold DBWR Write List Block xxxx Copyright 2006 Kyle Hailey Data Files

Recovery – Incremental Checkpoints Ø LOG_CHECKPOINT_INTERVAL = X Lags no more tha X blocks

Recovery – Incremental Checkpoints Ø LOG_CHECKPOINT_INTERVAL = X Lags no more tha X blocks (usually 512) Ø No more than 90% of smallest log file Ø Ø LOG_CHECKPOINT_TIMEOUT = X Ø Lags no more than X seconds Ø DB_BLOCK_MAX_DIRTY_TARGET Ø Limits dirty blocks in the SGA Ø FAST_START_IO_TARGET (8 i) Ø Limits dirty blocks in SGA, depricated Ø FAST_START_MTTR_TARGET (9 i) *** Ø Limits recover time to seconds Copyright 2006 Kyle Hailey #. 24

Recovery – Incremental Checkpoints 9 i. R 2 Ø Don’t set Ø LOG_CHECKPOINT_INTERVAL =0

Recovery – Incremental Checkpoints 9 i. R 2 Ø Don’t set Ø LOG_CHECKPOINT_INTERVAL =0 Ø LOG_CHECKPOINT_TIMEOUT = 0 Ø FAST_START_IO_TARGET = 0 Ø FAST_START_MTTR_TARGET Ø Seconds to Recovery, default 300 on 9 i Ø Is overridden by FAST_START_IO_TARGET Ø Is overridden by LOG_CHECKPOINT_INTERVAL Copyright 2006 Kyle Hailey #. 25

IO Waits Kyle Hailey Kylehaileyhotmail com 2 WaitsIO Waits Kyle Hailey Kylehaileyhotmail com 2 Waits
SQLNet Other Waits SQLNet Waits Host Machine SQLPlusSQLNet Other Waits SQLNet Waits Host Machine SQLPlus
Shared Pool Waits 2 Shared Pool Waits LibraryShared Pool Waits 2 Shared Pool Waits Library
Buffer Cache Waits 2 Buffer Cache Waits HotBuffer Cache Waits 2 Buffer Cache Waits Hot
Enqueue Waits Locks Wait Tree Locks Waits BufferEnqueue Waits Locks Wait Tree Locks Waits Buffer
Tom Waits Thomas Alan Waits is an AmericanTom Waits Thomas Alan Waits is an American
RESEARCH PAPER REDO 3 Options Redo your paperRESEARCH PAPER REDO 3 Options Redo your paper
Yeliah Photography A K A Hailey Cothran HaileyYeliah Photography A K A Hailey Cothran Hailey
ASH Active Session History Kyle Hailey Embarcadero TechnologiesASH Active Session History Kyle Hailey Embarcadero Technologies
ASH Masters ASH SQL Query Repository Kyle HaileyASH Masters ASH SQL Query Repository Kyle Hailey
DB Performance with Graphics Kyle Hailey Performance ArchitectDB Performance with Graphics Kyle Hailey Performance Architect
El francotirador Christopher Kyle Biografa Christopher Kyle naciEl francotirador Christopher Kyle Biografa Christopher Kyle naci
El francotirador Christopher Kyle Biografa Christopher Kyle naciEl francotirador Christopher Kyle Biografa Christopher Kyle naci
1 Kyle Malone CPA CGB Enterprises Kyle Malonecgb1 Kyle Malone CPA CGB Enterprises Kyle Malonecgb
Niagara Analytics Kyle Sardinia Sales Engineer Kyle SardiniatridiumNiagara Analytics Kyle Sardinia Sales Engineer Kyle Sardiniatridium
Selenium Waits 2015 Perfecto Mobile Ltd All RightsSelenium Waits 2015 Perfecto Mobile Ltd All Rights
GOD SEES THE TRUTH BUT WAITS By LEOGOD SEES THE TRUTH BUT WAITS By LEO
Creation groans For the creation waits with eagerCreation groans For the creation waits with eager
Time Model SQL Metrics Waits Stats Sessions PerformanceTime Model SQL Metrics Waits Stats Sessions Performance
2015 PRECONSTRUCTION CONFERENCE ACCESS MANAGEMENT POLICY Mark Waits2015 PRECONSTRUCTION CONFERENCE ACCESS MANAGEMENT POLICY Mark Waits
Demana Waits Foley Kennedy 4 3 Trigonometric FunctionsDemana Waits Foley Kennedy 4 3 Trigonometric Functions
Time Tide Waits For No Man It isTime Tide Waits For No Man It is
Prompt A man who waits to believe inPrompt A man who waits to believe in
Prudent Eye Care Clinical Prioritisation Reconciling Risks WaitsPrudent Eye Care Clinical Prioritisation Reconciling Risks Waits