[20201204]關於等待事件Log File Sync.txt

[20201204]關於等待事件Log File Sync.txt

--//昨天被別人問及Log File Sync等待事件，實際上我以前的理解存在很大的誤區，我一直認為出現這個主要是寫日誌太慢，磁碟處理
--//IO能力太差造成的，再還有就是頻繁提交。實際上還忽略一種情況，就是CPU太忙，這樣的情況我很少遇到。

--//找到一個連結：



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