WAITEVENT: "log file sync" Reference Note (文档 ID 34592.1)

最新推荐文章于 2021-09-25 12:00:25 发布

mybluetiankong

最新推荐文章于 2021-09-25 12:00:25 发布

阅读量1.5k

点赞数

分类专栏：网络资源_ORACLE_调优

网络资源_ORACLE_调优专栏收录该内容

109 篇文章 0 订阅

订阅专栏

 
 "log file sync" Reference Note This is a reference note for the wait event  
 "log file sync" which includes the following subsections: 
 Brief definition
Individual wait details (eg: For waits seen in <>)
Systemwide wait details (eg: For waits seen in <>)
Reducing waits / wait times
Data Guard Perspective
Known bugs
 See  
 Note:61998.1 for an introduction to Wait Events. 
 
  Definition: 
 Versions:7.0 - 11.1 Documentation: 11g 10g
When a user session(foreground process) COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.
 
 This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally). 
 
 See Reducing Waits section below for more detailed breakdown of this wait event. 
 
 ("log file sync" also applies to ROLLBACK/UNDO in that once the rollback/undo is complete the end of the rollback/undo operation requires all changes to complete the rollback/undo to be flushed to the redo log)
 
  Individual Waits: 
    Parameters: 
  
 P1 = buffer#
P2 = Not used
P3 = Not used
 
 buffer# 
   
     All changes up to this buffer number (in the log buffer) must be flushed to disk and the writes confirmed to ensure that the transaction is committed , and will remain committed upon an instance crash. Hence the wait is for LGWR to flush up to this  
    buffer#. 
    
    Wait Time: 
 
   The wait is entirely dependent on LGWR to write out the necessary redo blocks and confirm completion back to the user session. The wait time includes the writing of the log buffer and the post. The waiter times out and increments the sequence number every second while waiting. 
  
    Finding Blockers: 
  
  If a session continues to wait on the the same  
  buffer# then the  
  SEQ# column of <> should increment every second. If not then the local session has a problem with wait event timeouts. If the  
  SEQ# column is incrementing then the blocking process is the LGWR process. Check to see what LGWR is waiting on as it may be stuck. 
  
  Systemwide Waits: 
 
   Systemwide figures for waits on "log file sync" show the time spent waiting for COMMITs to complete. If this is significant then there may be a problem with LGWR's ability to flush redo out quickly enough. One can also look at: 
  "log file parallel write" waits for LGWR (See Note:34583.1)
"user commits" statistic shows the number of commits.
 
  Reducing Waits / Wait times: 
 
   Here are 3 main general tuning tips to help you reduce waits on "log file sync": 
  Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle reducing the overall number of commits by batching transactions can have a very beneficial effect.
See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.
Check to see if redologs are large enough. Enlarge the redologs so the logs switch between 15 to 20 minutes.
 
 For more detailed analysis for reducing waits on LOG FILE SYNC please see below:  
  
 The overall wait time for LOG FILE SYNC may be broken down into subsections or components. 
  
 If your system still shows high "log file sync" wait times after ensuring the general tuning tips above are completed, you should break down the total wait time into the individual components, then tune those components that make up the largest time.  
  
 The log file sync wait may be broken down into the following components: 
  
 1. Wakeup LGWR if idle  
  
 2. LGWR gathers the redo to be written and issue the I/O 
  
 3. Time for the log write I/O to complete 
  
 4. LGWR I/O post processing 
  
 5. LGWR posting the foreground/user session that the write has completed 
  
 6. Foreground/user session wakeup 
  
 Tuning advice based on log file sync component breakdown above: 
  
 Steps 2 and 3 are accumulated in the "redo write time" statistic.  
  (i.e. as found under STATISICS section of Statspack and AWR) 
  
 Step 3 is the "log file parallel write" wait event.  
  (Note.34583.1:"log file parallel write" Reference Note:) 
  
 Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take a some time for the OS to schedule it to run. May require monitoring from O/S level. 
  
  Data Guard Perspective: 
 
   For Data Guard with synchronous (SYNC) transport and commit WAIT defaults, the above tuning steps still apply, except step 3 also includes the time for the network write and the RFS/redo write to the standby redo logs. 
  
 This wait event and how it applies to Data Guard is explained in detail in the MAA OTN white paper: 
  
  Note 387174.1:MAA - Data Guard Redo Transport and Network Best Practices. 
  
 Known Bugs 
 
   You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:  
  
  NBBugFixedDescription
 1482337211.2.0.4, 12.1.0.1Adaptive "log file sync" picks inaccurate polling interval on RAC
 1370790411.2.0.4, 12.1.0.1LGWR sometimes uses polling, sometimes post/wait
 1261408511.2.0.4, 12.1.0.1Diagnostic enhancement to add new statistics for investigating "log file sync" and "log file parallel write" relationship
 1355140211.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4, 12.1.0.1High "log file parallel write" and "log file sync" after upgrading 11.2 with Veritas/Symantec ODM
 1237814711.2.0.2.7, 11.2.0.2.BP10, 11.2.0.3, 12.1.0.1Long broadcast ack warning messages, and/or many Log File Sync timeouts in foregrounds in RAC
 909569611.2.0.3.7, 11.2.0.3.BP08, 11.2.0.4, 12.1.0.1"log file sync" wait time spikes with ARCHIVE_LAG_TARGET set
 1307470611.2.0.3.BP14, 11.2.0.4, 12.1.0.1Long "log file sync" waits in RAC not correlated with slow writes
 849087910.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1Long "log file sync" latencies due to broadcast on commit scheme
 822073410.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1Long "log file sync" wait in RAC
 771635610.2.0.5, 11.2.0.1Long "log file sync" latencies with broadcast on commit scheme in RAC
 764363210.2.0.4.1, 10.2.0.5, 11.1.0.7.4, 11.2.0.1High log file sync in Data Guard maximum availability (sync) mode
 761036210.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1Long "log file sync" waits in RAC with broadcast on commit in RAC
P756873410.2.0.5, 11.2.0.1AIX: Sporadic spikes of 'log file sync' on AIX with heavy commit concurrency
 745237310.2.0.5, 11.1.0.7.1, 11.2.0.1"log file sync" timeout is not configurable
 631968510.2.0.4, 11.1.0.7, 11.2.0.1LGWR posts do not scale on some platforms
 619394510.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1High LGWR CPU use and long 'log file sync' latency in RAC
 977643111.1.0.7.411.1.0.7.3 fix for 8220734 is incomplete - "log file sync" timeout set to 1 second
 589696310.2.0.4, 11.1.0.6High LGWR CPU and longer "log file sync" with fix for bug 5065930
 514738610.2.0.4.1, 10.2.0.5, 11.1.0.6Long waits on "log file sync" /random ORA-27152 "attempt to post process failed"
 508759210.2.0.4, 11.1.0.6"log file sync" waits from read only commits
 506593010.2.0.3, 11.1.0.6"log file sync" timeouts can occur
 506106810.2.0.3, 11.1.0.6RAC using "broadcast on commit" can see delayed commit times
 33112109.2.0.5, 10.1.0.2Unnecessary 0.5 seconds waits for "Broadcast on commit" SCN scheme
 26631229.2.0.5, 10.1.0.2Unneccessarily long waits on "log file sync" can occur
 26406869.2.0.5, 10.1.0.2Long waits for "log file sync" with broadcast SCN in RAC
 
  '*' indicates that an alert exists for that issue.
'+' indicates a particularly notable issue / bug.
'I' indicates an install issue / bug included for completeness.
'P' indicates a port specific bug.
"OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].
 
  Related: 
  
  REFERENCES 
 NOTE:7452373.8 - Bug 7452373 - "log file sync" timeout is not configurable 
 
 NOTE:7568734.8 - Bug 7568734 - AIX: Sporadic spikes of 'log file sync' on AIX with heavy commit concurrency 
 
 NOTE:7610362.8 - Bug 7610362 - Long "log file sync" waits in RAC with broadcast on commit in RAC 
 
 NOTE:7643632.8 - Bug 7643632 - High log file sync in Data Guard maximum availability (sync) mode 
 
 NOTE:7716356.8 - Bug 7716356 - Long "log file sync" latencies with broadcast on commit scheme in RAC 
 
 NOTE:8220734.8 - Bug 8220734 - Long "log file sync" wait in RAC 
 
 NOTE:8490879.8 - Bug 8490879 - Long "log file sync" latencies due to broadcast on commit scheme 
 
 NOTE:9095696.8 - Bug 9095696 - "log file sync" wait time spikes with ARCHIVE_LAG_TARGET set 
 
 NOTE:9776431.8 - Bug 9776431 - 11.1.0.7.3 fix for 8220734 is incomplete - "log file sync" timeout set to 1 second 
 
 NOTE:2640686.8 - Bug 2640686 - Long waits for "log file sync" with broadcast SCN in RAC 
 
 NOTE:2663122.8 - Bug 2663122 - Unneccessarily long waits on "log file sync" can occur 
 
 NOTE:12378147.8 - Bug 12378147 - Long broadcast ack warning messages, and/or many Log File Sync timeouts in foregrounds in RAC 
 
 NOTE:13074706.8 - Bug 13074706 - Long "log file sync" waits in RAC not correlated with slow writes 
 
 NOTE:5087592.8 - Bug 5087592 - "log file sync" waits from read only commits 
 
 NOTE:5896963.8 - Bug 5896963 - High LGWR CPU and longer "log file sync" with fix for bug 5065930 
 
 NOTE:5147386.8 - Bug 5147386 - Long waits on "log file sync" /random ORA-27152 "attempt to post process failed" 
 
 NOTE:6193945.8 - Bug 6193945 - High LGWR CPU use and long 'log file sync' latency in RAC 
 
 NOTE:61998.1 - Introduction to Tuning Oracle7 / Oracle8 / 8i / 9i 
 
 NOTE:6319685.8 - Bug 6319685 - LGWR posts do not scale on some platforms 
 
 NOTE:3311210.8 - Bug 3311210 - Unnecessary 0.5 seconds waits for "Broadcast on commit" SCN scheme 
 
 NOTE:34583.1 - WAITEVENT: "log file parallel write" Reference Note 
 
 NOTE:387174.1 - MAA - Data Guard Redo Transport and Network Best Practices 
 
 NOTE:5061068.8 - Bug 5061068 - RAC using "broadcast on commit" can see delayed commit times 
 
 NOTE:5065930.8 - Bug 5065930 - "log file sync" timeouts can occur

   
            相关内容 
          
              产品 
            
       Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS
 
              关键字 
            
     BUGTAG_ADDBUGS; 
      
     LGWR; 
      
     LOG FILE; 
      
     LOG FILE SYNC; 
      
     LOGFILE; 
      
     REDO LOG; 
      
     SYNC; 
      
     WAITEVENT 
      
              错误 
            
     ORA-27152; 
      
     ORA-600 [NNNNN]

mybluetiankong

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
WAITEVENT: "log file sync" Reference Note (文档 ID 34592.1)

"log file sync" Reference NoteThis is a reference note for the wait event "log file sync" which includes the following subsections:Brief definitionIndividual wait details (eg: For waits seen in
复制链接

扫一扫

专栏目录

NB	Bug	Fixed	Description
	14823372	11.2.0.4, 12.1.0.1	Adaptive "log file sync" picks inaccurate polling interval on RAC
	13707904	11.2.0.4, 12.1.0.1	LGWR sometimes uses polling, sometimes post/wait
	12614085	11.2.0.4, 12.1.0.1	Diagnostic enhancement to add new statistics for investigating "log file sync" and "log file parallel write" relationship
	13551402	11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4, 12.1.0.1	High "log file parallel write" and "log file sync" after upgrading 11.2 with Veritas/Symantec ODM
	12378147	11.2.0.2.7, 11.2.0.2.BP10, 11.2.0.3, 12.1.0.1	Long broadcast ack warning messages, and/or many Log File Sync timeouts in foregrounds in RAC
	9095696	11.2.0.3.7, 11.2.0.3.BP08, 11.2.0.4, 12.1.0.1	"log file sync" wait time spikes with ARCHIVE_LAG_TARGET set
	13074706	11.2.0.3.BP14, 11.2.0.4, 12.1.0.1	Long "log file sync" waits in RAC not correlated with slow writes
	8490879	10.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1	Long "log file sync" latencies due to broadcast on commit scheme
	8220734	10.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1	Long "log file sync" wait in RAC
	7716356	10.2.0.5, 11.2.0.1	Long "log file sync" latencies with broadcast on commit scheme in RAC
	7643632	10.2.0.4.1, 10.2.0.5, 11.1.0.7.4, 11.2.0.1	High log file sync in Data Guard maximum availability (sync) mode
	7610362	10.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1	Long "log file sync" waits in RAC with broadcast on commit in RAC
P	7568734	10.2.0.5, 11.2.0.1	AIX: Sporadic spikes of 'log file sync' on AIX with heavy commit concurrency
	7452373	10.2.0.5, 11.1.0.7.1, 11.2.0.1	"log file sync" timeout is not configurable
	6319685	10.2.0.4, 11.1.0.7, 11.2.0.1	LGWR posts do not scale on some platforms
	6193945	10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1	High LGWR CPU use and long 'log file sync' latency in RAC
	9776431	11.1.0.7.4	11.1.0.7.3 fix for 8220734 is incomplete - "log file sync" timeout set to 1 second
	5896963	10.2.0.4, 11.1.0.6	High LGWR CPU and longer "log file sync" with fix for bug 5065930
	5147386	10.2.0.4.1, 10.2.0.5, 11.1.0.6	Long waits on "log file sync" /random ORA-27152 "attempt to post process failed"
	5087592	10.2.0.4, 11.1.0.6	"log file sync" waits from read only commits
	5065930	10.2.0.3, 11.1.0.6	"log file sync" timeouts can occur
	5061068	10.2.0.3, 11.1.0.6	RAC using "broadcast on commit" can see delayed commit times
	3311210	9.2.0.5, 10.1.0.2	Unnecessary 0.5 seconds waits for "Broadcast on commit" SCN scheme
	2663122	9.2.0.5, 10.1.0.2	Unneccessarily long waits on "log file sync" can occur
	2640686	9.2.0.5, 10.1.0.2	Long waits for "log file sync" with broadcast SCN in RAC