buffer cache size的學習筆記

jolly10發表於2009-09-17
讀深入解析ORACLE的buffer cache原理一篇的學習筆記[@more@]
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 16M


SQL> @gethidpar
Enter value for par: lru_latches
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%lru_latches%'

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_db_block_lru_latches 8 number of lru latches


SQL> select count(*) From v$latch_children where name='cache buffers lru chain';

COUNT(*)
----------
8

SQL> @gethidpar
Enter value for par: hash_latches
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%hash_latches%'

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_db_block_hash_latches 512 Number of database block hash latches


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

COUNT(*)
----------
512


SQL> @gethidpar
Enter value for par: buckets
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%buckets%'

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_lm_num_pt_buckets 4096 number of buckets in the object affinity hash table
_db_block_hash_buckets 4096 Number of database block hash buckets


db_block_hash_buckets的數量是buffer block的2倍,16M=16*1024/8=2048*2=4096。

以前一直有個疑惑,為什麼需要兩倍的buckets來管理buffer block?豈不是每兩個bucket管理一個block?

其實bucket和buffer數量的關係不重要,bucket是hash bucket是為資料塊做雜湊用的,最後用這個數量就是oracle覺得是最均衡的,太多的話

實際上也沒意義,太少肯定會使dba算hash的雜湊太低造成bucket“保護”太多,實際上不要想bucket是保護塊用的,對塊的保護工作大多都是

header管理的,bucket就是定位,快速定位,獲得相應的latch釋放相應的latch而已。


latch <= 512 _db_block_hash_latchs
||
/
bucket<= 4096 _db_block_hash_buckets
||
/
chain <= 一個bucket有一個buffer chain,所以也應該是4096個buffer chain
||
/
buffer header <= 對應於X$BH的每一行記錄


在測試bucket數量及X$BH記錄數量時有些問題

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 16M
SQL> @gethidpar
Enter value for par: buckets
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%buckets%'

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_lm_num_pt_buckets 4096 number of buckets in the object affinity hash table
_db_block_hash_buckets 4096 Number of database block hash buckets


看深入解析書上eygle說從8i之後,bucket數量是block buffer的2倍,那麼16M的數量bucket應該是16M*1024/8*2=4096,這裡沒有問題。


但是將db_cache_size增加到180M後

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 180M
SQL> @gethidpar
Enter value for par: buckets
old 4: AND x.ksppinm LIKE '%&par%'
new 4: AND x.ksppinm LIKE '%buckets%'

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_lm_num_pt_buckets 4096 number of buckets in the object affinity hash table
_db_block_hash_buckets 65536 Number of database block hash buckets

180M*1024/8*2=46080,而buckets的數量是65536,明顯大於46080,是不是10g的bucket演算法改變了?

在itpub上問網友時,一位兄弟解釋到

Oracle 10g 作業系統solaris的規則如下:
http://www.itpub.net/viewthread.php?tid=1216889&pid=14270003&page=1&extra=#pid14270003


Db_cache_size _db_block_hash_buckets
<32m 8192
<64m 16384
<128m 32768
<256m 65536
<512m 131072
<1024m 262144
<2048m 524288

大概測試了一下,的確是如上所說。



還有另外一個問題如下,還不知道是為什麼,希望有人告訴我。


SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 16M

SQL> select count(*) from x$BH;

COUNT(*)
----------
1996

buffer cache為16M,應該有2048個block可以存入cache,而X$BH為何只有1996行呢?

同樣,將db_cache_size設為180M,應該能存入23040個block,X$BH卻只有22455行?

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 180M

SQL> select count(*) from v$bh;

COUNT(*)
----------
22455

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

相關文章