学习BUFFER CACHE的一点问题和实验理解过程一-优快云博客

最初是发了下面的一个帖子求助，后来随着自己的思考和实验，
总结了一点点东西，不敢作为分享，怕自己理解的不正确对其他人产生误导。仅作为自己的一点学习笔记，以后有了新的理解再做更新。
BUFFER CACHE水太深，目前自己基础太差，不敢过多涉入，浅尝辄止！
《无法理解的多出来的几个BUFFER HEADER来自何方，请指教！》
环境：
SYS@PROD>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
过程：
建一张表并插入一条记录，然后获取该记录的文件号、块号
SYS@PROD>create table zn.t3(x int, y int);
Table created.
SYS@PROD>insert into zn.t3 values(1,2);
1 row created.
SYS@PROD>commit;
Commit complete.
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from zn.t3 where x=1;
     FILE#     BLOCK#
---------- ----------
         4        213
SYS@PROD>select obj#,dataobj# from obj$ where name='T3';
      OBJ#   DATAOBJ#
---------- ----------
     13506      13506
然后把BUFFER CACHE的BH转出出来看一下：
SYS@PROD>ALTER SESSION SET EVENTS 'immediate trace name buffers level 1';
Session altered.
SYS@PROD>select * from v$diag_info where name='Default Trace File';
   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Default Trace File                                               /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_2561.trc
vi /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_2561.trc
在trace file中搜索"objn: 13506"，即打算把T3表相关的块信息DUMP出来观察一下。
在查看之前，个人认为应该只会有两个BH，一个是T3表的SEGMENT HEADER对应的BH，一个是T3表中X=1的记录所在块的BH。
但事实是，搜索"objn: 13506"，得到多达8个BH，它们的块号分别不同，按照class分类看了一下：
class: 1--5个data block，其中rdba: 0x010000d5 (4/213)对应的就是记录X=1的BH，其余4个是从哪里来的？
class: 4--1个segment header，这个应该是T3的段头的BH
class: 8--1个1st level bmb
class: 9--1个2nd level bmb
问题就是，我认为应该只有2个BH，那多出来的6个来自哪里？我试图根据其中的文件号、块号构建ROWID到T3表中查询，也一无所获，是否还有其他途径能够得知多出来你的6个BH来自何方？
全部8个BH的DUMP信息列在下方，以及后面尝试追溯的脚本，请指教原因在哪里，还是我的方法有问题？如果太过初级，请提醒我关闭问题，多谢！
汇总一下：
class: 1--5个data block
BH (0x747eeca8) file#: 4 rdba: 0x010000d3 (4/211) class: 1 ba: 0x74668000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 120,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x8412fd40,0x8412fd40] lru: [0x777d85c0,0x747eec60]
obj-flags: object_ckpt_list
ckptq: [0x747eeb78,0x777d84d8] fileq: [0x747eeb88,0x83c3a2e0] objq: [0x7f3f4670,0x747eec88]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xe.287cc.0] LSCN: [0x0.7581d] HSCN: [0x0.7581d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747fa128) file#: 4 rdba: 0x010000d4 (4/212) class: 1 ba: 0x74798000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 110,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x84ff36c0,0x84ff36c0] lru: [0x747fa340,0x747fa0e0]
obj-flags: object_ckpt_list
ckptq: [0x787e7f68,0x747fa388] fileq: [0x83c41f10,0x747fa398] objq: [0x747fa498,0x7f3f1120]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xe.287cc.0] LSCN: [0x0.7581d] HSCN: [0x0.7581d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747dbca8) file#: 4 rdba: 0x010000d5 (4/213) class: 1 ba: 0x74468000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 115,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x841d7900,0x841d7900] lru: [0x747dbec0,0x747dbc60]
obj-flags: object_ckpt_list
ckptq: [0x763e6c68,0x787f1e88] fileq: [0x843ef6d0,0x747dbde8] objq: [0x747dbee8,0x7f3f7620]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xe.287cc.0] LSCN: [0x0.7581d] HSCN: [0x0.75821] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747e61b8) file#: 4 rdba: 0x010000d6 (4/214) class: 1 ba: 0x7457e000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 131,19
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x840c8360,0x840c8360] lru: [0x747e63d0,0x747e6170]
obj-flags: object_ckpt_list
ckptq: [0x787fa258,0x74ff9a08] fileq: [0x843f7300,0x747e62f8] objq: [0x747e63f8,0x7f3eef48]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xe.287cc.0] LSCN: [0x0.7581d] HSCN: [0x0.7581d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x747eeb78) file#: 4 rdba: 0x010000d7 (4/215) class: 1 ba: 0x74666000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 120,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x84280140,0x84280140] lru: [0x747eed90,0x747eeb30]
obj-flags: object_ckpt_list
ckptq: [0x747ef758,0x747eeca8] fileq: [0x83c3a2e0,0x747eecb8] objq: [0x747eedb8,0x7f3f4670]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xe.287cc.0] LSCN: [0x0.7581d] HSCN: [0x0.7581d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
class: 4--segment header，这个应该是T3的段头
BH (0x747fa388) file#: 4 rdba: 0x010000d2 (4/210) class: 4 ba: 0x7479c000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 110,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x8423ff60,0x8423ff60] lru: [0x747fa5a0,0x747fa340]
obj-flags: object_ckpt_list
ckptq: [0x747fa128,0x747fa258] fileq: [0x747fa138,0x83c41f10] objq: [0x7f3f1120,0x747fa238]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xe.287c2.0] LSCN: [0x0.75813] HSCN: [0x0.7581d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
class: 8--1st level bmb
BH (0x747e62e8) file#: 4 rdba: 0x010000d0 (4/208) class: 8 ba: 0x74580000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 131,19
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x84197720,0x84197720] lru: [0x747e6500,0x747e62a0]
obj-flags: object_ckpt_list
ckptq: [0x74ff9a08,0x77fe8c78] fileq: [0x747e61c8,0x77fe8c88] objq: [0x7f3eef48,0x747e62c8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xe.287c2.0] LSCN: [0x0.75815] HSCN: [0x0.7581d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
class: 9--2nd level bmb
BH (0x747dbdd8) file#: 4 rdba: 0x010000d1 (4/209) class: 9 ba: 0x7446a000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 115,28
dbwrid: 0 obj: 13506 objn: 13506 tsn: 4 afn: 4 hint: f
hash: [0x84087500,0x84087500] lru: [0x747dbff0,0x747dbd90]
obj-flags: object_ckpt_list
ckptq: [0x787f1e88,0x757dbca8] fileq: [0x747dbcb8,0x757dbcb8] objq: [0x7f3f7620,0x747dbdb8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xe.287c2.0] LSCN: [0x0.75814] HSCN: [0x0.75814] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
我想弄清楚的是为什么同一个objn: 13506，其中class=1的就有多达5个BH，而表T3中的记录只有一条。且对应的文件号相同块号不同，除了块号213的是T3表的X=1的记录外，其他四个来自哪里？
SYS@PROD>select addr, indx, hladdr, flag,tch,TS#, FILE#    ,DBABLK ,state,obj,ba from x$bh where file#=4 and dbablk between 211 and 215 order by dbablk;
ADDR                   INDX HLADDR                 FLAG        TCH        TS#      FILE#     DBABLK      STATE        OBJ BA
---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
00007FF7DAA09B80       3958 000000008412E908   35651584          2          4          4        211          1      13506 0000000074668000
00007FF7DAA09B80        237 0000000084FF1EC8   35651584          2          4          4        212          1      13506 0000000074798000
00007FF7DAA09B80       6247 00000000841D5C80   35651584          2          4          4        213          1      13506 0000000074468000
00007FF7DAA09B80       2541 00000000840C76A0   35651584          2          4          4        214          1      13506 000000007457E000
00007FF7DAA09B80       8527 000000008427EFF8   35651584          2          4          4        215          1      13506 0000000074666000
SYS@PROD>select name from obj$ where obj#=13506;
NAME
------------------------------
T3
select DBMS_ROWID.ROWID_CREATE (1-生成rowid,13506-对象号,4-文件号,213-块号,0-行号) from dual;
SYS@PROD>select DBMS_ROWID.ROWID_CREATE (1,13506,4,213,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAADTCAAEAAAADVAAA
SYS@PROD>select * from zn.t3 where rowid='AAADTCAAEAAAADVAAA';
         X          Y
---------- ----------
         1          2
SYS@PROD>select DBMS_ROWID.ROWID_CREATE (1,13506,4,211,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAADTCAAEAAAADTAAA
SYS@PROD>select * from zn.t3 where rowid='AAADTCAAEAAAADTAAA';
no rows selected
还是没弄明白多出来的BH是哪儿来的？
=============================================================================================
?
我把其中一个data block 212 dump出来看：
Block header dump: 0x010000d4
Object id on Block? Y
seg/obj: 0x34c2 csc: 0x00.7581d itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x10000d0 ver: 0x01 opc: 0
     inc: 0 exflg: 0
发现block header上已经有了OBJECT ID:
Object id on Block? Y
seg/obj: 0x34c2--十进制就是13506，就是T3表，与213块上的OBJECT ID相同
但是下面的nrow=0，说明块是空的，还没有写数据进去，但是块头已经被写入object id了。
T3所在表空间的属性如下：
TABLESPACE_NAME                BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
EXAMPLE                              8192 LOCAL      AUTO
猜想这可能是在建表或插入第一条记录的时候就在若干个块头上写入了表的OBJECT ID，包括空白的块，留待以后写入记录的时候用。至于这与ORACLE的存储空间管理方式有什么关系，这样提前写入OBJECT ID的方式有什么好处，我再自学一下。

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

转载于:http://blog.itpub.net/26521853/viewspace-1137815/