读深入解析ORACLE的buffer cache原理一篇的学习笔记

读深入解析ORACLE的buffer cache原理一篇的学习笔记
http://blog.itpub.net/post/7268/491693
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

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

  COUNT(0)
----------
   1863893

SQL> show parameter db_cach

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice                      string                 ON
db_cache_size                        big integer            15024M
SQL> select * from v$bh where rownum<=1;

     FILE#     BLOCK#     CLASS# STATUS                XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADD LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS DI TE PI ST DI NE           OBJD        TS#
---------- ---------- ---------- -------------- ---------- ------------ ------------- ---------------- ----------------- ------------------ -- -- -- -- -- -- ---------- ----------
        12    1949160          1 xcur                    0            0             0 00    N  N  N  N  N  N       14467          5

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27573546/viewspace-740777/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27573546/viewspace-740777/