ITPub博客

首页 > 数据库 > Oracle > 学习BUFFER CACHE的一点问题和实验理解过程二

学习BUFFER CACHE的一点问题和实验理解过程二

原创 Oracle 作者:nathanzhn 时间:2014-04-08 15:19:57 0 删除 编辑

======================================================================================================================================================
第二天,重新总结了一下,怀疑跟段空间管理方式有关。随做如下实验:
 一、在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
二、在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

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

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

请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    262732