buffer cache的疑问

最新推荐文章于 2021-04-16 05:48:15 发布

ckomuo086028

最新推荐文章于 2021-04-16 05:48:15 发布

阅读量156

点赞数

看深入浅出的一些疑问，希望以后能弄清楚。[@more@]

我的测试库的db_cache_size为16MB,此时的_db_block_hash_buckets为4096,正好为block buffer的2倍(16*1024/8)*2.
在oracle8i之前,该参数是db_block_buffers/4,而之后该参数缺省为db_block_buffers*2.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter db_cache_size

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

SQL> set linesize 1000
SQL> col NAME for a30
SQL> col DESCRIB for a60
SQL> col VALUE for a10

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=userenv('Instance')
and y.inst_id=userenv('Instance')
and x.indx=y.indx
and x.ksppinm like '%&par%'; 2 3 4 5 6
Enter value for par: hash_bucket
old 6: and x.ksppinm like '%&par%'
new 6: and x.ksppinm like '%hash_bucket%'

NAME VALUE DESCRIB
-------------------- ---------- ------------------------------------------------------------
_lm_res_hash_bucket 0 number of resource hash buckets
_db_block_hash_buckets 4096 Number of database block hash buckets

转储buffer的内容,踊踪文件中的Cache Buffer Chain的数量正好是4096

SQL> alter session set events 'immediate trace name buffers level 10';

SQL> SELECT d.VALUE
2 || '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
3 FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d
/

TRACE_FILE_NAME

/u01/app/admin/orcl/udump/orcl_ora_4432.trc

[oracle@rhel131 pfile]$ grep ^CHAIN /u01/app/admin/orcl/udump/orcl_ora_4432.trc |wc -l
4096

看一下部分内容

[oracle@rhel131 pfile]$ grep ^CHAIN /u01/app/admin/orcl/udump/orcl_ora_4432.trc |head -20
CHAIN: 0 LOC: 0x26adeda4 HEAD: [207e8fc8,207e8fc8]
CHAIN: 1 LOC: 0x26adedac HEAD: [213f481c,213f481c]
CHAIN: 2 LOC: 0x26adedb4 HEAD: [NULL]
CHAIN: 3 LOC: 0x26adedbc HEAD: [213f2364,20bf2b78]
CHAIN: 4 LOC: 0x26adedc4 HEAD: [NULL]
CHAIN: 5 LOC: 0x26adedcc HEAD: [NULL]
CHAIN: 6 LOC: 0x26adedd4 HEAD: [207fc37c,20bf9248]
CHAIN: 7 LOC: 0x26adeddc HEAD: [NULL]
CHAIN: 8 LOC: 0x26adee60 HEAD: [NULL]
CHAIN: 9 LOC: 0x26adee68 HEAD: [NULL]
CHAIN: 10 LOC: 0x26adee70 HEAD: [NULL]
CHAIN: 11 LOC: 0x26adee78 HEAD: [NULL]
CHAIN: 12 LOC: 0x26adee80 HEAD: [NULL]
CHAIN: 13 LOC: 0x26adee88 HEAD: [213fa1b4,213fa1b4]
CHAIN: 14 LOC: 0x26adee90 HEAD: [NULL]
CHAIN: 15 LOC: 0x26adee98 HEAD: [213ec4a8,213ec4a8]
CHAIN: 16 LOC: 0x26adef1c HEAD: [NULL]
CHAIN: 17 LOC: 0x26adef24 HEAD: [NULL]
CHAIN: 18 LOC: 0x26adef2c HEAD: [NULL]
CHAIN: 19 LOC: 0x26adef34 HEAD: [NULL]

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

我是觉的bucket太多了，快速定位的话，bucket大小等同buffer pool能放的block的数量已足够了，给那么多的bucket，在buffer pool满了后，其实有大部分的bucket是空的。
我做了个实验: buffer_pool为16M,只能存放 2048个block.

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 16M

SQL> col segment_name for a10
SQL> select segment_name,blocks From dba_segments where segment_name='ABC';

SEGMENT_NA BLOCKS
---------- ----------
ABC 9216

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

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

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

Cache Buffer Chain的数量应该是和bucket的数量一样多的，看到dump出来的CHAIN有许多为空的，说明有许多的bucket下也是没有存在block的。

[oracle@rhel131 ~]$ grep ^CHAIN /u01/app/admin/orcl/udump/orcl_ora_4729.trc |wc -l
4096

总共有4096个 CHAIN,正好是能存放block数量的2倍。

[oracle@rhel131 ~]$ grep ^CHAIN /u01/app/admin/orcl/udump/orcl_ora_4729.trc | grep NULL |wc -l
2450

为空的则有2450个，说明只有4096-2450=1646个CHAIN上有block.

不知道的我理解的对不对？