AWR--Top 5 Timed Foreground Events

基於Wait Interface的調優是目前的主流！每個指標都重要！

 

Waits : 該等待事件發生的次數， 對於DB CPU此項不可用

Times : 該等待事件消耗的總計時間，單位為秒， 對於DB CPU 而言是前臺程式所消耗CPU時間片的總和，但不包括Wait on CPU QUEUE

Avg Wait(ms)  :  該等待事件平均等待的時間， 實際就是  Times/Waits，單位ms， 對於DB CPU此項不可用

 

db file scattered read檔案雜湊讀取等待即多塊讀等待事件是當SESSION等待multi-block I/O時發生的，通常是由於full table scans或index fast full scans引起，Avg wait>20ms就要注意了，db file scattered read 異常大，可能是由於全表掃描引起

該事件通常與全表掃描或者index fast full scan有關。因為全表掃描是被放入記憶體中進行的，通常情況下基於效能的考慮，有時候也可能是分配不到足夠長的連續記憶體空間，所以會將資料塊分散(scattered)讀入Buffer Cache中。該等待過大可能是缺少索引或者沒有合適的索引(可以調整optimizer_index_cost_adj) 。這種情況也可能是正常的，因為執行全表掃描可能比索引掃描效率更高。當系統存在這些等待時，需要透過檢查來確定全表掃描是否必需的來調整。因為全表掃描被置於LRU(Least Recently Used，最近最少適用)列表的冷端(cold end)，對於頻繁訪問的較小的資料表，可以選擇把他們Cache 到記憶體中，以避免反覆讀取。當這個等待事件比較顯著時，可以結合v$session_longops 動態效能檢視來進行診斷，該檢視中記錄了長時間(執行時間超過6 秒的)執行的事物

關於引數OPTIMIZER_INDEX_COST_ADJ＝n：該引數是一個百分比值，預設值為100，可以理解為FULL SCAN COST/INDEX SCAN COST。當n%* INDEX SCAN COST<full scan="" cost時，oracle會選擇使用索引。在具體設定的時候，我們可以根據具體的語句來調整該值。如果我們希望某個statement使用索引，而實際它確走全表掃描，可以對比這兩種情況的執行計劃不同的COST，從而設定一個更合適的值。

關於INDEX FULL SCAN和INDEX FAST FULL SCAN的區別在於，前者在對索引進行掃描的時候會考慮大索引的結構，而且會按照索引的排序， 而後者則不會，INDEX FAST FULL SCAN不會去掃描根塊和分支塊，對索引像訪問堆表一樣訪問，所以這兩個掃描方式用在不同的場合 ,如果存在ORDER BY這樣的排序，INDEX FULL SCAN是合適的，如果不需要排序，那INDEX FAST FULL SCAN效率是更高的。

 

DB file sequential read單塊讀等待意味著發生順序I/O讀等待,如果這個等待嚴重，則應該對大表進行分割槽以減少I/O量，或者最佳化執行計劃（透過使用儲存大綱或執行資料分析）以避免單塊讀操作引起的sequential read等待，通常由於INDEX FULL SCAN/UNIQUE SCAN 、INDEX RANGE SCAN、行遷移或行連結引起，Avg wait>20ms就要注意了。

這裡的sequential指的是將資料塊讀入到相連的記憶體空間中(contiguous memory space)，而不是指所讀取的資料塊是連續的。

?  最為常見的是執行計劃中包含了INDEX FULL SCAN/UNIQUE SCAN,此時出現”db file sequential read”等待是預料之中的

?  當執行計劃包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”)， 服務程式將按照”訪問索引->找到rowid->訪問rowid指定的表資料塊並執行必要的操作”順序訪問index和table，每次物理讀取都會進入”db file sequential read”等待，且每次讀取的都是一個資料塊；這種情況下clustering_factor將發揮其作用（ALL_INDEXES的CLUSTERING_FACTOR*值來判斷資料的離散程度）

?  Extent boundary,假設一個Extent區間中有33個資料塊，而一次”db file scattered read”多塊讀所讀取的塊數為8，那麼在讀取這個區間時經過4次多塊讀取後，還剩下一個資料塊，但是請記住多塊讀scattered read是不能跨越一個區間的(span an extent)，此時就會單塊讀取並出現”db file sequential read”。

?  假設某個區間內有8個資料塊，它們可以是塊a,b,c,d,e,f,g,h，恰好當前系統中除了d塊外的其他資料塊都已經被快取在buffer cache中了，而這時候恰好要訪問這個區間中的資料，那麼此時就會單塊讀取d這個資料塊，並出現”db file sequential read”等待。注意這種情況不僅於表，也可能發生在索引上。 ?         

chained/migrated rows行遷移行連結，chained/migrated rows會造成服務程式在fetch一行記錄時需要額外地單塊讀取，從而出現”db file sequential read”。這種現象需要我們特別去關注，因為大量的鏈式/遷移行將導致如FULL SCAN等操作極度惡化(以往的經驗是一張本來全表掃描只需要30分鐘的表，在出現大量鏈式行後，全表掃描需要數個小時)，同時也會對其他操作造成不那麼 明顯的效能影響。可以透過DBA_TBALES檢視中的CHAIN_CNT來了解表上的鏈式/遷移行情況

?  建立Index entry，顯然當對錶上執行INSERT操作插入資料時，雖然在執行計劃中你看不到過多的細節，但實際上我們需要利用索引來快速驗證表上的某些約束是否 合理，還需要在索引的葉子塊中插入相關的記錄，此時也可能出現”db file sequential read”等待事件

?  針對表上的UPDATE/DELETE，不同於之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”，如果我們使用rowid去更新或刪除資料時，服務程式會先訪問rowid指向的表塊(注意是先訪問table block)上的行資料，之後會根據該行上的具體資料去訪問索引葉子塊(注意Oracle並不知道這些leaf block在哪裡，所以這裡同樣要如range-scan/unique-scan那樣去訪問index branch block)，這些訪問都將會是單塊讀取，並會出現’db file sequential read’，完成必要的讀取後才會執行更新或刪除的實際操作

 

 

log file sync：觸發LGWR把log buffer寫入redo log file的條件有5個（log_buffer中的內容滿1/3 、間隔3秒鐘、log_buffer中的資料到達1M、commit、DBWR 需要寫入的資料的SCN大於LGWR記錄的SCN導致DBWR 觸發LGWR寫入）只有commit稱為同步寫入，其他的都是後臺寫入，log file sync只和同步寫入commit有關（引數_wait_for_sync的解釋wait for sync on commit MUST BE ALWAYS TRUE），log file sync是使用者commit/rollback觸發，在AWR中屬於Foreground Wait Events欄，導致'log file sync' 等待事件的主要原因有：

1. 磁碟IO 慢導致LGWR程式將redo buffer中的資訊寫入到redo log file速度慢。

2. user session commit過於頻繁。

3. 本地或者遠端伺服器CPU資源不足，導致LMS和/或者LGWR不能及時得到CPU排程，不能正常工作。

4. RAC私有網路效能差，導致LMS同步commit SCN慢。

5.user session到DB之間的網路慢（201503宇達案例，DB的網路裡面一臺機器中毒導致不斷髮包致使交換機很慢，雖然客戶端ping DB不慢，但是客戶端session傳送和接送資料比ping的位元組大導致慢。）

6.log buffer太大引起的，log buffer過大，會使lgwr懶惰，因為日誌寫的觸發條件有一個是1/3的log buffer滿。當設定過大的log buffer，也會讓一次性寫入過大的redo record到log file中，寫得過多導致log file sync等待。有一個隱含引數可以控制redo 寫的閥值:_log_io_size。

所以沒有必要將log buffer設定過大，只要沒有出現log buffer space等待事件，log buffer大小就足夠，而且通常預設情況下log buffer是足夠的。

 

通常Log file sync結合Load Profile中的Redo size和Instance Activity Stats中的user commits來一起看比較好

1. user session執行提交（commit）

2. user session會通知LGWR程式將redo buffer中的資訊寫入到redo log file

3. LGWR程式將redo buffer中的資訊寫入redo log file（RAC的話有如下三個小步驟）

   3.1. 同時LGWR 將COMMIT SCN同步/傳播給遠端的資料庫例項的LMS 程式。

   3.2. 遠端資料庫例項的LMS將commit SCN同步到本地SCN，然後通知commit例項的LMS，表示SCN 同步已經完成。

   3.3  當commit 例項的LMS接收到所有遠端資料庫例項的LMS的通知後，commit 例項的LMS再通知本地的LGWR 所有節點SCN同步已經完成以便LGWR進行IO寫入操作。

4. LGWR 在完成了IO寫入操作後通知user session commit 成功

5. user session收到LGWR寫完的通知後，向使用者端傳送提交完成

 

從步1開始，程式開始等待log file sync，到步5結束。

步3、步4，LGWR等待log file parallel write。

 

 

Log File Parallel Write：由lgwr程式引起的，不是user commit/rollback引起的，在AWR中屬於Background Wait Events欄，

log file sync比log file parallel write大很大，有兩個原因，一是CPU緊張，二是同時提交的程式多。

試想一下，如果10個程式同時提交，每個程式等0.1秒，加起來就一共等了1秒。而log file parallel write只會有一個程式等待，就是LGWR。

 

 

log buffer space、log file switch completion

log buffer space是日誌緩衝區寫的速度快於LGWR寫REDO log FILE的速度導致（伺服器程式生成重做記錄的速度快過LGWR寫出重做記錄的速度,因而發生等待）.日誌I/O緩慢是log buffer space等待的主要原因之一.還有一點,如果日誌快取區過小,也容易出現此等待事件.將日誌快取設定的大一些,對於緩解此事件的等待會有幫助.但是,過大的日誌快取,又會降低LGWR重新整理快取的頻率,這可能會使提交時必須重新整理的快取數量增多.從而造成log file sync 等待

log file switch completion是 當一個日誌檔案滿了,oracle要開啟另一個日誌檔案,寫完上一日誌檔案,準備好下一日誌檔案,這之間的等待就是此等待事件了,簡單點說,就是為了完成日誌檔案切換而發生的等待. 若重做日誌檔案過小，則log file switch completion等待會增加

log buffer space等待和log file switch completion等待在許多情況下是同時出現的

 

 

Direct path read：

傳統讀取資料的方式是伺服器程式透過讀取磁碟，然後把資料載入到共享記憶體中，這樣後面的程式就可以透過共享記憶體訪問這些資料，不用再讀取磁碟。direct path read讀取資料塊方式，是指伺服器程式直接讀取資料檔案載入到伺服器程式的PGA內中當中，不進入buffer cache中。

Oracle透過隱含引數 _small_table_threshold來界定大表小表的臨界，Oracle認為對於大表執行直接路徑讀取的意義比較大，對於小表透過將其快取可能受益更大。_small_table_threshold的單位為block。預設為db cache size的2%，只要表滿足大於等於_small_table_threshold定義的大小就會採取直接路徑讀取

等待事件direct path read每秒讀取多少M可以參考IOStat by Function summary或IOStat by Function/Filetype summary中的Function Name 為Direct Reads項

等待事件direct path read要考慮是普通堆表、lob、temporary tablespace中的哪個引起，就需要檢視Instance Activity Stats中physical reads direct、physical reads direct (lob)、physical reads direct temporary tablespace誰的每秒讀取最大。Segments by Direct Physical Reads中可以看到是哪個表被Direct Reads了。Instance Activity Stats中table scans (long tables)的per Second不是每秒讀取塊數，而是每秒讀取次數（和其他指標不一樣）。table scans (long tables)表示大表掃描次數，table scans (direct read)表示大表direct read掃描次數，一般table scans (direct read)<= table scans (long tables),如果前者1000，後者1000零幾說明基本上大表掃描都是direct read

如果Segments by Direct Physical Reads中可只有一個表被Direct Reads了，結合Instance Activity Stats中physical reads direct、table scans (long tables)的per second值可以算出表的實際高水位多少M了，計算方法為(physical reads direct*8/1024)/ table scans (long tables)

兩種方法來啟用、禁用Direct path read特性。event 10949或者設定隱含引數_serial_direct_read(預設是false)

SQL> ALTER session SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

SQL> ALTER session SET EVENTS '10949 TRACE NAME CONTEXT off';

SQL> alter session set "_serial_direct_read"=true;

SQL> alter session set "_serial_direct_read"=false;

關閉Direct path read可以降低物理讀，但是會提升邏輯讀引起CPU升高

 

 

Buffer Busy Waits：

該事件是在一個SESSION需要訪問BUFFER CACHE中的一個資料庫塊而又不能訪問時發生的（指的是多個session對於同一個block的寫入競爭）

buffer busy waits就發生在pin資料塊的過程裡，讀讀，讀寫都不會有衝突。可是寫寫的話，就會出現buffer busy waits。buffer busy waits事件表明修改的太頻繁了，而不是讀的太頻繁了

update導致buffer busy waits原因：undo不足或熱點塊造成

insert導致buffer busy waits原因：freelist 爭用造成，可以將表空間更改為ASSM 管理或者加大freelist 。

解決辦法：

1、 發現hot block，改變pctused 和pctfree，使得一個block 中可以容納更多得資料

2、 增加freelist

3、 增加一定數量的回滾段

 

當程式需要存取SGA中的buffer的時候，它會依次執行如下步驟的操作：
1.獲得cache buffers chains latch，遍歷那條buffer chain直到找到需要的buffer header
2.根據需要進行的操作型別(讀或寫)，它需要在buffer header上獲得一個共享或獨佔模式的buffer pin或者buffer lock
3.若程式獲得buffer header pin，它會釋放獲得的cache buffers chains latch，然後執行對buffer block的操作
4.若程式無法獲得buffer header pin，它就會在buffer busy waits事件上等待
程式之所以無法獲得buffer header pin，是因為為了保證資料的一致性，同一時刻一個block只能被一個程式pin住進行存取，因此當一個程式需要存取buffer cache中一個被其他程式使用的block的時候，這個程式就會產生對該block的buffer busy waits事件。

 

 

read by other session：

10G以前，等待事件read by other session劃入到了buffer busy waits裡,read by other session的意思是，多個session併發請求相同的資料塊，但因該資料塊不在buffer_cache中而必須從磁碟讀取，處理這種情況，oracle會只讓其中一個sesion進行磁碟讀取，此時其它session等待塊從磁碟上讀取進buffer_cache而丟擲read by other session等待事件。10G以後，read by other session被從buffer busy waits單獨了出來，變的單純。

你讀的塊，別人也正在從磁碟讀。這是Read By Other Session，不是Buffer BUsy Waits事件了。導致原因可能是大量的邏輯/物理讀  ;或者過小的buffer cache引起

 

 

latch free：

其實是未獲得latch，而進入latch sleep，如果latch資源被爭用，通常都會表現為CPU資源使用過高。latch只作用於記憶體中、只能被當前例項訪問、是瞬間的佔用和釋放、非入隊的、不存在死鎖。

產生原因：SQL語句沒有繫結變數、熱塊較多

Latch的種類

願意等待(Willing-To-Wait)

大部分的latch都屬於這種型別(Willing-To-Wait)。

任何時候，只有一個程式可以訪問記憶體中的某一個資料塊，如果程式因為別的程式正佔用塊而無法獲得Latch時，他會對CPU進行一次spin(旋轉)，時間非常的短暫，spin過後繼續獲取，不成功仍然spin，直到spin次數到達閥值限制(這個由隱含引數_spin_count指定)，此時程式會停止spin,進行短期的休眠，休眠過後會繼續剛才的動作，直到獲取塊上的Latch為止。

長期等待鎖存器(Latch Wait Posting)

此時等待程式請求Latch不成功，進入休眠，他會向鎖存器等待連結串列(Latch Wait List)壓入一條訊號，表示獲取Latch的請求，當佔用程式釋放Latch時會檢查Latch Wait List，向請求的程式傳遞一個訊號，啟用休眠的程式。Latch Wait List是在SGA區維護的一個程式列表，他也需要Latch來保證其正常執行，預設情況下share pool latch和library cache latch是採用這個機制。

不等待（No-Wait）

這種型別的latch比較少，對於這種型別的latch來說，都會有很多個可用的latch。當一個程式請求其中的一個latch時，會以no-wait模式開始請求。如果所請求的latch不可用，則程式不會等待，而是立刻請求另外一個latch。只有當所有的latch都不能獲得時，才會進入等待。

 

 

free buffer waits:

顧名思義就是db buffer裡沒有空閒可用buffer，使得當前會話當前程式等待記憶體db buffer中的可用空間。如果SQL充分繫結變數、I/0不是寫太慢、Dbwr程式足夠，那這種等待可能說明Buffer Cache 設定的偏小

 

 

 

library cache pin：

“LIBRARY CACHE PIN”通常是發生在編譯或重新編譯PL/SQL,VIEW,TYPES等object時

當我們對object進行維護,如”ALTER”,”GRANT”,”REVOKE”時,就會使object變得無效, 透過object的”LAST_DDL”屬性可檢視到這些變化.當object變得無效時,Oracle 會在第一次訪問此object時試圖去重新編譯它,如果此時其他session已經把此object pin到library cache中,就會出現問題,特別時當有大量的活動session並且存在較複雜的dependence時.在某種情況下,重新編譯object可能會花幾個小時時間,從而阻塞其它試圖去訪問此object的程式。

 

 

library cache lock：

當要對一個過程或者函式進行編譯時，需要在library cache中pin該物件。在pin該物件以前，需要獲得該物件handle的鎖定，如果獲取失敗，就會產生library cache lock等待。如果成功獲取handle的lock，則繼續在library cache中pin該物件，如果pin物件失敗，則會產生library cache pin等待。如果是儲存過程或者函式，可以這樣認為：如果存在librarycache lock等待，則一定存在library cache pin等待；反過來，如果存在library cache pin等待，不一定會存在library cache lock等待；但如果是表引起的，則一般只有library cache lock等待，則不一定存在library cache pin。

可能發生library cache pin和library cache lock的情況：
1、在儲存過程或者函式正在執行時被編譯。
2、在儲存過程或者函式正在執行時被對它們進行授權、或者移除許可權等操作。
3、對某個表執行DDL期間，有另外的會話對該表執行DML或者DDL。
4、PL/SQL物件之間存在複雜的依賴性

 

gc buffer busy

gc就是global cache，11g的gc buffer busy，在10g中就叫global cache buffer busy。gc buffer busy產生的原因和單例項的 buffer busy waits 類似就是一個時間點節點a的例項向節點b請求block的等待。11g開始gc buffer busy分為gc buffer busy acquire和gc buffer busy release。

產生原因：熱塊，低效sql（越多的資料塊請求到buffer cache 中，那麼越可能造成別的會話等待。）資料交叉訪問（RAC資料庫，同一資料在不同資料庫例項上被請求訪問）

所以RAC建議不同的應用功能在不同的資料庫例項上被訪問

 

gc buffer busy acquire：

gc buffer busy acquire是當session#1嘗試請求訪問遠端例項(remote  instance) buffer，但是在session#1之前已經有相同例項上另外一個session#2請求訪問了相同的buffer，並且沒有完成，那麼session#1等待gc buffer busy acquire。

 

 

gc buffer busy release：

gc buffer busy release是在session#1之前已經有遠端例項的session#2請求訪問了相同的buffer，並且沒有完成，那麼session#1等待gc buffer busy release。

 

 

gcs log flush sync

GCS日誌重新整理同步

flush 是Oracle為了保證Instance Recovery例項恢復機制，而要求每一個current block在本地節點local instance被修改後(modify/update) 必須要將該current block相關的redo 寫入到logfile 後（要求LGWR必須完成寫入後才能返回)，才能由LMS程式傳輸給其他節點使用。

The cause of this wait event 'gcs log flush sync' is mainly - Redo log IO performance.

 

enq:TX-row lock contention：

行鎖，當一個事務開始時，如執行INSERT/DELETE/UPDATE/MERGE等操作或者使用SELECT ... FOR UPDATE語句進行查詢時，會首先獲取鎖，直到該事務結束。當然在AWR的Segments by Row Lock Waits一欄中也可以看到鎖住的物件

產生原因

第一種情況：當兩個會話對同一行進行更新時，ORACLE為了保證資料庫的一致性，加了一個TX鎖，這時另一個或多個會話必須等待第一個會話commit或rollback，否則會一等待下去，這是最為常見的一種模式！

    第二種情況：兩個或多個會話向具有唯一主健索引的表中插入或更新相同的資料行，既然是唯一主健索引，那麼先獲得插入的session以TX排它鎖模式進行新增，此時其它session只能等待，也是常見的一種情況

第三種情況：兩個或多個會話插入或更新具有點陣圖索引的列，跟據點陣圖索引的特性，一個索引鍵值對應多個資料行的rowid值，此種情況下也是以TX模式訪問，一般在OLTP系統中很少有點陣圖索引，但不排除個別系統，本例中不存在點陣圖索引。

第四種情況：IO慢、log file sync慢、commit慢、cluster叢集慢等多重原因也會引起

獲取該等待事務對應行鎖的指令碼

select sid, sql_id

  from v$session s, v$sql q

 where sid in

       (select sid

          from v$session

         where state in ('WAITING')

           and wait_class != 'Idle'

           and event = 'enq: TX - row lock contention'

           and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

 

 

enq: TX- index contention：

當在基於索引的表中插入一條記錄時，此時正好另一會話事務正在操作這個索引塊的分裂，則這個會話進入enq:TX-index contention等待，sequence使用nocache時且利用sequence插入列且該列索引列時很容易出現