對wait events 和statistics 的理解小記

buffer busy waits

說明buffer cache中有一些buffers被多個進程嘗試同時訪問。
查看V$WAITSTAT觀察各種類型buffer wait的統計資訊。
SELECT class, count FROM V$WAITSTAT WHERE count > 0 ORDER BY count DESC;

也可以查看V$SESSION_WAIT觀察當前buffer wait資訊,其中P1-FILE_ID, P2- BLOCK_ID,

再通過DBA_EXTENTS尋找哪些SEGMENT被爭用。
Select * from v$session_wait where event=’buffer busy waits’
SELECT segment_owner, segment_name
  FROM DBA_EXTENTS
 WHERE file_id = <&p1>
   AND <&p2> BETWEEN block_id AND block_id + blocks - 1;

對於segment header爭用：
1、很可能是freelist的爭用，LMT中的ASSM可以解決問題。
2、如果不能使用ASSM,可以增加FREELIST數量，還不行就使用FREELIST GROUP
查看segment 的freelist 情況：
SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;

對於data block爭用：
1、最佳化sql，避免使用選擇性差的index
2、使用LMT中的ASSM，或增加FREELIST避免多個進程同時插入資料到相同的塊。

對於undo header爭用：
使用automatic undo 管理，或者增加rollback segments

對於undo block爭用：
使用automatic undo 管理，或者增大rollback segments size

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

free buffer waits

發現找不到free buffer，而通知dbwr 將髒資料寫入磁碟，以獲得free buffer。
形成等待dbwr完成的因素有：
1、 IO慢，非同步磁碟，使用raw device
2、 等待某些資源，比如latches
3、 Buffer cache 太小，使得dbwr花費很長的時間用來寫髒資料
4、 Buffer cache 太大，使得dbwr沒有能力寫出足夠的髒資料來滿足需求。

檢查哪裡阻塞了DBWR
1、 檢查V$FILESTAT看哪裡發生了最多的write操作
2、 檢查作業系統的IO情況
3、 檢查CACHE是否太小，查看buffer cache hit ratio是否很低，使用V$DB_CACHE_ADVICE 判斷是否應該增大CACHE
4、 如果CACHE足夠大，IO也沒有問題，則考慮使用非同步IO，或者多個DBWR進程，以滿足負載
調整DB_WRITER_PROCESSES參數（from DBW0 to DBW9 and from DBWa to DBWj），適用於多個CPU(at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups)
5、 DBWR_IO_SLAVES

LATCH FREE

1、 檢查在等待什麼類型的LATCH,比如shared pool latch, cache buffer LRU chain
檢查V$SESSION_WAIT
P1-Address of latch  p2- Latch number  p3- 休眠等待次數
SELECT n.name, SUM(w.p3) Sleeps
  FROM V$SESSION_WAIT w, V$LATCHNAME n
 WHERE w.event = `latch free'
   AND w.p2 = n.latch#
 GROUP BY n.name;

2、 檢查LATCH對應的資源使用方式，比如library cache latch競爭嚴重，則檢查the hard and soft parse rates
3、 檢查存在LATCH競爭的SESSION所執行的SQL，是否需要最佳化。

Shared Pool and Library Cache Latch Contention

主要問題出在parse
1、 Unshared SQL
手工檢查那些只有一次執行的SQL是否相似：
SELECT sql_text  FROM V$SQLAREA
 WHERE executions < 4  ORDER BY sql_text;
或者:
SELECT SUBSTR(sql_text,1, 60), COUNT(*)
  FROM V$SQLAREA
 WHERE executions < 4
 GROUP BY SUBSTR(sql_text, 1, 60)
 HAVING COUNT(*) > 1;

2、 Reparsed Sharable SQL
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLAREA
ORDER BY PARSE_CALLS;
當PARSE_CALLS和EXECUTIONS相近的時候，說明進行了REPARSE。最佳化這些SQL

3、 By Session
檢查是否某個session執行了很多的parse。最好結合時間，看parse率
SELECT pa.sid, pa.value "Hard Parses", ex.value "Execute Count"
  FROM v$sesstat pa, v$sesstat ex
 WHERE pa.sid=ex.sid
   AND pa.statistic#=(select statistic#
       FROM v$statname where name='parse count (hard)')
   AND ex.statistic#=(select statistic#
       FROM v$statname where name='execute count')
   AND pa.value>0;

cache buffer lru chain

保護buffers在cache中的鏈，將buffer增加，移動，移出list時，必須要事先得到這個latch。
由大量buffer輸入輸出引起，比如低效的SQL重複訪問不合適的index (large index range scans) or many full table scans。讀Buffer會引起buffer在LUR中的移動。
查看Statements with very high logical I/O or physical I/O, using unselective indexes
或者CACHE太小，DBWR不能及時寫出髒資料，而使前台進程花費很長的時間保持著latch去尋找free buffer。

cache buffers chains

用來當從buffer cache中尋找、增加、刪除buffer時獲得。體現為某些資料區塊被嚴重爭用，即熱點塊。

cache buffer lru chain latch 和cache buffers chains latch的區別：

關鍵點在於他們保護的資料結構不同：前者保護LRU chain pointer。LRU chain 用來發現free buffers，移動熱buffer到MRU端，並協助完成寫髒資料和檢查點等動作；後者用來保護hash chain。Hash chain 用來通過hash 演算法（根據所在的檔案和block id）訪問cache在buffer中的blocks。

db file scattered read

表示通過multiblock read將data讀入到很多不連續的記憶體中（根據file_id/block_id,通過hash演算法分佈於不同的地方），通常發生於fast full scan of index，或者full table scan。
查看V$SESSION_WAIT: P1-FILE_ID, P2-BLOCK_ID, P3-NUMBER OF BLCOKS(>1)

在大型資料庫中，通常物理讀的等待和idle wait都排在最前面。當然也要考慮是否有以下的特徵：
Direct read wait(fts with parallel) / db file scattered read wait / Poor buffer cache hit ratio / 使用者回應時間慢。

查看哪些session進行中全表掃描：
SELECT s.sql_address, s.sql_hash_value,w.p1,w.p2
  FROM V$SESSION s, V$SESSION_WAIT w
 WHERE w.event LIKE 'db file%read' AND w.sid = s.sid ;

查看是哪個對象
SELECT segment_owner, segment_name
  FROM DBA_EXTENTS
 WHERE file_id = &p1 AND &p2 between block_id AND block_id + blocks - 1 ;

查看是哪個sql
SELECT sql_text
  FROM V$SQL
 WHERE HASH_VALUE=&sql_hash_value AND ADDRESS=&sql_address

db file sequential read

表示通過single block read將data讀入到連續的記憶體中, 往往通過索引.
查看V$SESSION_WAIT: P1-FILE_ID, P2-BLOCK_ID, P3-NUMBER OF BLCOKS(=1)
 

direct path read and direct path read (lob)

將資料從disk中直接讀到PGA中，而繞過SGA. 通常發生在DSS或WH
起因：
1、 排序過大，不能在sort memory中完成，而轉移到temp disk中。然後又讀入，形成直接讀。
2、 Parallel slaves 查詢
3、 The server process is processing buffers faster than the I/O system can return the buffers. 表明了很大的IO load
解決：
1、 查詢V$TEMPSEG_USAGE, 找出產生sort的sql；查詢V$SESSTAT, 查看sort size的大小。
2、 調整sql；如果WORKAREA_SIZE_POLICY=MANUAL, 增大SORT_AREA_SIZE;
如果WORKAREA_SIZE_POLICY=AUTO, 增大PGA_AGGREGATE_TARGET
3、 如果table 被定義為很高的degree of parallelism，將會引導optimizer使用parallel slaves 進行full table scan

direct path write

情況同read，等待直接從PGA中得到buffer，並寫入磁碟。