ORACLE （4）： buffer cache(1-基础机构)
什么是buffer cache（buffer cache 结构
buffer：是将block拷贝到内存上面，和block一一对应
buffer header:描述buffer的结构，状态，使用情况等，每个buffer都有一个buffer head 包含：dba（data block address）| BA (buffer address)
buffer bucket:是一种维护一组buffer header，有相关联的表空间，文件号，块号的内存结构
(属于 shared pool)
buffer chain：是一种有关系的buffer header的列表
LRU:用来描述或者说指定如何去去空闲的buffer
RBA(redo byte address):sequence number+block number+ offset
buffer cache的作用
a.减少I/O
b.通过构造CR块，从而提供读一致性功能。
c.通过提供各种lock、latch机制，从而提供多个进程并发访问同一个数据块的功能
总体结构：
其中
Latch ，hash bucket 属于 shared pool
buffer header buffer 属于 buffer cache,(x$bh)
BH的结构：
对应的内部表：
SQL> desc X$BH
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
HLADDR RAW(8) --latch的内存地址
BLSIZ NUMBER
NXT_HASH RAW(8) --hash 链表
PRV_HASH RAW(8) --hash 链表
NXT_REPL RAW(8)
PRV_REPL RAW(8)
FLAG NUMBER
FLAG2 NUMBER
LOBID NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER
TS# NUMBER --表空间
FILE# NUMBER --文件号
DBARFIL NUMBER
DBABLK NUMBER
CLASS NUMBER
STATE NUMBER
MODE_HELD NUMBER
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW(8)
DIRTY_QUEUE NUMBER
SET_DS RAW(8)
OBJ NUMBER
BA RAW(8)
CR_SCN_BAS NUMBER
CR_SCN_WRP NUMBER
CR_XID_USN NUMBER
CR_XID_SLT NUMBER
CR_XID_SQN NUMBER
CR_UBA_FIL NUMBER
CR_UBA_BLK NUMBER
CR_UBA_SEQ NUMBER
CR_UBA_REC NUMBER
CR_SFL NUMBER
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER
LRBA_BNO NUMBER
HSCN_BAS NUMBER
HSCN_WRP NUMBER
HSUB_SCN NUMBER
US_NXT RAW(8)
US_PRV RAW(8)
WA_NXT RAW(8)
WA_PRV RAW(8)
OQ_NXT RAW(8)
OQ_PRV RAW(8)
AQ_NXT RAW(8)
AQ_PRV RAW(8)
OBJ_FLAG NUMBER
TCH NUMBER
TIM NUMBER
CR_RFCNT NUMBER
SHR_RFCNT NUMBER
查找记录的BA地址
SQL> select * from study.test where rownum =1;
ID
------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
1
AAAAAAAAAAA1
SQL> desc dbms_rowid;
FUNCTION ROWID_BLOCK_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_CREATE RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN
RELATIVE_FNO NUMBER IN
BLOCK_NUMBER NUMBER IN
ROW_NUMBER NUMBER IN
PROCEDURE ROWID_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_RELATIVE_FNO RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_ROW_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_TO_ABSOLUTE_FNO RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
FUNCTION ROWID_TO_EXTENDED RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TO_RESTRICTED RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TYPE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_VERIFY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as bk_num,dbms_rowid.ROWID_RELATIVE_FNO(rowid) as f_num from study.test where rownum =1;
BK_NUM F_NUM
---------- ----------
131 4
SQL> select HLADDR,TS#,FILE#,STATE,BA from x$bh where FILE# =4 and DBABLK = 131;
HLADDR TS# FILE# STATE BA
---------------- ---------- ---------- ---------- ----------------
00000000D03048B8 4 4 0 00000000B55AC000
查看BH的大小:
SQL> SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm like '%_db_block_buffers%';
KSPPINM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
KSPPDESC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_db_block_buffers
69090
Number of database blocks cached in memory: hidden parameter
SQL> select t.COMPONENT,t.CURRENT_SIZE/1024/1024 from v$sga_dynamic_components t where t.COMPONENT ='DEFAULT buffer cache';
COMPONENT T.CURRENT_SIZE/1024/1024
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DEFAULT buffer cache 564 M
则buffer cache为
SQL> select 69090*8/1024 from dual;
69090*8/1024
------------
539.765625
对应的空间就为bh的大小：
SQL> select (564-539)*1024*1024/69090 from dual;
(564-539)*1024*1024/69090
-------------------------
379.42394
差不多就是380 字节。
熟悉CBC latch和hash bucket
SQL> SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm like '%_db_block_hash_buckets%';
KSPPINM
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
KSPPDESC
--------------------------------------------------------------------------------
_db_block_hash_buckets
262144
Number of database block hash buckets
select 262144/69090 from dual;
在ORACLE11G中，默认值是大于2倍的buffer数量的最小的2的幂的值。举例如buffer数量是69090，2倍就是138180，那么大于1000的最小的2的幂的值是2的18次幂，也就是就会有个262144hash bucket。
对应的latch的大小:
SQL> SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y 2
3 WHERE x.indx = y.indx AND ksppinm like '%_db_block_hash_latches%';
KSPPINM
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
KSPPDESC
--------------------------------------------------------------------------------
_db_block_hash_latches
8192
Number of database block hash latches
需要管理的buchet个数：
SQL> select 262144/8192 from dual;
262144/8192
-----------
32
测试结果每个latch管理32个bucket