buffer cache深度分析及效能調整（五）

4.2 buffer cache的統計資訊

為了對buffer cache進行效能的診斷，oracle提供了很多有關buffer cache的統計資訊。這些統計資訊

大致可以分成三類：1）有關使用者發出的對記憶體資料塊的請求相關的統計資訊；2）有關DBWR後臺程式對記憶體資料塊處理相關的統計資訊；3）RAC相關的統計資訊。

我們在診斷buffer cache時，不需要關注所有的統計資訊。這裡主要介紹幾個重要的統計資訊，其他的統計資訊都可以到《Oracle9i Database Reference: Appendix C》中找到。如下所示：

SQL> SELECT name, value FROM v$sysstat WHERE name in (

2 'session logical reads',

3 'physical reads',

4 'physical reads direct',

5 'physical reads direct (lob) ',

6 'consistent gets',

7 'db block gets',

8 'free buffer inspected')

9 /

NAME VALUE

---------------------------------------------------------------- ----------

session logical reads 73797

db block gets 498

consistent gets 73299

physical reads 29017

free buffer inspected 0

physical reads direct 40

這裡做些簡單的解釋。

1) session logical reads：所有的邏輯讀的資料塊的數量。注意，其中包括先從硬碟上讀資料塊到記憶體裡，再從記憶體裡讀資料塊。

2) consistent gets：在一致性（consistent read）讀模式下讀取的記憶體裡的資料塊數量。包括從rollback segment裡讀取的資料塊數量以及從data block buffer裡讀取的資料塊數量。主要是通過select產生的。Update/delete也能產生很少量的此類資料塊。注意：如果oracle的執行時間過長，由於oracle的bug導致consistent gets大大超過實際的數量。因此建議使用‘no work - consistent read gets’, ‘cleanouts only - consistent read gets’,‘rollbacks only - consistent read gets’, ‘cleanouts and rollbacks - consistent read gets’之和來代替consistent gets的值。

3) db block gets：在當前（current）模式下讀取的記憶體裡的資料塊的數量。不是讀取過去某個時點的資料塊，而必須是當前最新的資料塊。主要是通過update/delete/insert來產生的，因為DML需要當前最新的資料塊才能對之進行改變。在字典管理表空間下，一些獲得當前可用擴充套件空間的select語句也會產生此類資料塊，因為必須得到當前最新的空間使用資訊才能擴充套件。邏輯上，session logical reads = consistent gets + db block gets。

4) physical reads：從硬碟裡讀取的資料塊的數量。注意，這個數量大於實際從硬碟裡讀取的數量，因為這部分block也包括了從作業系統快取裡讀取的資料塊數量。

5) physical reads direct：有些資料塊不會先從硬碟讀入記憶體再從記憶體讀入PGA再傳給使用者，而是繞過SGA直接從硬碟讀入PGA。比如並行查詢以及從臨時表空間讀取資料。這部分資料塊由於不快取使得hit ratio不會被提高。

6) physical reads direct (lob)：與physical reads direct一樣。

7) free buffer inspected：這個值表示為了找到可用資料塊而跳過的資料塊的數量。這些被跳過的資料塊就是髒的或被鎖定的資料塊。明顯，這個值如果持續增長或很高，就需要增加buffer cache的大小了。

在獲得了這些統計資訊以後，我們可以計算buffer cache的命中率：

Hit Ratio = 1 – (physical reads – physical reads direct - physical reads direct (lob) ) / session logical reads

Miss ratio = (physical reads – physical reads direct - physical reads direct (lob) ) / session logical reads

通常在OLTP下，hit ratio應該高於0.9。否則如果低於0.9則需要增加buffer cache的大小。在考慮

調整buffer cache hit ratio時，需要注意以下幾點。

1) 如果上次增加buffer cache的大小以後，沒有對提高hit ratio產生很大效果的話，不要盲目增加buffer cache的大小以提高效能。因為對於排序操作或並行讀，oracle是繞過buffer cache進行的。

2) 在調整buffer cache時，儘量避免增加很多的記憶體而只是提高少量hit ratio的情況出現。

我們還可以查詢每種buffer cache的統計資訊，主要關注的還是consistent_gets和db_block_gets以及

physical_reads的值。

SQL> SELECT name, block_size,physical_reads, db_block_gets,consistent_gets

2 FROM v$buffer_pool_statistics;

NAME BLOCK_SIZE PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS

-------------------- ---------- -------------- ------------- ---------------

DEFAULT 8192 28978 719 77591

DEFAULT 16384 2 80 11

v$sysstat中名稱以DBWR開頭的都是有關DBWR後臺程式相關的統計資訊。當DBWR程式寫完髒資料塊以後或者掃描完LRU連結串列以後更新這些統計資訊。DBWR會基於被觸發的頻率以及所處理的記憶體資料塊的數量與總記憶體資料塊的數量的比例，來進行自我調整。我們可以通過這些統計資訊得到一些對當前DBWR執行情況的認識。

4.3 buffer cache的等待事件

與buffer cache相關的等待事件包括：latch free、buffer busy waits、free buffer waits。曾經發生過的等

待事件可以從v$system_event（一個等待事件對應一行記錄）和v$session_event（一個session一個等待事件對應一行記錄）中看到。而當前系統正在經歷的等待事件可以從v$session_wait看到。

4.3.1 latch free等待

等待事件“latch free”中與buffer cache有關的有兩類：cache buffers chains latch和cache buffers lru chain

latch。在理解了上面所描述的有關buffer cache的內部管理機制以後，就應該很容易理解這兩個latch產生的原因。

對於buffer cache中的每個hash chain連結串列來說，都會有一個名為cache buffers chains latch的latch來保護對hash chain的併發操作，這種latch通常也叫作hash latch或CBC latch。資料庫中會有很多的cache buffers chains latch，每個latch都叫做child cache buffers chains latch。一個child cache buffers chains latch會管理多個hash chain。前面我們知道，hash chain的數量由一個隱藏引數：_db_block_hash_buckets決定。同樣也有一個隱藏引數：_db_block_hash_latches來決定有多少個cache buffers chains latch來管理這些hash chain。該引數的預設值由buffer cache中所含有的記憶體資料塊的多少決定，當記憶體資料塊的數量

·少於2052個時，_db_block_hash_latches = power(2,trunc(log(2, 記憶體塊數量 - 4) - 1))

·多於131075個時，_db_block_hash_latches = power(2,trunc(log(2, db_block_buffers - 4) - 6))

·位於2052與131075 buffers之間，_db_block_hash_latches = 1024

可以使用下面的SQL語句來確定當前系統的cache buffers chains latch的數量。

SQL> select count(distinct(hladdr)) from x$bh;

COUNT(DISTINCT(HLADDR))

-----------------------

1024

SQL> select count(*) from v$latch_children where name='cache buffers chains';

COUNT(*)

----------

1024

在知道了cache buffers chains latch的數量以後，我們只需要用hash chain的數量除以latch的數量以後，就可以算出每個latch管理多少個hash chain了。我們將下面7532除以1024，就可以知道，當前的系統中，每個latch大概對應8個hash chain。

SQL> select x.ksppinm, y.ksppstvl, x.ksppdesc

2 from x$ksppi x , x$ksppcv y

3 where x.indx = y.indx

4 and x.ksppinm like '\_%' escape '\'

5 and ksppinm like '%_db_block_hash_buckets%'

6 ;

KSPPINM KSPPSTVL KSPPDESC

---------------------- -------- -------------------------------------

_db_block_hash_buckets 7523 Number of database block hash buckets

當資料庫在hash chain搜尋需要的資料塊時，必須先獲得cache buffers chains latch。然後在掃描hash chain的過程中會一直持有該latch，直到找到所要的資料塊才會釋放該latch。當有程式一直在掃描某條hash chain，而其他程式也要掃描相同的hash chain時，其他程式就必須等待型別為cache buffers chains latch的latch free等待事件。

不夠優化的SQL語句是導致cache buffers chains latch的主要原因。如果SQL語句需要訪問過多的記憶體資料塊，那麼必然會持有latch很長時間。找出邏輯讀特別大的sql語句進行調整。v$sqlarea裡那些buffer_gets/executions為較大值的SQL語句就是那些需要調整的SQL語句。這種方式不是很有針對性，比較盲目。網上曾經有人提供了一個比較有針對性的、查詢這種引起較為嚴重的cache buffers chains latch的SQL語句的方式，其原理是根據latch的地址，到x$bh中找對應的buffer header，x$bh的hladdr表示該buffer header所對應的latch地址。然後根據buffer header可以找到所對應的表的名稱。最後可以到v$sqltext（也可以到stats$sqltext）中找到引用了這些表的SQL語句。我也列在這裡。where條件中的rownum<10主要是為了不要返回太多的行，只要能夠處理掉前10個latch等待就能有很大改觀。

select /*+ rule */ s.sql_text

from x$bh a,dba_extents b,

(select * from (select addr from v$latch_children

where name = 'cache buffers chains' order by sleeps desc)

where rownum<11) c,

v$sqltext s

where a.hladdr = c.addr

and a.dbarfil = b.relative_fno

and a.dbablk between b.block_id and b.block_id + b.blocks

and s.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE'

order by s.hash_value,s.address,s.piece

/

還有一個原因可能會引起cache buffers chains latch，就是熱點資料塊問題。這是指多個session重複訪問一個或多個被同一個child cache buffers chains latch保護的記憶體資料塊。這主要是應用程式的問題。大多數情況下，單純增加child cache buffers chains latches的個數對提高效能沒有作用。這是因為記憶體資料塊是根據資料塊地址以及hash chain的個數來進行hash運算從而得到具體的hash chain的，而不是根據child cache buffers chains latches的個數。如果資料塊的地址以及hash chain的個數保持一致，那麼熱點塊仍然很有可能會被hash到同一個child cache buffers chains latch上。可以通過v$session_wait的p1raw欄位來判斷latch free等待事件是否是由於出現了熱點塊。如果p1raw保持一致，那麼說明session在等待同一個latch地址，系統存在熱點塊。當然也可以通過x$bh的tch來判斷是否出現了熱點塊，該值越高則資料塊越熱。

SQL> select sid, p1raw, p2, p3, seconds_in_wait, wait_time, state

2 from v$session_wait

3 where event = 'latch free'

4 order by p2, p1raw;

SID P1RAW P2 P3 SECONDS_IN_WAIT WAIT_TIME STATE

---- -------- --- --- --------------- ---------- ------------------

38 6666535C 13 1 1 2 WAITED KNOWN TIME

42 6666535C 13 1 1 2 WAITED KNOWN TIME

44 6666535C 13 3 1 4 WAITED KNOWN TIME

………………………

85 6666535C 13 3 1 12 WAITED KNOWN TIME

214 6666535C 138 1 1 2 WAITED KNOWN TIME

接下來，我們就可以根據p1raw的值去找到所對應的記憶體資料塊以及對應的表的名稱了。

select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name

from x$bh a, dba_objects b

where (a.obj = b.object_id or a.obj = b.data_object_id)

and a.hladdr = '6666535C';

要解決熱點塊的問題，可以通過將熱點塊中的行分散到多個資料塊中去，這樣原來的熱點塊就變成了

多個資料塊，這樣被hash到同一個latch的機率就降低了。如果熱點塊屬於表，則可以先將表的資料匯出來，然後增加表的pctfree值，最後將資料再匯入。如果熱點塊屬於索引，則可以設定較高的 pctfree引數後，重建索引。注意，這會增加索引的高度。

通過前面我們已經知道，每個working set都會有一個名為cache buffers lru chain的latch（也叫做lru latch）來管理。任何要訪問working set的程式都必須先獲得cache buffers lru chain latch。cache buffers lru chain latch爭用也是由於低效的掃描過多的記憶體資料塊的SQL語句引起的。調整這些語句以降低邏輯讀和物理讀。只要修改一下上面找引起cache buffers chains latch的SQL語句即可找到這樣的SQL語句。