Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 无法理解的多出来的几个BUFFER HEADER来自何方，请指教 ...

8 积分	1 好友	4 主题

发消息

[升级] 无法理解的多出来的几个BUFFER HEADER来自何方，请指教！

1^#

发表于 2014-4-6 23:48:48 | 查看: 3928| 回复: 10

本帖最后由 timothyzhn 于 2014-4-6 23:48 编辑

环境：
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是哪儿来的？

分享0

收藏0 回复只看该作者道具举报

timothyzhn

2^#

发表于 2014-4-8 10:57:47

我把其中一个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的方式有什么好处，我再自学一下。

回复只看该作者道具举报

timothyzhn

3^#

发表于 2014-4-8 10:58:10

问题关闭，留待自学，叨扰给位大侠了！多谢关注！

回复只看该作者道具举报

timothyzhn

4^#

发表于 2014-4-8 14:20:07

本帖最后由 timothyzhn 于 2014-4-8 14:22 编辑

重新总结了一下，怀疑跟段空间管理方式有关。
一、在SEGMENT SPACE MANAGEMENT=MANUAL的SYSTEM表空间下建表：
1、建表sys.t4
SYS@PROD>create table t4(x int, y int);

Table created.

SYS@PROD>select tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces where tablespace_name='SYSTEM';

TABLESPACE_NAME             BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
SYSTEM                            8192 LOCAL    MANUAL

SYS@PROD>select obj# from obj$ where name='T4';

   OBJ#
----------
   13587

SYS@PROD>alter session set events 'immediate trace name buffers level 1';

Session altered.
--ONLY ONE BH FOR SEGMENT HEADER
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
  obj-flags: object_ckpt_list
  ckptq: [0x70fe5118,0x70beb868] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79efd] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

此时只有一个segment header block header。

2、向sys.t4表中插入一条记录
SYS@PROD>insert into t4 values(1,2);

1 row created.

SYS@PROD>commit;

Commit complete.

SYS@PROD>alter session set events 'immediate trace name buffers level 1';

Session altered.

SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t4;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                 1                               32401

--2 BH: 1 FOR SEGMENT HEADER 1 FOR DATA BLOCK. what des pwc means?
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
  obj-flags: object_ckpt_list
  ckptq: [0x70fe5118,0x70beb868] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79efd] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 199,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
  obj-flags: object_ckpt_list
  ckptq: [0x70fe5118,0x83c41e90] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79fdb] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 184,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x841f5600,0x841f5600] lru: [0x707d8360,0x843ead88]
  obj-flags: object_ckpt_list
  ckptq: [0x707d91e8,0x70fee328] fileq: [0x843ef670,0x757d8f98] objq: [0x7a868ef0,0x7a868ef0]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21a2a.0] LSCN: [0x0.79fdb] HSCN: [0x0.79fdb] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

可以发现多了一个CLASS=1的DATA BLOCK HEADER，且仅有一个。

3、删表
SYS@PROD>drop table t4 purge;

Table dropped.

SYS@PROD>alter session set events 'immediate trace name buffers level 1';

Session altered.
-- see what happed below:
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
  obj-flags: object_ckpt_list
  ckptq: [0x70fe5118,0x70beb868] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79efd] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 199,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
  obj-flags: object_ckpt_list
  ckptq: [0x70fe5118,0x83c41e90] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79fdb] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 184,28
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x841f5600,0x841f5600] lru: [0x707d8360,0x843ead88]
  obj-flags: object_ckpt_list
  ckptq: [0x707d91e8,0x70fee328] fileq: [0x843ef670,0x757d8f98] objq: [0x7a868ef0,0x7a868ef0]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.21a2a.0] LSCN: [0x0.79fdb] HSCN: [0x0.79fdb] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x707e1358) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x704fa000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 200,19
  dbwrid: 0 obj: 13587 objn: 0 tsn: 0 afn: 1 hint: f
  hash: [0x70beb588,0x8403d820] lru: [0x60fe1310,0x843eb468]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,19
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x8403d820,0x707e1408] lru: [0x60ff3730,0x843ec208]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 190,19
  dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
  hash: [0x841f5600,0x841f5600] lru: [0x60fd8100,0x843ead98]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0

回复只看该作者道具举报

timothyzhn

5^#

发表于 2014-4-8 14:46:34

接上：
二、在SEGMENT SPACE MANAGEMENT=AUTO的EXAMPLE空间下建表：
1、建表zn.t8
SYS@PROD>create table zn.t8(x int, y int);

Table created.

SYS@PROD>select obj# from obj$ where name='T8';

   OBJ#
----------
   13591

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_7243.trc

vi /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7243.trc
找不到objn: 13591的BH
同样发现此时在DBA_SEGMENTS和ZN.USER_SEGMENTS里都找不到SEGMENT_NAME='T8'的记录

2、插入一条记录zn.t8
SYS@PROD>insert into zn.t8 values(1,2);

1 row created.

SYS@PROD>select segment_name,tablespace_name from dba_segments where segment_name='T8';

SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
T8       EXAMPLE

SYS@PROD>roll back;
Rollback complete.
SYS@PROD>select segment_name,tablespace_name from dba_segments where segment_name='T8';

SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
T8       EXAMPLE

此时再做dump查看并查看trace file
SYS@PROD>alter session set events 'immediate trace name buffers level 1';

Session altered.

发现如下出现8个BH，对应的OBJECT_ID=13591-----ZN.T8!重现了我的帖子问题。但是此时我就可以理解一些了。
自动段空间管理预分配了5个DATA BLOCK，再加上1个SEGMENT HEADER和CLASS=8/9的两个块（用处不明），合计8个。
即使我上面的插入没有提交，做了回滚，依然分配了5个DATA BLOCK。
最后重新做插入和提交，直接将ROW放在了229号块上。

BH (0x6fbe8428) file#: 4 rdba: 0x010000e1 (4/225) class: 9 ba: 0x6f9b8000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,28
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x84038500,0x84038500] lru: [0x6fbe8640,0x6fbe83e0]
  obj-flags: object_ckpt_list
  ckptq: [0x6fbe82f8,0x6fbe8a18] fileq: [0x83c3a2e0,0x83c3a2e0] objq: [0x7a86bca8,0x6fbe82d8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a87c] HSCN: [0x0.7a87c] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x753e7128) file#: 4 rdba: 0x010000e6 (4/230) class: 1 ba: 0x75198000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 196,28
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x840786e0,0x840786e0] lru: [0x743f6580,0x843ead88]
  obj-flags: object_ckpt_list
  ckptq: [0x843ec410,0x6fbf6bb8] fileq: [0x843ec490,0x843ec490] objq: [0x7a868770,0x7a868770]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x6fbdeaf8) file#: 4 rdba: 0x010000e3 (4/227) class: 1 ba: 0x6f8b6000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x840e00c0,0x840e00c0] lru: [0x6fbded10,0x6fbdeab0]
  obj-flags: object_ckpt_list
  ckptq: [0x6fbde9c8,0x703eedd8] fileq: [0x6fbde9d8,0x703eede8] objq: [0x6fbded38,0x6fbdead8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x6fbf2df8) file#: 4 rdba: 0x010000e0 (4/224) class: 8 ba: 0x6fad6000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 215,19
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x73ff9f78,0x84148720] lru: [0x6fbf3010,0x6fbf2db0]
  obj-flags: object_ckpt_list
  ckptq: [0x6fbf2cc8,0x6ffe74b8] fileq: [0x83c41f10,0x83c41f10] objq: [0x7a86a9e8,0x6fbf2ca8]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a87d] HSCN: [0x0.7a886] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x6fbf2b98) file#: 4 rdba: 0x010000e5 (4/229) class: 1 ba: 0x6fad2000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 215,19
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x84188900,0x84188900] lru: [0x6fbf2db0,0x843ec1f8]
  obj-flags: object_ckpt_list
  ckptq: [0x83c450d0,0x703dfa68] fileq: [0x83c45150,0x83c45150] objq: [0x6fbf2f08,0x7a86a9e8]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a892] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x6fbdec28) file#: 4 rdba: 0x010000e2 (4/226) class: 4 ba: 0x6f8b8000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x841f02e0,0x841f02e0] lru: [0x6fbdee40,0x6fbdebe0]
  obj-flags: object_ckpt_list
  ckptq: [0x843f7280,0x77fe8a18] fileq: [0x843f7300,0x77fe8a28] objq: [0x7a865b80,0x6fbdec08]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a87b] HSCN: [0x0.7a886] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x6fbde9c8) file#: 4 rdba: 0x010000e7 (4/231) class: 1 ba: 0x6f8b4000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x842304c0,0x842304c0] lru: [0x6fbdebe0,0x843eb458]
  obj-flags: object_ckpt_list
  ckptq: [0x843fa4c0,0x6fbdeaf8] fileq: [0x843fa540,0x6fbdeb08] objq: [0x6fbdec08,0x7a865b80]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x6fbe81c8) file#: 4 rdba: 0x010000e4 (4/228) class: 1 ba: 0x6f9b4000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,28
  dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
  hash: [0x84298b20,0x84298b20] lru: [0x6fbe83e0,0x843ebb28]
  obj-flags: object_ckpt_list
  ckptq: [0x83c37020,0x6ffd9908] fileq: [0x83c370a0,0x83c370a0] objq: [0x6fbe8538,0x7a86bca8]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0

SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from zn.t8;

no rows selected

SYS@PROD>insert into zn.t8 values(1,2);

1 row created.

SYS@PROD>commit;

Commit complete.

SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from zn.t8;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                 4                               229

回复只看该作者道具举报

timothyzhn

6^#

发表于 2014-4-8 14:48:03

不知上面我的理解和实验过程是否正确，请各位大侠指正。

回复只看该作者道具举报

Liu Maclean(刘相兵

7^#

发表于 2014-4-8 16:57:37

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

INSTANCE_NAME
----------------
Parnassus

SQL> alter system flush buffer_cache;

System altered.

SQL> create table maclean (t1 int) tablespace users;

Table created.

SQL> insert into maclean values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from maclean;

   FILE#    BLOCK#
---------- ----------
      4    136159

SQL> select obj#,dataobj# from obj$ where name='MACLEAN';

   OBJ# DATAOBJ#
---------- ----------
   79311    79311

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump buffers 1;
oradebug tracefile_name

BH (0x85ff1c18) file#: 4 rdba: 0x010213dc (4/136156) class: 1 ba: 0x85e82000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80f6f9e8,0xfe5ade38] lru: [0x85ff1e30,0x85ff1bd0]
  obj-flags: object_ckpt_list
  ckptq: [0x85ff1ae8,0x85ff1d48] fileq: [0x85ff1af8,0xfd0e26c0] objq: [0x85ff2dc8,0x85ff1bf8] objaq: [0x85ff2dd8,0x85ff1c08]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7998.0] LSCN: [0x0.17b95e] HSCN: [0x0.17b95e] HSUB: [1]


  BH (0x85ff2cb8) file#: 4 rdba: 0x010213d9 (4/136153) class: 9 ba: 0x85e9e000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80fbdb28,0xfe74c5b8] lru: [0x85ff2ed0,0x85ff2c70]
  obj-flags: object_ckpt_list
  ckptq: [0x85ff2de8,0x85ff2f18] fileq: [0xfd0e5900,0x85ff2f28] objq: [0xf9a55878,0x85ff1d28] objaq: [0xf9a55858,0x85ff1d38]
  st: XCURRENT md: NULL fpin: 'ktspswh4: ktspfsbmb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7981.0] LSCN: [0x0.17b94d] HSCN: [0x0.17b94d] HSUB: [1]



  BH (0x85ff1ae8) file#: 4 rdba: 0x010213de (4/136158) class: 1 ba: 0x85e80000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80f6f8b8,0xfe84cd38] lru: [0x85ff1d00,0x85ff1970]
  obj-flags: object_ckpt_list
  ckptq: [0x85ff1888,0x85ff1c18] fileq: [0xfd0e26c0,0x85ff1c28] objq: [0x85ff1d28,0xf9a55878] objaq: [0x85ff1d38,0xf9a55858]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7998.0] LSCN: [0x0.17b95e] HSCN: [0x0.17b95e] HSUB: [1]



  BH (0x90fe9bd8) file#: 4 rdba: 0x010213db (4/136155) class: 1 ba: 0x90daa000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80fbd9f8,0xfe9ee6b8] lru: [0x90fe9df0,0x90fe9b90]
  obj-flags: object_ckpt_list
  ckptq: [0x90fe9aa8,0x90fea1c8] fileq: [0x90fe9ab8,0xfd100778] objq: [0x90feab28,0x90fe9bb8] objaq: [0x90feab38,0x90fe9bc8]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7998.0] LSCN: [0x0.17b95e] HSCN: [0x0.17b95e] HSUB: [1]


  BH (0x90feaa18) file#: 4 rdba: 0x010213d8 (4/136152) class: 8 ba: 0x90dc2000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80f6fc48,0xfeb8ce38] lru: [0x90fefe20,0x90fea8a0]
  obj-flags: object_ckpt_list
  ckptq: [0x90fe9e38,0x90fefd38] fileq: [0xfd0fd538,0x90fefd48] objq: [0x90fefe48,0x90fe9ce8] objaq: [0x90fefe58,0x90fe9cf8]
  st: XCURRENT md: NULL fpin: 'ktspfwh6: ktspffbmb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7981.0] LSCN: [0x0.17b94e] HSCN: [0x0.17b95e] HSUB: [1]


  BH (0x90fe9aa8) file#: 4 rdba: 0x010213dd (4/136157) class: 1 ba: 0x90da8000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80fbd8c8,0xfec8d5b8] lru: [0x90fe9cc0,0x90fe90e0]
  obj-flags: object_ckpt_list
  ckptq: [0x90fe8ff8,0x90fe9bd8] fileq: [0x90fe9008,0x90fe9be8] objq: [0x90fe9ce8,0x90fe9108] objaq: [0x90fe9cf8,0x90fe9118]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7998.0] LSCN: [0x0.17b95e] HSCN: [0x0.17b95e] HSUB: [1]



  BH (0x90fefd38) file#: 4 rdba: 0x010213da (4/136154) class: 4 ba: 0x90e4e000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x80f6fb18,0xfee2bd38] lru: [0x90ff0080,0x90feab00]
  obj-flags: object_ckpt_list
  ckptq: [0x90feaa18,0x90ff00c8] fileq: [0x90feaa28,0x90ff00d8] objq: [0xf9a6e6a0,0x90feab28] objaq: [0xf9a6e680,0x90feab38]
  st: XCURRENT md: NULL fpin: 'ktswh03: ktscts' tch: 3
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7981.0] LSCN: [0x0.17b94c] HSCN: [0x0.17b95e] HSUB: [1]

  BH (0x90fe8ff8) file#: 4 rdba: 0x010213df (4/136159) class: 1 ba: 0x90d96000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 0 obj: 79311 objn: 79311 tsn: 4 afn: 4 hint: f
  hash: [0x84fe9db8,0xfef2c4b8] lru: [0x90fe9b90,0x90fe8fb0]
  obj-flags: object_ckpt_list
  ckptq: [0x90fe8ec8,0x90fe9aa8] fileq: [0xfd100778,0x90fe9ab8] objq: [0x90fe9bb8,0xf9a6e6a0] objaq: [0x90fe9bc8,0xf9a6e680]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0x44.7998.0] LSCN: [0x0.17b95e] HSCN: [0x0.17b95f] HSUB: [1]



  CLASS    NUMBER See Note 33434.1
            1,'data block',
            2,'sort block',
            3,'save undo block',
            4,'segment header',
            5,'save undo header',
            6,'free list',
            7,'extent map',
            8,'1st level bmb',
            9,'2nd level bmb',
            10,'3rd level bmb',
            11,'bitmap block',
            12,'bitmap index block',
            13,'file header block',
            14,'unused',
            15,'system undo header',
            16,'system undo block',
            17,'undo header',
            18,'undo block'             -- since 10g


  class 8 1st level bmb : 136152
  class 9 2nd level bmb: 136153
  class 4 segment header: 136154
  class 1 data block: 136155 136156 136157 136158 136159


  这是你看到的8个块



Low HighWater Mark :
   Highwater::  0x010213e0  ext#: 0    blk#: 8    ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 5
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x010213d8
  Level 1 BMB for Low HWM block: 0x010213d8
  --------------------------------------------------------
  Segment Type: 1 nl2: 1    blksz: 8192 fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x010213d9
  Last Level 1 BMB:  0x010213d8
  Last Level II BMB:  0x010213d9
  Last Level III BMB:  0x00000000
   Map Header:: next  0x00000000  #extents: 1 obj#: 79311  flag: 0x10000000
  Inc # 0
  Extent Map


  Highwater::  0x010213e0 ==》 4/ 136160
  L2 Hint for inserts:  0x010213d9  ==> 4/136153  ==>2nd level bmb

回复只看该作者道具举报

timothyzhn

8^#

发表于 2014-4-8 19:58:41

感谢Maclean的指点，虽然Low HighWater Mark下面一段因还没学到过看不懂，但感觉有了方向了。另外，请教一点，像BUFFER CACHE、BH、BLOCK这几块知识，有必要把dump里的内容每个关键词、每次dump出来的变化都搞懂搞透吗？对于除了学院派以外的帮助会有哪些？
如果有必要，从方法上来说，对于我这种刚起步的，是不是没必要立即弄得很清楚，随着学习的进步，再不断地去深入学习会更好？

回复只看该作者道具举报