[20201204]关于等待事件Log File Sync.txt

[20201204]关于等待事件Log File Sync.txt --//昨天被别人问及Log File Sync等待事件，实际上我以前的理解存在很大的误区，我一直认为出现这个主要是写日志太慢，磁盘处理 --//IO能力太差造成的，再还有就是频繁提交。实际上还忽略一种情况，就是CPU太忙，这样的情况我很少遇到。 --//找到一个链接： https://logicalread.com/oracle-log-file-sync-wait-event-dr01/ The Oracle "log file sync" wait event is triggered when a user session issues a commit (or a rollback). The user session will signal or post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session. The wait is entirely dependent on LGWR to write out the necessary redo blocks and send confirmation of its completion back to the user session. The wait time includes the writing of the log buffer and the post, and is sometimes called "commit latency". The "log file sync" wait event may be broken down into the following components: 1. Wakeup LGWR if idle 2. LGWR gathers the redo to be written and issues the I/O 3. Wait 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 Tune the system based on the "log file sync" component with the most wait time. Steps 2 and 3 are accumulated in the "redo write time" statistic. (i.e. as found under STATISICS section of Statspack) Step 3 is the "log file parallel write" wait event. (See Metalink Note 34583.1:"log file parallel write") 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 some time for the OS to schedule it to run. 根据"日志文件同步"组件调整系统，等待时间最多。 步骤2和步骤3是在"重做写入时间"统计中累积的。 (即。 如Statspack的STATISICS 部分所示)步骤3是"日志文件并行写入"等待事件。 (参见Metalink注释34583.1："日志文件并行写入")步骤5和步骤6可能随着系统负载的 增加而变得非常重要。 这是因为即使在前台发布之后，操作系统也可能需要一些时间来安排它的运行。 --//看看我们几个生产系统的情况： > @ tpt/ash/ash_wait_chains event2 "event='log file sync'" trunc(sysdate-1) trunc(sysdate) -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS     AAS WAIT_CHAIN ------ ---------- ------- --------------------------------------------   98%        1043      .0 -> log file sync  -> log file parallel write    1%          15      .0 -> log file sync    0%           5      .0 -> log file sync  -> ON CPU --//很明显这个问题集中在log file parallel write，也就是写入磁盘太慢。 --//这套系统磁盘设置有问题，设置在写通模式。导致这样的情况出现。参考链接 --// http://blog.itpub.net/267265/viewspace-2684072/ > @ tpt/ash/ash_wait_chains event2 "event='log file sync'" trunc(sysdate-1) trunc(sysdate) -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS        AAS WAIT_CHAIN ------ ---------- ---------- --------------------------------------------   87%         104          0 -> log file sync    7%           8          0 -> log file sync  -> log file parallel write    7%           8          0 -> log file sync  -> ON CPU --//这个磁盘情况很少，但是业务高峰时密集提交很多。 --//这套系统平时业务很少，但是业务出现非常集中。 --//再看看生产系统exadata的情况,不再解析： > @ tpt/ash/ash_wait_chains event2 "event='log file sync'" trunc(sysdate-1) trunc(sysdate) -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS        AAS WAIT_CHAIN ------ ---------- ---------- --------------------------------------------   75%         354          0 -> log file sync   21%         100          0 -> log file sync  -> log file parallel write    3%          16          0 -> log file sync  -> ON CPU