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

4. buffer cache的優化

4.1 buffer cache的設定優化

buffer cache的設定隨著oracle版本的升級而不斷變化。8i下使用db_block_buffers來設定，該參數列示buffer cache中所能夠包含的記憶體資料塊的個數；9i以後使用db_cache_size來設定，該參數列示buffer cache的總共的容量，可以用位元組、K、M為單位來進行設定。而到了10g以後則更加簡單，甚至可以不用去單獨設定buffer cache的大小。因為10g引入了ASMM（Automatic Shared Memory Management）這樣一個可以進行自我調整的元件，該元件可以自動調整shared pool size、db cache size等SGA中的元件。只需要設定sga_target引數，則其他元件就能夠根據系統的負載和歷史資訊自動的調整各個部分的大小。要啟動ASMM，只需要設定statistics_level為typical或all。

oracle8.0以前只能設定一種buffer cache，而從8.0以後，oracle提供了三種型別的buffer cache，分別是default、keep、recyle。keep和recycle是可選的，default必須存在。8i以前使用db_block_buffer設定default、buffer_pool_keep設定keep、buffer_pool_recycle設定recyle。而8i以後使用db_cache_size設定default、db_keep_cache_size設定keep、db_recycle_cache_size設定recycle。10g不能自動設定db_keep_cache_size和db_recycle_cache_size，必須手工設定。

同時，8i以前，這三種buffer cache是獨立指定的，互不制約。而8i以後，這三種buffer cache是有相互制約關係的。如果指定了keep和recycle的buffer cache，則default型別的buffer cache的大小就是db_cache_size - buffer_pool_keep - buffer_pool_recycle。

通常將經常訪問的物件放入keep型別的buffer cache裡，而將不常訪問的大表放入recycle型別的buffer cache裡。其他沒有指定buffer cache型別的物件都將進入default型別的buffer cache裡。為物件指定buffer cache型別的方法如下：

SQL> create table test (n number) storage (buffer_pool keep);

SQL> alter table test storage (buffer_pool recycle);

如果沒有指定buffer_pool短語，則表示該物件進入default型別的buffer cache。

這裡要說明的是，從名字上看，很容易讓人誤以為這三種buffer cache提供了三種不同的管理記憶體數

據塊的機制。但事實上，它們之間在管理和內部機制上沒有任何的區別。它們僅僅是為DBA們提供了一個選擇，就是能夠將資料庫物件分成“非常熱的”、“比較熱的”和“不熱的”這三種型別。因為資料庫中總會存在一些“非常熱”的物件，它們頻繁的被訪問。而如果某個時候系統偶爾做了一次大表的全表掃描，就有可能將這些物件清除出記憶體。為了防止這種情況的發生，我們可以設定keep型別的buffer cache，並將這種物件都移入keep buffer cache中。同樣的，資料庫中也總會有一些很大的表，可能每天為了生成一張報表，而只需要訪問一次就可以了。但有可能就是這麼一次訪問，就將大部分的記憶體資料塊清除出了buffer cache。為了避免這種情況的發生，可以設定recycle型別的buffer cache，並將這種偶爾訪問的大表移入recycle buffer cache。

毫無疑問，如果你要設定這三種型別的buffer cache，你需要自己研究並等於你的資料庫中的物件進行分類，並計算這些物件的大小，從而才能夠正確的把它們放入不同的buffer cache。但是，不管怎麼說，設定這三種型別的buffer cache只能算是最低層次的優化，也就是說在你沒有任何辦法的情況下，可以考慮設定他們。但是如果你能夠優化某條buffer gets非常高SQL使其buffer gets降低50%的話，就已經比設定多個buffer cache要好很多了。

9i以後還提供了可以設定多種資料塊尺寸（2、4、8、16 或 32k）的buffer cache，以便存放不同資料

塊尺寸的表空間中的物件。使用初始化引數：db_Nk_cache_size來指定不同資料塊尺寸的buffer cache，這裡的N就是2、4、8、16 或 32。建立資料庫時，使用初始化引數：db_block_size所指定預設的資料塊尺寸用於system表空間。然後可以指定最多4個不同資料塊尺寸的表空間，每種資料塊尺寸的表空間必須對應一種不同尺寸的buffer cache，否則不能建立不同資料塊尺寸的表空間。

SQL> create tablespace tbs_test_16k

2 datafile 'C:\oracle\oradata\ora92\tbs_test_16k.dbf' size 10M

3 blocksize 16k;

create tablespace tbs_test_16k

*

ERROR 位於第 1 行:

ORA-29339: 表空間塊大小 16384 與配置的塊大小不匹配

SQL> show parameter db_16k_cache_size

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

我們可以看到，由於16k資料塊所對應的buffer cache沒有指定，所以建立16k資料塊的表空間會失

敗。於是我們先設定db_16k_cache_size，然後再試著建立16k資料塊的表空間。

SQL> alter system set db_16k_cache_size=10M;

系統已更改。

SQL> create tablespace tbs_test_16k

2 datafile 'C:\oracle\oradata\ora92\tbs_test_16k.dbf' size 10M

3 blocksize 16k;

表空間已建立。

不同尺寸資料塊的buffer cache的管理和內部機制與預設資料塊的buffer cache沒有任何的分別。它最大的好處是，當使用可傳輸的表空間從其他資料庫中將不同於當前預設資料塊尺寸的表空間傳輸過來的時候，可以不做很多處理的直接匯入到當前資料庫，只需要設定對應的資料塊尺寸的buffer cache即可。同時，它對於調優OLTP和OLAP混合的資料庫也有一定的用處。OLTP環境下，傾向於使用較小的資料塊，而OLAP環境下，由於基本都是執行全表掃描，因此傾向於使用較大的資料塊。這時，可以將OLAP的錶轉移到使用大資料塊（比如32k）的表空間裡去。而將OLTP的表放在中等大小的資料塊（比如8k）的表空間裡。

對於應該設定buffer cache為多大，oracle從9i開始通過設定初始化引數：db_cache_advice，從而提供了可以參照的建議值。oracle會監控default型別、keep型別和recycle型別的buffer cache的使用，以及其他五種不同資料庫尺寸（2、4、8、16 或 32k）的buffer cache的使用。在典型負荷的時候，啟用該引數，從而收集資料幫助使用者確定最佳的db_cache_size的大小。該引數有三個值：

1) off：不收集資料。

2) on：開始分配記憶體收集資料，有可能引發CPU和記憶體的負擔，可能引起4031錯。

3) ready：不收集資料，但是收集資料的記憶體已經預先分配好了。通過把該引數值從off設定為ready，然後再設定為on，就可以避免出現4031錯。

oracle會根據當前所監控到的物理讀的速率，從而估算出在不同大小尺寸的buffer cache下，所產生的可能的物理讀的數量。oracle會將這些收集到的資訊放入檢視：v$db_cache_advice中。每種型別的buffer cache都會有相應的若干條記錄來表示所建議的buffer cache的大小。比如下面，我們顯示對於預設型別的、預設資料塊尺寸的buffer cache的建議大小應該是多少。

SQL> SELECT size_for_estimate, buffers_for_estimate,

2 estd_physical_read_factor,estd_physical_reads

3 FROM v$db_cache_advice

4 WHERE NAME = 'DEFAULT'

5 AND block_size = (SELECT VALUE

6 FROM v$parameter

7 WHERE NAME = 'db_block_size')

8 /

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

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

4 500 1.3869 40154

8 1000 1.3848 40093

12 1500 1.1861 34339

16 2000 1.1397 32996

20 2500 1 28952

24 3000 1 28952

28 3500 1 28952

32 4000 1 28952

36 4500 0.8671 25104

40 5000 0.8671 25104

44 5500 0.8671 25104

48 6000 0.7422 21488

52 6500 0.7422 21488

56 7000 0.7422 21488

60 7500 0.554 16040

64 8000 0.554 16040

68 8500 0.554 16040

72 9000 0.554 16040

76 9500 0.554 16040

80 10000 0.554 16040

這裡的欄位estd_physical_read_factor表示在相應的buffer cache尺寸（由欄位size_for_estimate表示）

下，估計從硬碟裡讀取資料的次數除以在記憶體裡讀取資料的次數。如果沒有發生物理讀則該比值為空。在

記憶體足夠的前提下，這個比值應該是越低越好的。從上面的輸出中，我們可以看到，如果將buffer cache

設定為60M，可以獲得較好的效能，物理讀也將會有一個顯著的下降。但是設定為大於60M的話（比如

64M或68M），則不會降低物理讀，反而浪費記憶體空間。所以從上面的查詢結果中，我們可以知道，設定

為60M是比較合適的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9842/viewspace-399669/，如需轉載，請註明出處，否則將追究法律責任。

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

4. buffer cache的優化

4.1 buffer cache的設定優化

相關文章