ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ASSM三级位图结构与高水位的探究（上）

ASSM三级位图结构与高水位的探究（上）

原创 Oracle 作者：liupzmin 时间：2015-09-25 16:14:04 0 删除 编辑

    ASSM三级位图结构与高水位的探究
    Oracle9i在本地表空管理(LMT)的基础上，对段空间管理也引入了位图管理（Segment Space Management Auto）来取代原来的freelist管理方式（Segment Space Management Manual）。
但是默认system和undo表空间仍然是MSSM的管理方式，本文主要探究ASSM管理方式下，段的三级位图结构和高水位推进的关系 先来看
ASSM管理方式下的三级位图结构图：

注：此图来源于网络
    一个段被创建之后，段头其实是一个L3块，在我的试验中，一个1M固定区大小，block为8K的表空间，创建的第一个段第0个区，前两个块是L1（128,129号块），第三个是L2（130号块），第四个是L3（131号块），前128个块是数据文件头，本文不讨论。
    当数据被插入的时候，oracle根据连接进来的session，做hash运算，随机选择一个L3（如果有多个的话），再随机选择一个L2，接下来在该L2下随机选择一个L1，再从L1中管理的block里面随机选择一个块将数据插入，不同的session经过hash之后，最后落到block时已经是很分散的了，不会产生很多和会话同时往一个block中插数据申请独占buffer pin，而造成大量buffer busy waits的情况，这就是ASSM号称支持大并发插入的原理所在，但是事实上真的如此么？我们来一探究竟！

    实验环境：
    OS:REDHAT6.5 X64
    DB VERSION:11.2.0.4
    首先，创建一个1M区大小的表空间，并在上面创建一张表，此表创建了4个空间很大的字段，目的是要一行占满一个block，便于观察结果
    

  1. SQL> select file#,NAME,BYTES/1024/1204 from v$datafile;

  3.      FILE# NAME BYTES/1024/1204
  4. ---------- -------------------------------------------------------------------------------- ---------------
  5.          1 +DATA/min/datafile/system.256.854775095 637.873754
  6.          2 +DATA/min/datafile/sysaux.257.854775097 484.784053
  7.          3 +DATA/min/datafile/undotbs1.258.854775097 187.109635
  8.          4 +DATA/min/datafile/users.259.854775097 103.122924
  9.          
  10. SQL> create tablespace lp datafile '+DATA/min/datafile/lp.dbf' size 2048M uniform size 1m;

  12. Tablespace created.

  14. SQL> select file#,NAME,BYTES/1024/1204 from v$datafile;

  16.      FILE# NAME BYTES/1024/1204
  17. ---------- -------------------------------------------------------------------------------- ---------------
  18.          1 +DATA/min/datafile/system.256.854775095 637.873754
  19.          2 +DATA/min/datafile/sysaux.257.854775097 484.784053
  20.          3 +DATA/min/datafile/undotbs1.258.854775097 187.109635
  21.          4 +DATA/min/datafile/users.259.854775097 103.122924
  22.          5 +DATA/min/datafile/lp.dbf 1741.8206
  23.          
  24. SQL> create table lp (id number,des1 char(2000),des2 char(2000),des3 char(2000),des4 char(500)) tablespace lp;

  26. Table created.
    观察新建的表所占区

  1. SQL> col SEGMENT_NAME for a30
  2. SQL> col des1 for a1
  3. SQL> col des2 for a1
  4. SQL> col des3 for a1
  5. SQL> col des4 for a1
  6. SQL> set line 200
  7. SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID, BLOCKS from dba_extents where segment_name='LP';

  9. SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
  10. ------------------------------ ---------- ---------- ---------- ----------
  11. LP 0 5 128 128
    此表是5号文件，拥有1个区，block从128号开始，接下来我们插入一条数据，并dump出段头进行观察

  1. SQL> insert into lp values(1,'a','a','a','a');

  3. 1 row created.

  5. SQL> commit;

  7. Commit complete.

  9. SQL> select segment_name ,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='LP';

  11. SEGMENT_NAME HEADER_FILE HEADER_BLOCK
  12. ------------------------------ ----------- ------------
  13. LP 5 131


  16. SQL> alter system checkpoint;

  18. System altered.
LP的段头是5号文件，131号块，进行dump

  1. SQL> alter system dump datafile 5 block 131;

  3. SQL> col value for a65
  4. SQL> select * from v$diag_info;

  6.    INST_ID NAME VALUE
  7. ---------- -------------------------------------------------------------------------------- -----------------------------------------------------------------
  8.          1 Diag Enabled TRUE
  9.          1 ADR Base /u01/app/oracle
  10.          1 ADR Home /u01/app/oracle/diag/rdbms/min/min
  11.          1 Diag Trace /u01/app/oracle/diag/rdbms/min/min/trace
  12.          1 Diag Alert /u01/app/oracle/diag/rdbms/min/min/alert
  13.          1 Diag Incident /u01/app/oracle/diag/rdbms/min/min/incident
  14.          1 Diag Cdump /u01/app/oracle/diag/rdbms/min/min/cdump
  15.          1 Health Monitor /u01/app/oracle/diag/rdbms/min/min/hm
  16.          1 Default Trace File /u01/app/oracle/diag/rdbms/min/min/trace/min_ora_2835.trc
  17.          1 Active Problem Count 0
  18.          1 Active Incident Count 0

  20. 11 rows selected.

观察trc文件min_ora_2835.trc

  1. Block dump from disk:
  2. buffer tsn: 8 rdba: 0x01400083 (5/131)
  3. scn: 0x0000.00140d51 seq: 0x01 flg: 0x04 tail: 0x0d512301
  4. frmt: 0x02 chkval: 0xfd8a type: 0x23=PAGETABLE SEGMENT HEADER
  5. Hex dump of block: st=0, typ_found=1
  9. Extent Control Header
  10. -----------------------------------------------------------------
  11. Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
  12. last map 0x00000000 #maps: 0 offset: 2716
  13. Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
  14. #blocks in seg. hdr's freelists: 0
  15. #blocks below: 60
  16. mapblk 0x00000000 offset: 0
  17. Unlocked
  18. --------------------------------------------------------
  19. Low HighWater Mark :
  20. Highwater:: 0x01400084 ext#: 0 blk#: 4 ext size: 128
  21. #blocks in seg. hdr's freelists: 0
  22. #blocks below: 0
  23. mapblk 0x00000000 offset: 0
  24. Level 1 BMB for High HWM block: 0x01400080
  25. Level 1 BMB for Low HWM block: 0x01400080
  26. --------------------------------------------------------
  27. Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
  28. L2 Array start offset: 0x00001434
  29. First Level 3 BMB: 0x00000000
  30. L2 Hint for inserts: 0x01400082
  31. Last Level 1 BMB: 0x01400081
  32. Last Level II BMB: 0x01400082
  33. Last Level III BMB: 0x00000000
  34. Map Header:: next 0x00000000 #extents: 1 obj#: 87596 flag: 0x10000000
  35. Inc # 0
  36. Extent Map
  37. -----------------------------------------------------------------
  38. 0x01400080 length: 128
  40. Auxillary Map
  41. --------------------------------------------------------
  42. Extent 0 : L1 dba: 0x01400080 Data dba: 0x01400084
  43. --------------------------------------------------------
  45. Second Level Bitmap block DBAs
  46. --------------------------------------------------------
  47. DBA 1: 0x01400082
  49. End dump data blocks tsn: 8 file#: 5 minblk 131 maxblk 131

很容易发现这是一个PAGETABLE SEGMENT HEADER块，Extent Map中只有一个区，另外在尾部有一个指向二级位图块的地址0x01400082，这是数据块地址的二进制用十六进制来表示，我们来将其转化成直观一点的信息

  1. SQL> select dbms_utility.data_block_address_file(to_number('01400082', 'xxxxxxxx')) file#,
  2.   2 dbms_utility.data_block_address_block(to_number('01400082', 'xxxxxxxx')) block#
  3.   3 from dual;

  5.      FILE# BLOCK#
  6. ---------- ----------
  7.          5 130

依样将5号文件130号块的内容dump出来

  1. Block dump from disk:
  2. buffer tsn: 8 rdba: 0x01400082 (5/130)
  3. scn: 0x0000.00140a45 seq: 0x02 flg: 0x04 tail: 0x0a452102
  4. frmt: 0x02 chkval: 0xd119 type: 0x21=SECOND LEVEL BITMAP BLOCK
  5. Hex dump of block: st=0, typ_found=1
  8. Dump of Second Level Bitmap Block
  9. number: 2 nfree: 2 ffree: 0 pdba: 0x01400083
  10. Inc #: 0 Objd: 87596
  11. opcode:0
  12. xid:
  13. L1 Ranges :
  14. --------------------------------------------------------
  15. 0x01400080 Free: 5 Inst: 1
  16. 0x01400081 Free: 5 Inst: 1
  18. --------------------------------------------------------
  19. End dump data blocks tsn: 8 file#: 5 minblk 130 maxblk 130

可以发现这是一个二级位图块，并从其中找到了2个L1块的地址，我们来看第一个L1

  1. Block dump from disk:
  2. buffer tsn: 8 rdba: 0x01400080 (5/128)
  3. scn: 0x0000.00140d51 seq: 0x03 flg: 0x04 tail: 0x0d512003
  4. frmt: 0x02 chkval: 0xe697 type: 0x20=FIRST LEVEL BITMAP BLOCK
  5. Hex dump of block: st=0, typ_found=1
  7. DBA Ranges :
  8. --------------------------------------------------------
  9. 0x01400080 Length: 64 Offset: 0
  11. 0:Metadata 1:Metadata 2:Metadata 3:Metadata
  12. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  13. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  14. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  15. 16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
  16. 20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
  17. 24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
  18. 28:75-100% free 29:75-100% free 30:75-100% free 31:0-25% free
  19. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  20. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  21. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  22. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  23. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  24. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  25. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  26. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  27. --------------------------------------------------------
  28. End dump data blocks tsn: 8 file#: 5 minblk 128 maxblk 128

无疑这是一个L1块，里面挂了64个数据块，因为我之前插入了一行数据，oracle已经格式化了一部分，并向其中一个块插入了数据，但是没达到我们的效果，被插入的块还显示0-25%的空闲，我们要的是full的状态，说明我们构造的数据不够大，没关系，我们修改一下pctfree就可以了，另外我们可以发现这个区是1M，有2个L1，每个L1里有64个数据块，接下来修改pctfree

  1. SQL> alter table lp pctfree 24;

  3. Table altered.

  5. SQL> select TABLE_NAME,PCT_FREE from dba_tables where table_name='LP';

  7. TABLE_NAME PCT_FREE
  8. ------------------------------ ----------
  9. LP 24

  11. SQL> insert into lp values(2,'a','a','a','a');

  13. 1 row created.

  15. SQL> commit;

  17. Commit complete.
 --手动触发检查点将内存中的块刷新到磁盘，本文每次dump之前都会做这个操作，以保证磁盘和内存的内容一致
  1. SQL> alter system checkpoint;

  3. System altered.

我们再看128号块

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x01400080 Length: 64 Offset: 0
  5. 0:Metadata 1:Metadata 2:Metadata 3:Metadata
  6. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  7. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  8. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  9. 16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
  10. 20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
  11. 24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
  12. 28:75-100% free 29:75-100% free 30:75-100% free 31:0-25% free
  13. 32:75-100% free 33:75-100% free 34:75-100% free 35:FULL
  14. 36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
  15. 40:75-100% free 41:75-100% free 42:75-100% free 43:75-100% free
  16. 44:75-100% free 45:75-100% free 46:75-100% free 47:75-100% free
  17. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  18. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  19. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  20. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  21. --------------------------------------------------------
  22. End dump data blocks tsn: 8 file#: 5 minblk 128 maxblk 128
已经使我们想的结果了，到这里我们再来看一下另外一个L1吧，dump129号块

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x014000c0 Length: 64 Offset: 0
  5. 0:unformatted 1:unformatted 2:unformatted 3:unformatted
  6. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  7. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  8. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  9. 16:unformatted 17:unformatted 18:unformatted 19:unformatted
  10. 20:unformatted 21:unformatted 22:unformatted 23:unformatted
  11. 24:unformatted 25:unformatted 26:unformatted 27:unformatted
  12. 28:unformatted 29:unformatted 30:unformatted 31:unformatted
  13. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  14. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  15. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  16. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  17. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  18. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  19. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  20. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  21. --------------------------------------------------------
  22. End dump data blocks tsn: 8 file#: 5 minblk 129 maxblk 129

全是未格式化的块，试一试并发插入，这里不模拟真正的并发了，只是开不同的session来进行插入


  1. SQL> insert into lp values(3,'a','a','a','a');

  3. 1 row created.

  5. SQL> insert into lp values(4,'a','a','a','a');

  7. 1 row created.

  9. SQL> insert into lp values(5,'a','a','a','a');

  11. 1 row created.

  13. SQL> insert into lp values(6,'a','a','a','a');

  15. 1 row created.

  17. SQL> insert into lp values(7,'a','a','a','a');

  19. 1 row created.

  21. 单独session5条

  23. SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

  25. DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  26. ------------------------------------ ------------------------------------
  27.                                    5 158
  28.                                    5 159
  29.                                    5 163
  30.                                    5 167
  31.                                    5 171
  32.                                    5 172
  33.                                    5 174
  34.                                    5 175
  35.                                    5 179
  36.                                    5 183
  37.                                    5 187
  38.                                    5 191

  40. 12 rows selected.

一个单独的session里插入了5条，另外5个单独的session里各插一条，发现规律了么，插入的块只在132和192之间，我们分别看看两个L1

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x01400080 Length: 64 Offset: 0
  5. 0:Metadata 1:Metadata 2:Metadata 3:Metadata
  6. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  7. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  8. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  9. 16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
  10. 20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
  11. 24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
  12. 28:75-100% free 29:75-100% free 30:FULL 31:0-25% free
  13. 32:75-100% free 33:75-100% free 34:75-100% free 35:FULL
  14. 36:75-100% free 37:75-100% free 38:75-100% free 39:FULL
  15. 40:75-100% free 41:75-100% free 42:75-100% free 43:FULL
  16. 44:FULL 45:75-100% free 46:FULL 47:FULL
  17. 48:75-100% free 49:75-100% free 50:75-100% free 51:FULL
  18. 52:75-100% free 53:75-100% free 54:75-100% free 55:FULL
  19. 56:75-100% free 57:75-100% free 58:75-100% free 59:FULL
  20. 60:75-100% free 61:75-100% free 62:75-100% free 63:FULL
  21. --------------------------------------------------------
  22. End dump data blocks tsn: 8 file#: 5 minblk 128 maxblk 128

  25. 看一下第二个L1
  27. DBA Ranges :
  28. --------------------------------------------------------
  29. 0x014000c0 Length: 64 Offset: 0
  31. 0:unformatted 1:unformatted 2:unformatted 3:unformatted
  32. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  33. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  34. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  35. 16:unformatted 17:unformatted 18:unformatted 19:unformatted
  36. 20:unformatted 21:unformatted 22:unformatted 23:unformatted
  37. 24:unformatted 25:unformatted 26:unformatted 27:unformatted
  38. 28:unformatted 29:unformatted 30:unformatted 31:unformatted
  39. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  40. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  41. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  42. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  43. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  44. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  45. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  46. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  47. --------------------------------------------------------
  48. End dump data blocks tsn: 8 file#: 5 minblk 129 maxblk 129


这说明只在第一个L1里面随机，那为什么L2下挂了2个L1，我插入了10条却没有一条随机到第二个L1块呢？答案是高水位，这是vage大师已经论证过的了，偶在这里仅为见证一下~~，好了，还记得L3里的高水位信息么？

  1. Extent Control Header
  2. -----------------------------------------------------------------
  3. Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
  4. last map 0x00000000 #maps: 0 offset: 2716
  5. Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
  6. #blocks in seg. hdr's freelists: 0
  7. #blocks below: 60
  8. mapblk 0x00000000 offset: 0
  9. Unlocked

Highwater::  0x014000c0是那个块呢？

  1. SQL> select dbms_utility.data_block_address_file(to_number('014000c0', 'xxxxxxxx')) file#,
  2.   2 dbms_utility.data_block_address_block(to_number('014000c0', 'xxxxxxxx')) block#
  3.   3 from dual;

  5.      FILE# BLOCK#
  6. ---------- ----------
  7.          5 192
这是第二个L1管理的第一个块，可见数据的插入只在高水位之下进行（直接路径插入除外），此时的状态如下：


那么L1里面只会有64个块么？高水位永远都会指向L1的最后一个块么？我们继续剖析！

首先给LP表多分配一些区

  1. SQL> alter table lp allocate extent(size 100m);

  3. Table altered.

  5. SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID, BLOCKS from dba_extents where segment_name='LP';

  7. SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
  8. ------------------------------ ---------- ---------- ---------- ----------
  9. LP 0 5 128 128
  10. LP 1 5 256 128
  11. LP 2 5 384 128
  12. LP 3 5 512 128
  13. LP 4 5 640 128
  14. LP 5 5 768 128
  15. LP 6 5 896 128
  16. LP 7 5 1024 128
  17. LP 8 5 1152 128
  18. LP 9 5 1280 128
  19. LP 10 5 1408 128
  20. LP 11 5 1536 128
  21. LP 12 5 1664 128
  22. LP 13 5 1792 128
  23. LP 14 5 1920 128
  24. LP 15 5 2048 128
  25. LP 16 5 2176 128
  26. LP 17 5 2304 128
  27. LP 18 5 2432 128
  28. LP 19 5 2560 128
  29. LP 20 5 2688 128
  30. LP 21 5 2816 128
  31. LP 22 5 2944 128
  32. LP 23 5 3072 128
  33. LP 24 5 3200 128
  34. LP 25 5 3328 128
  35. LP 26 5 3456 128
  36. LP 27 5 3584 128
  37. LP 28 5 3712 128
  38. LP 29 5 3840 128
  39. LP 30 5 3968 128
  40. LP 31 5 4096 128
  41. LP 32 5 4224 128
  42. LP 33 5 4352 128
  43. LP 34 5 4480 128
  44. LP 35 5 4608 128
  45. LP 36 5 4736 128
  46. LP 37 5 4864 128
  47. LP 38 5 4992 128
  48. LP 39 5 5120 128
  49. LP 40 5 5248 128
  50. LP 41 5 5376 128
  51. LP 42 5 5504 128
  52. LP 43 5 5632 128
  53. LP 44 5 5760 128
  54. LP 45 5 5888 128
  55. LP 46 5 6016 128
  56. LP 47 5 6144 128
  57. LP 48 5 6272 128
  58. LP 49 5 6400 128
  59. LP 50 5 6528 128
  60. LP 51 5 6656 128
  61. LP 52 5 6784 128
  62. LP 53 5 6912 128
  63. LP 54 5 7040 128
  64. LP 55 5 7168 128
  65. LP 56 5 7296 128
  66. LP 57 5 7424 128
  67. LP 58 5 7552 128
  68. LP 59 5 7680 128
  69. LP 60 5 7808 128
  70. LP 61 5 7936 128
  71. LP 62 5 8064 128
  72. LP 63 5 8192 128
  73. LP 64 5 8320 128
  74. LP 65 5 8448 128
  75. LP 66 5 8576 128
  76. LP 67 5 8704 128
  77. LP 68 5 8832 128
  78. LP 69 5 8960 128
  79. LP 70 5 9088 128
  80. LP 71 5 9216 128
  81. LP 72 5 9344 128
  82. LP 73 5 9472 128
  83. LP 74 5 9600 128
  84. LP 75 5 9728 128
  85. LP 76 5 9856 128
  86. LP 77 5 9984 128
  87. LP 78 5 10112 128
  88. LP 79 5 10240 128
  89. LP 80 5 10368 128
  90. LP 81 5 10496 128
  91. LP 82 5 10624 128
  92. LP 83 5 10752 128
  93. LP 84 5 10880 128
  94. LP 85 5 11008 128
  95. LP 86 5 11136 128
  96. LP 87 5 11264 128
  97. LP 88 5 11392 128
  98. LP 89 5 11520 128
  99. LP 90 5 11648 128
  100. LP 91 5 11776 128
  101. LP 92 5 11904 128
  102. LP 93 5 12032 128
  103. LP 94 5 12160 128
  104. LP 95 5 12288 128
  105. LP 96 5 12416 128
  106. LP 97 5 12544 128
  107. LP 98 5 12672 128
  108. LP 99 5 12800 128
  109. LP 100 5 12928 128

我分配了100个区，加上原来的一共101个，现在我们看看L3段头里面的内容

  1. Extent Control Header
  2. -----------------------------------------------------------------
  3. Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
  4. last map 0x00000000 #maps: 0 offset: 2716
  5. Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
  6. #blocks in seg. hdr's freelists: 0
  7. #blocks below: 60
  8. mapblk 0x00000000 offset: 0
  11. Extent Map
  12. -----------------------------------------------------------------
  13. 0x01400080 length: 128
  14. 0x01400100 length: 128
  15. 0x01400180 length: 128
  16. 0x01400200 length: 128
  17. 0x01400280 length: 128
  18. 0x01400300 length: 128
  19. 0x01400380 length: 128
  20. 0x01400400 length: 128
  21. 0x01400480 length: 128
  22. 0x01400500 length: 128
  23. 0x01400580 length: 128
  24. 0x01400600 length: 128
  25. 0x01400680 length: 128
  26. 0x01400700 length: 128
  27. 0x01400780 length: 128
  28. 0x01400800 length: 128
  29. 0x01400880 length: 128
  30. 0x01400900 length: 128
  31. 0x01400980 length: 128
  32. 0x01400a00 length: 128
  33. 0x01400a80 length: 128
  34. 0x01400b00 length: 128
  35. 0x01400b80 length: 128
  36. 0x01400c00 length: 128
  37. 0x01400c80 length: 128
  38. 0x01400d00 length: 128
  39. 0x01400d80 length: 128
  40. 0x01400e00 length: 128
  41. 0x01400e80 length: 128
  42. 0x01400f00 length: 128
  43. 0x01400f80 length: 128
  44. 0x01401000 length: 128
  45. 0x01401080 length: 128
  46. 0x01401100 length: 128
  47. 0x01401180 length: 128
  48. 0x01401200 length: 128
  49. 0x01401280 length: 128
  50. 0x01401300 length: 128
  51. 0x01401380 length: 128
  52. 0x01401400 length: 128
  53. 0x01401480 length: 128
  54. 0x01401500 length: 128
  55. 0x01401580 length: 128
  56. 0x01401600 length: 128
  57. 0x01401680 length: 128
  58. 0x01401700 length: 128
  59. 0x01401780 length: 128
  60. 0x01401800 length: 128
  61. 0x01401880 length: 128
  62. 0x01401900 length: 128
  63. 0x01401980 length: 128
  64. 0x01401a00 length: 128
  65. 0x01401a80 length: 128
  66. 0x01401b00 length: 128
  67. 0x01401b80 length: 128
  68. 0x01401c00 length: 128
  69. 0x01401c80 length: 128
  70. 0x01401d00 length: 128
  71. 0x01401d80 length: 128
  72. 0x01401e00 length: 128
  73. 0x01401e80 length: 128
  74. 0x01401f00 length: 128
  75. 0x01401f80 length: 128
  76. 0x01402000 length: 128
  77. 0x01402080 length: 128
  78. 0x01402100 length: 128
  79. 0x01402180 length: 128
  80. 0x01402200 length: 128
  81. 0x01402280 length: 128
  82. 0x01402300 length: 128
  83. 0x01402380 length: 128
  84. 0x01402400 length: 128
  85. 0x01402480 length: 128
  86. 0x01402500 length: 128
  87. 0x01402580 length: 128
  88. 0x01402600 length: 128
  89. 0x01402680 length: 128
  90. 0x01402700 length: 128
  91. 0x01402780 length: 128
  92. 0x01402800 length: 128
  93. 0x01402880 length: 128
  94. 0x01402900 length: 128
  95. 0x01402980 length: 128
  96. 0x01402a00 length: 128
  97. 0x01402a80 length: 128
  98. 0x01402b00 length: 128
  99. 0x01402b80 length: 128
  100. 0x01402c00 length: 128
  101. 0x01402c80 length: 128
  102. 0x01402d00 length: 128
  103. 0x01402d80 length: 128
  104. 0x01402e00 length: 128
  105. 0x01402e80 length: 128
  106. 0x01402f00 length: 128
  107. 0x01402f80 length: 128
  108. 0x01403000 length: 128
  109. 0x01403080 length: 128
  110. 0x01403100 length: 128
  111. 0x01403180 length: 128
  112. 0x01403200 length: 128
  113. 0x01403280 length: 128
  115. Auxillary Map
  116. --------------------------------------------------------
  117. Extent 0 : L1 dba: 0x01400080 Data dba: 0x01400084
  118. Extent 1 : L1 dba: 0x01400100 Data dba: 0x01400102
  119. Extent 2 : L1 dba: 0x01400180 Data dba: 0x01400182
  120. Extent 3 : L1 dba: 0x01400200 Data dba: 0x01400202
  121. Extent 4 : L1 dba: 0x01400280 Data dba: 0x01400282
  122. Extent 5 : L1 dba: 0x01400300 Data dba: 0x01400302
  123. Extent 6 : L1 dba: 0x01400380 Data dba: 0x01400382
  124. Extent 7 : L1 dba: 0x01400400 Data dba: 0x01400402
  125. Extent 8 : L1 dba: 0x01400480 Data dba: 0x01400482
  126. Extent 9 : L1 dba: 0x01400500 Data dba: 0x01400502
  127. Extent 10 : L1 dba: 0x01400580 Data dba: 0x01400582
  128. Extent 11 : L1 dba: 0x01400600 Data dba: 0x01400602
  129. Extent 12 : L1 dba: 0x01400680 Data dba: 0x01400682
  130. Extent 13 : L1 dba: 0x01400700 Data dba: 0x01400702
  131. Extent 14 : L1 dba: 0x01400780 Data dba: 0x01400782
  132. Extent 15 : L1 dba: 0x01400800 Data dba: 0x01400802
  133. Extent 16 : L1 dba: 0x01400880 Data dba: 0x01400882
  134. Extent 17 : L1 dba: 0x01400900 Data dba: 0x01400902
  135. Extent 18 : L1 dba: 0x01400980 Data dba: 0x01400982
  136. Extent 19 : L1 dba: 0x01400a00 Data dba: 0x01400a02
  137. Extent 20 : L1 dba: 0x01400a80 Data dba: 0x01400a82
  138. Extent 21 : L1 dba: 0x01400b00 Data dba: 0x01400b02
  139. Extent 22 : L1 dba: 0x01400b80 Data dba: 0x01400b82
  140. Extent 23 : L1 dba: 0x01400c00 Data dba: 0x01400c02
  141. Extent 24 : L1 dba: 0x01400c80 Data dba: 0x01400c82
  142. Extent 25 : L1 dba: 0x01400d00 Data dba: 0x01400d02
  143. Extent 26 : L1 dba: 0x01400d80 Data dba: 0x01400d82
  144. Extent 27 : L1 dba: 0x01400e00 Data dba: 0x01400e02
  145. Extent 28 : L1 dba: 0x01400e80 Data dba: 0x01400e82
  146. Extent 29 : L1 dba: 0x01400f00 Data dba: 0x01400f02
  147. Extent 30 : L1 dba: 0x01400f80 Data dba: 0x01400f82
  148. Extent 31 : L1 dba: 0x01401000 Data dba: 0x01401002
  149. Extent 32 : L1 dba: 0x01401080 Data dba: 0x01401082
  150. Extent 33 : L1 dba: 0x01401100 Data dba: 0x01401102
  151. Extent 34 : L1 dba: 0x01401180 Data dba: 0x01401182
  152. Extent 35 : L1 dba: 0x01401200 Data dba: 0x01401202
  153. Extent 36 : L1 dba: 0x01401280 Data dba: 0x01401282
  154. Extent 37 : L1 dba: 0x01401300 Data dba: 0x01401302
  155. Extent 38 : L1 dba: 0x01401380 Data dba: 0x01401382
  156. Extent 39 : L1 dba: 0x01401400 Data dba: 0x01401402
  157. Extent 40 : L1 dba: 0x01401480 Data dba: 0x01401482
  158. Extent 41 : L1 dba: 0x01401500 Data dba: 0x01401502
  159. Extent 42 : L1 dba: 0x01401580 Data dba: 0x01401582
  160. Extent 43 : L1 dba: 0x01401600 Data dba: 0x01401602
  161. Extent 44 : L1 dba: 0x01401680 Data dba: 0x01401682
  162. Extent 45 : L1 dba: 0x01401700 Data dba: 0x01401702
  163. Extent 46 : L1 dba: 0x01401780 Data dba: 0x01401782
  164. Extent 47 : L1 dba: 0x01401800 Data dba: 0x01401802
  165. Extent 48 : L1 dba: 0x01401880 Data dba: 0x01401882
  166. Extent 49 : L1 dba: 0x01401900 Data dba: 0x01401902
  167. Extent 50 : L1 dba: 0x01401980 Data dba: 0x01401982
  168. Extent 51 : L1 dba: 0x01401a00 Data dba: 0x01401a02
  169. Extent 52 : L1 dba: 0x01401a80 Data dba: 0x01401a82
  170. Extent 53 : L1 dba: 0x01401b00 Data dba: 0x01401b02
  171. Extent 54 : L1 dba: 0x01401b80 Data dba: 0x01401b82
  172. Extent 55 : L1 dba: 0x01401c00 Data dba: 0x01401c02
  173. Extent 56 : L1 dba: 0x01401c80 Data dba: 0x01401c82
  174. Extent 57 : L1 dba: 0x01401d00 Data dba: 0x01401d02
  175. Extent 58 : L1 dba: 0x01401d80 Data dba: 0x01401d82
  176. Extent 59 : L1 dba: 0x01401e00 Data dba: 0x01401e02
  177. Extent 60 : L1 dba: 0x01401e80 Data dba: 0x01401e82
  178. Extent 61 : L1 dba: 0x01401f00 Data dba: 0x01401f02
  179. Extent 62 : L1 dba: 0x01401f80 Data dba: 0x01401f82
  180. Extent 63 : L1 dba: 0x01402000 Data dba: 0x01402001
  181. Extent 64 : L1 dba: 0x01402000 Data dba: 0x01402080
  182. Extent 65 : L1 dba: 0x01402100 Data dba: 0x01402101
  183. Extent 66 : L1 dba: 0x01402100 Data dba: 0x01402180
  184. Extent 67 : L1 dba: 0x01402200 Data dba: 0x01402201
  185. Extent 68 : L1 dba: 0x01402200 Data dba: 0x01402280
  186. Extent 69 : L1 dba: 0x01402300 Data dba: 0x01402301
  187. Extent 70 : L1 dba: 0x01402300 Data dba: 0x01402380
  188. Extent 71 : L1 dba: 0x01402400 Data dba: 0x01402401
  189. Extent 72 : L1 dba: 0x01402400 Data dba: 0x01402480
  190. Extent 73 : L1 dba: 0x01402500 Data dba: 0x01402501
  191. Extent 74 : L1 dba: 0x01402500 Data dba: 0x01402580
  192. Extent 75 : L1 dba: 0x01402600 Data dba: 0x01402601
  193. Extent 76 : L1 dba: 0x01402600 Data dba: 0x01402680
  194. Extent 77 : L1 dba: 0x01402700 Data dba: 0x01402701
  195. Extent 78 : L1 dba: 0x01402700 Data dba: 0x01402780
  196. Extent 79 : L1 dba: 0x01402800 Data dba: 0x01402801
  197. Extent 80 : L1 dba: 0x01402800 Data dba: 0x01402880
  198. Extent 81 : L1 dba: 0x01402900 Data dba: 0x01402901
  199. Extent 82 : L1 dba: 0x01402900 Data dba: 0x01402980
  200. Extent 83 : L1 dba: 0x01402a00 Data dba: 0x01402a01
  201. Extent 84 : L1 dba: 0x01402a00 Data dba: 0x01402a80
  202. Extent 85 : L1 dba: 0x01402b00 Data dba: 0x01402b01
  203. Extent 86 : L1 dba: 0x01402b00 Data dba: 0x01402b80
  204. Extent 87 : L1 dba: 0x01402c00 Data dba: 0x01402c01
  205. Extent 88 : L1 dba: 0x01402c00 Data dba: 0x01402c80
  206. Extent 89 : L1 dba: 0x01402d00 Data dba: 0x01402d01
  207. Extent 90 : L1 dba: 0x01402d00 Data dba: 0x01402d80
  208. Extent 91 : L1 dba: 0x01402e00 Data dba: 0x01402e01
  209. Extent 92 : L1 dba: 0x01402e00 Data dba: 0x01402e80
  210. Extent 93 : L1 dba: 0x01402f00 Data dba: 0x01402f01
  211. Extent 94 : L1 dba: 0x01402f00 Data dba: 0x01402f80
  212. Extent 95 : L1 dba: 0x01403000 Data dba: 0x01403001
  213. Extent 96 : L1 dba: 0x01403000 Data dba: 0x01403080
  214. Extent 97 : L1 dba: 0x01403100 Data dba: 0x01403101
  215. Extent 98 : L1 dba: 0x01403100 Data dba: 0x01403180
  216. Extent 99 : L1 dba: 0x01403200 Data dba: 0x01403201
  217. Extent 100 : L1 dba: 0x01403200 Data dba: 0x01403280
  218. --------------------------------------------------------
  220. Second Level Bitmap block DBAs
  221. --------------------------------------------------------
  222. DBA 1: 0x01400082
  224. End dump data blocks tsn: 8 file#: 5 minblk 131 maxblk 131

    这里我们主要看Auxillary Map，里面记录了每个区所属的L1，不难发现从第64个区开始，64、65两个区的L1相同，这说明什么，说明L1下面挂了整整两个区的数据块，验证一下，将0x01402000块dump出来之后的内容：

  1. SQL> select dbms_utility.data_block_address_file(20979712) Rfile#,dbms_utility.data_block_address_block(20979712) "Block#" from dual;
  3. RFILE# Block#
  4. ---------- ----------
  5. 5 8192
  7. DBA Ranges :
  8. --------------------------------------------------------
  9. 0x01402000 Length: 128 Offset: 0
  10. 0x01402080 Length: 128 Offset: 128
  12. 0:Metadata 1:unformatted 2:unformatted 3:unformatted
  13. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  14. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  15. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  16. 16:unformatted 17:unformatted 18:unformatted 19:unformatted
  17. 20:unformatted 21:unformatted 22:unformatted 23:unformatted
  18. 24:unformatted 25:unformatted 26:unformatted 27:unformatted
  19. 28:unformatted 29:unformatted 30:unformatted 31:unformatted
  20. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  21. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  22. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  23. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  24. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  25. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  26. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  27. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  28. 64:unformatted 65:unformatted 66:unformatted 67:unformatted
  29. 68:unformatted 69:unformatted 70:unformatted 71:unformatted
  30. 72:unformatted 73:unformatted 74:unformatted 75:unformatted
  31. 76:unformatted 77:unformatted 78:unformatted 79:unformatted
  32. 80:unformatted 81:unformatted 82:unformatted 83:unformatted
  33. 84:unformatted 85:unformatted 86:unformatted 87:unformatted
  34. 88:unformatted 89:unformatted 90:unformatted 91:unformatted
  35. 92:unformatted 93:unformatted 94:unformatted 95:unformatted
  36. 96:unformatted 97:unformatted 98:unformatted 99:unformatted
  37. 100:unformatted 101:unformatted 102:unformatted 103:unformatted
  38. 104:unformatted 105:unformatted 106:unformatted 107:unformatted
  39. 108:unformatted 109:unformatted 110:unformatted 111:unformatted
  40. 112:unformatted 113:unformatted 114:unformatted 115:unformatted
  41. 116:unformatted 117:unformatted 118:unformatted 119:unformatted
  42. 120:unformatted 121:unformatted 122:unformatted 123:unformatted
  43. 124:unformatted 125:unformatted 126:unformatted 127:unformatted
  44. 128:unformatted 129:unformatted 130:unformatted 131:unformatted
  45. 132:unformatted 133:unformatted 134:unformatted 135:unformatted
  46. 136:unformatted 137:unformatted 138:unformatted 139:unformatted
  47. 140:unformatted 141:unformatted 142:unformatted 143:unformatted
  48. 144:unformatted 145:unformatted 146:unformatted 147:unformatted
  49. 148:unformatted 149:unformatted 150:unformatted 151:unformatted
  50. 152:unformatted 153:unformatted 154:unformatted 155:unformatted
  51. 156:unformatted 157:unformatted 158:unformatted 159:unformatted
  52. 160:unformatted 161:unformatted 162:unformatted 163:unformatted
  53. 164:unformatted 165:unformatted 166:unformatted 167:unformatted
  54. 168:unformatted 169:unformatted 170:unformatted 171:unformatted
  55. 172:unformatted 173:unformatted 174:unformatted 175:unformatted
  56. 176:unformatted 177:unformatted 178:unformatted 179:unformatted
  57. 180:unformatted 181:unformatted 182:unformatted 183:unformatted
  58. 184:unformatted 185:unformatted 186:unformatted 187:unformatted
  59. 188:unformatted 189:unformatted 190:unformatted 191:unformatted
  60. 192:unformatted 193:unformatted 194:unformatted 195:unformatted
  61. 196:unformatted 197:unformatted 198:unformatted 199:unformatted
  62. 200:unformatted 201:unformatted 202:unformatted 203:unformatted
  63. 204:unformatted 205:unformatted 206:unformatted 207:unformatted
  64. 208:unformatted 209:unformatted 210:unformatted 211:unformatted
  65. 212:unformatted 213:unformatted 214:unformatted 215:unformatted
  66. 216:unformatted 217:unformatted 218:unformatted 219:unformatted
  67. 220:unformatted 221:unformatted 222:unformatted 223:unformatted
  68. 224:unformatted 225:unformatted 226:unformatted 227:unformatted
  69. 228:unformatted 229:unformatted 230:unformatted 231:unformatted
  70. 232:unformatted 233:unformatted 234:unformatted 235:unformatted
  71. 236:unformatted 237:unformatted 238:unformatted 239:unformatted
  72. 240:unformatted 241:unformatted 242:unformatted 243:unformatted
  73. 244:unformatted 245:unformatted 246:unformatted 247:unformatted
  74. 248:unformatted 249:unformatted 250:unformatted 251:unformatted
  75. 252:unformatted 253:unformatted 254:unformatted 255:unformatted
  76. --------------------------------------------------------
  77. End dump data blocks tsn: 8 file#: 5 minblk 8192 maxblk 8192

    256个块，2M 2个区，可见L1中块的数量会随着段大小的改变而做调整的，可以增大到1024个甚至更多，我在这里不去再做验证，将重点放在高水位上，根据我们之前观察到的现象，高水位在第二个L1的地一个块，也可说第一个L1的末端，那么现在的L1里有256个块，高水位是不是也会推进到这个L1的末端呢？很容易进行验证，将数据插满到这个L1的前几个块再去观察段头高水位的变化就行了，那么插多少条呢？再来计算一下，当前的L1是8192号块，前面一共63个区，每个区里面两个L1，加上第一个区里面的一个L2和L3，我们需要插满8192-63*2-2=8064

  1. declare
  2. i number;
  3. begin
  4. for i in 1..8064 loop
  5. insert into lp values(i,'a','a','a','a');
  6. end loop;
  7. end;
  8. /

    观察62号区的第二个L1，第一个L1的地址是0x01401f80，那么第二个就是01401f81，5号文件8065号块，dump出来的结果

  1. DBA Ranges :
  2.   --------------------------------------------------------
  3.    0x01401fc0 Length: 64 Offset: 0
  4.   
  5.    0:FULL 1:FULL 2:FULL 3:FULL
  6.    4:FULL 5:FULL 6:FULL 7:FULL
  7.    8:FULL 9:FULL 10:FULL 11:FULL
  8.    12:FULL 13:FULL 14:FULL 15:FULL
  9.    16:FULL 17:FULL 18:FULL 19:FULL
  10.    20:FULL 21:FULL 22:FULL 23:FULL
  11.    24:FULL 25:FULL 26:FULL 27:FULL
  12.    28:FULL 29:FULL 30:FULL 31:FULL
  13.    32:FULL 33:FULL 34:FULL 35:FULL
  14.    36:FULL 37:FULL 38:FULL 39:FULL
  15.    40:FULL 41:FULL 42:FULL 43:FULL
  16.    44:FULL 45:FULL 46:FULL 47:FULL
  17.    48:FULL 49:FULL 50:FULL 51:FULL
  18.    52:FULL 53:FULL 54:FULL 55:FULL
  19.    56:FULL 57:FULL 58:FULL 59:FULL
  20.    60:FULL 61:FULL 62:FULL 63:FULL
  21.   --------------------------------------------------------
  22. End dump data blocks tsn: 8 file#: 5 minblk 8065 maxblk 8065

    居然全满了？再看8192号L1呢

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x01402000 Length: 128 Offset: 0
  4. 0x01402080 Length: 128 Offset: 128
  6. 0:Metadata 1:FULL 2:FULL 3:FULL
  7. 4:FULL 5:FULL 6:FULL 7:FULL
  8. 8:FULL 9:FULL 10:FULL 11:FULL
  9. 12:FULL 13:FULL 14:FULL 15:FULL
  10. 16:FULL 17:FULL 18:FULL 19:FULL
  11. 20:FULL 21:FULL 22:FULL 23:FULL
  12. 24:FULL 25:FULL 26:FULL 27:FULL
  13. 28:FULL 29:FULL 30:FULL 31:FULL
  14. 32:FULL 33:FULL 34:FULL 35:FULL
  15. 36:FULL 37:FULL 38:FULL 39:FULL
  16. 40:FULL 41:FULL 42:FULL 43:FULL
  17. 44:FULL 45:FULL 46:FULL 47:FULL
  18. 48:FULL 49:FULL 50:FULL 51:FULL
  19. 52:FULL 53:FULL 54:FULL 55:FULL
  20. 56:FULL 57:FULL 58:FULL 59:FULL
  21. 60:FULL 61:FULL 62:FULL 63:FULL
  22. 64:FULL 65:FULL 66:FULL 67:FULL
  23. 68:FULL 69:FULL 70:FULL 71:FULL
  24. 72:FULL 73:FULL 74:FULL 75:FULL
  25. 76:FULL 77:FULL 78:FULL 79:FULL
  26. 80:FULL 81:FULL 82:FULL 83:FULL
  27. 84:FULL 85:FULL 86:FULL 87:FULL
  28. 88:FULL 89:FULL 90:FULL 91:FULL
  29. 92:FULL 93:FULL 94:FULL 95:FULL
  30. 96:FULL 97:FULL 98:FULL 99:FULL
  31. 100:FULL 101:FULL 102:FULL 103:FULL
  32. 104:FULL 105:FULL 106:FULL 107:FULL
  33. 108:FULL 109:FULL 110:FULL 111:FULL
  34. 112:FULL 113:FULL 114:FULL 115:FULL
  35. 116:FULL 117:FULL 118:FULL 119:FULL
  36. 120:FULL 121:FULL 122:FULL 123:FULL
  37. 124:FULL 125:FULL 126:FULL 127:FULL
  38. 128:unformatted 129:unformatted 130:unformatted 131:unformatted
  39. 132:unformatted 133:unformatted 134:unformatted 135:unformatted
  40. 136:unformatted 137:unformatted 138:unformatted 139:unformatted
  41. 140:unformatted 141:unformatted 142:unformatted 143:unformatted
  42. 144:75-100% free 145:75-100% free 146:75-100% free 147:75-100% free
  43. 148:75-100% free 149:75-100% free 150:75-100% free 151:75-100% free
  44. 152:75-100% free 153:75-100% free 154:75-100% free 155:FULL
  45. 156:75-100% free 157:75-100% free 158:75-100% free 159:75-100% free
  46. 160:75-100% free 161:75-100% free 162:75-100% free 163:FULL
  47. 164:75-100% free 165:75-100% free 166:75-100% free 167:75-100% free
  48. 168:75-100% free 169:75-100% free 170:75-100% free 171:75-100% free
  49. 172:75-100% free 173:75-100% free 174:75-100% free 175:75-100% free
  50. 176:unformatted 177:unformatted 178:unformatted 179:unformatted
  51. 180:unformatted 181:unformatted 182:unformatted 183:unformatted
  52. 184:unformatted 185:unformatted 186:unformatted 187:unformatted
  53. 188:unformatted 189:unformatted 190:unformatted 191:unformatted
  54. 192:unformatted 193:unformatted 194:unformatted 195:unformatted
  55. 196:unformatted 197:unformatted 198:unformatted 199:unformatted
  56. 200:unformatted 201:unformatted 202:unformatted 203:unformatted
  57. 204:unformatted 205:unformatted 206:unformatted 207:unformatted
  58. 208:unformatted 209:unformatted 210:unformatted 211:unformatted
  59. 212:unformatted 213:unformatted 214:unformatted 215:unformatted
  60. 216:unformatted 217:unformatted 218:unformatted 219:unformatted
  61. 220:unformatted 221:unformatted 222:unformatted 223:unformatted
  62. 224:unformatted 225:unformatted 226:unformatted 227:unformatted
  63. 228:unformatted 229:unformatted 230:unformatted 231:unformatted
  64. 232:unformatted 233:unformatted 234:unformatted 235:unformatted
  65. 236:unformatted 237:unformatted 238:unformatted 239:unformatted
  66. 240:unformatted 241:unformatted 242:unformatted 243:unformatted
  67. 244:unformatted 245:unformatted 246:unformatted 247:unformatted
  68. 248:unformatted 249:unformatted 250:unformatted 251:unformatted
  69. 252:unformatted 253:unformatted 254:unformatted 255:unformatted
  70. --------------------------------------------------------
  71. End dump data blocks tsn: 8 file#: 5 minblk 8192 maxblk 8192

    完蛋了，计算错误，L1下面的第二个区也已经被格式化了，看段头信息

  1. Extent Control Header
  2. -----------------------------------------------------------------
  3. Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
  4. last map 0x00000000 #maps: 0 offset: 2716
  5. Highwater:: 0x01402100 ext#: 64 blk#: 128 ext size: 128
  6. #blocks in seg. hdr's freelists: 0
  7. #blocks below: 8191
  8. mapblk 0x00000000 offset: 64

    果然高水位指到了第66个区的L1块地址，这会影响我们的判断，到底哪里出问题了呢？观察8192号块内容是后128个块里被插入了2条，前128个块全满，也就是说我多插了130个块，后来猛然醒悟，我使用DBA来计算的，忘记减去128个块的文件头了，这么一算结果相查差还是不大的，太马虎了，要不是因为马虎哥也能上清华北大了；然后该怎么办呢？重新来过？不用！既然这个L1推过头了，索性把它堆满，然后我们去观察下一个L1，这下就好计算了；

  1. declare
  2. i number;
  3. begin
  4. for i in 1..126 loop
  5. insert into lp values(i,'a','a','a','a');
  6. end loop;
  7. end;
  8. /

    观察8192号块

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x01402000 Length: 128 Offset: 0
  4. 0x01402080 Length: 128 Offset: 128
  6. 0:Metadata 1:FULL 2:FULL 3:FULL
  7. 4:FULL 5:FULL 6:FULL 7:FULL
  8. 8:FULL 9:FULL 10:FULL 11:FULL
  9. 12:FULL 13:FULL 14:FULL 15:FULL
  10. 16:FULL 17:FULL 18:FULL 19:FULL
  11. 20:FULL 21:FULL 22:FULL 23:FULL
  12. 24:FULL 25:FULL 26:FULL 27:FULL
  13. 28:FULL 29:FULL 30:FULL 31:FULL
  14. 32:FULL 33:FULL 34:FULL 35:FULL
  15. 36:FULL 37:FULL 38:FULL 39:FULL
  16. 40:FULL 41:FULL 42:FULL 43:FULL
  17. 44:FULL 45:FULL 46:FULL 47:FULL
  18. 48:FULL 49:FULL 50:FULL 51:FULL
  19. 52:FULL 53:FULL 54:FULL 55:FULL
  20. 56:FULL 57:FULL 58:FULL 59:FULL
  21. 60:FULL 61:FULL 62:FULL 63:FULL
  22. 64:FULL 65:FULL 66:FULL 67:FULL
  23. 68:FULL 69:FULL 70:FULL 71:FULL
  24. 72:FULL 73:FULL 74:FULL 75:FULL
  25. 76:FULL 77:FULL 78:FULL 79:FULL
  26. 80:FULL 81:FULL 82:FULL 83:FULL
  27. 84:FULL 85:FULL 86:FULL 87:FULL
  28. 88:FULL 89:FULL 90:FULL 91:FULL
  29. 92:FULL 93:FULL 94:FULL 95:FULL
  30. 96:FULL 97:FULL 98:FULL 99:FULL
  31. 100:FULL 101:FULL 102:FULL 103:FULL
  32. 104:FULL 105:FULL 106:FULL 107:FULL
  33. 108:FULL 109:FULL 110:FULL 111:FULL
  34. 112:FULL 113:FULL 114:FULL 115:FULL
  35. 116:FULL 117:FULL 118:FULL 119:FULL
  36. 120:FULL 121:FULL 122:FULL 123:FULL
  37. 124:FULL 125:FULL 126:FULL 127:FULL
  38. 128:FULL 129:FULL 130:FULL 131:FULL
  39. 132:FULL 133:FULL 134:FULL 135:FULL
  40. 136:FULL 137:FULL 138:FULL 139:FULL
  41. 140:FULL 141:FULL 142:FULL 143:FULL
  42. 144:FULL 145:FULL 146:FULL 147:FULL
  43. 148:FULL 149:FULL 150:FULL 151:FULL
  44. 152:FULL 153:FULL 154:FULL 155:FULL
  45. 156:FULL 157:FULL 158:FULL 159:FULL
  46. 160:FULL 161:FULL 162:FULL 163:FULL
  47. 164:FULL 165:FULL 166:FULL 167:FULL
  48. 168:FULL 169:FULL 170:FULL 171:FULL
  49. 172:FULL 173:FULL 174:FULL 175:FULL
  50. 176:FULL 177:FULL 178:FULL 179:FULL
  51. 180:FULL 181:FULL 182:FULL 183:FULL
  52. 184:FULL 185:FULL 186:FULL 187:FULL
  53. 188:FULL 189:FULL 190:FULL 191:FULL
  54. 192:FULL 193:FULL 194:FULL 195:FULL
  55. 196:FULL 197:FULL 198:FULL 199:FULL
  56. 200:FULL 201:FULL 202:FULL 203:FULL
  57. 204:FULL 205:FULL 206:FULL 207:FULL
  58. 208:FULL 209:FULL 210:FULL 211:FULL
  59. 212:FULL 213:FULL 214:FULL 215:FULL
  60. 216:FULL 217:FULL 218:FULL 219:FULL
  61. 220:FULL 221:FULL 222:FULL 223:FULL
  62. 224:FULL 225:FULL 226:FULL 227:FULL
  63. 228:FULL 229:FULL 230:FULL 231:FULL
  64. 232:FULL 233:FULL 234:FULL 235:FULL
  65. 236:FULL 237:FULL 238:FULL 239:FULL
  66. 240:FULL 241:FULL 242:FULL 243:FULL
  67. 244:FULL 245:FULL 246:FULL 247:FULL
  68. 248:FULL 249:FULL 250:FULL 251:FULL
  69. 252:FULL 253:FULL 254:FULL 255:FULL
  70. --------------------------------------------------------
  71. End dump data blocks tsn: 8 file#: 5 minblk 8192 maxblk 8192

    已经全满了，这个时候我们再去看一下段头的高水位

  1. Extent Control Header
  2. -----------------------------------------------------------------
  3. Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
  4. last map 0x00000000 #maps: 0 offset: 2716
  5. Highwater:: 0x01402100 ext#: 64 blk#: 128 ext size: 128
  6. #blocks in seg. hdr's freelists: 0
  7. #blocks below: 8191
  8. mapblk 0x00000000 offset: 64

    没有动，看来oracle也是事不到眼前不知道着急啊，来看一看下一个L1

  1. SQL> select dbms_utility.data_block_address_file(to_number('01402100', 'xxxxxxxx')) file#,
  2.   2 dbms_utility.data_block_address_block(to_number('01402100', 'xxxxxxxx')) block#
  3.   3 from dual;

  5.      FILE# BLOCK#
  6. ---------- ----------
  7.          5 8448

    这个L1是5号文件8448号块，dump出来

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x01402100 Length: 128 Offset: 0
  4. 0x01402180 Length: 128 Offset: 128
  6. 0:Metadata 1:unformatted 2:unformatted 3:unformatted
  7. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  8. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  9. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  10. 16:unformatted 17:unformatted 18:unformatted 19:unformatted
  11. 20:unformatted 21:unformatted 22:unformatted 23:unformatted
  12. 24:unformatted 25:unformatted 26:unformatted 27:unformatted
  13. 28:unformatted 29:unformatted 30:unformatted 31:unformatted
  14. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  15. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  16. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  17. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  18. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  19. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  20. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  21. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  22. 64:unformatted 65:unformatted 66:unformatted 67:unformatted
  23. 68:unformatted 69:unformatted 70:unformatted 71:unformatted
  24. 72:unformatted 73:unformatted 74:unformatted 75:unformatted
  25. 76:unformatted 77:unformatted 78:unformatted 79:unformatted
  26. 80:unformatted 81:unformatted 82:unformatted 83:unformatted
  27. 84:unformatted 85:unformatted 86:unformatted 87:unformatted
  28. 88:unformatted 89:unformatted 90:unformatted 91:unformatted
  29. 92:unformatted 93:unformatted 94:unformatted 95:unformatted
  30. 96:unformatted 97:unformatted 98:unformatted 99:unformatted
  31. 100:unformatted 101:unformatted 102:unformatted 103:unformatted
  32. 104:unformatted 105:unformatted 106:unformatted 107:unformatted
  33. 108:unformatted 109:unformatted 110:unformatted 111:unformatted
  34. 112:unformatted 113:unformatted 114:unformatted 115:unformatted
  35. 116:unformatted 117:unformatted 118:unformatted 119:unformatted
  36. 120:unformatted 121:unformatted 122:unformatted 123:unformatted
  37. 124:unformatted 125:unformatted 126:unformatted 127:unformatted
  38. 128:unformatted 129:unformatted 130:unformatted 131:unformatted
  39. 132:unformatted 133:unformatted 134:unformatted 135:unformatted
  40. 136:unformatted 137:unformatted 138:unformatted 139:unformatted
  41. 140:unformatted 141:unformatted 142:unformatted 143:unformatted
  42. 144:unformatted 145:unformatted 146:unformatted 147:unformatted
  43. 148:unformatted 149:unformatted 150:unformatted 151:unformatted
  44. 152:unformatted 153:unformatted 154:unformatted 155:unformatted
  45. 156:unformatted 157:unformatted 158:unformatted 159:unformatted
  46. 160:unformatted 161:unformatted 162:unformatted 163:unformatted
  47. 164:unformatted 165:unformatted 166:unformatted 167:unformatted
  48. 168:unformatted 169:unformatted 170:unformatted 171:unformatted
  49. 172:unformatted 173:unformatted 174:unformatted 175:unformatted
  50. 176:unformatted 177:unformatted 178:unformatted 179:unformatted
  51. 180:unformatted 181:unformatted 182:unformatted 183:unformatted
  52. 184:unformatted 185:unformatted 186:unformatted 187:unformatted
  53. 188:unformatted 189:unformatted 190:unformatted 191:unformatted
  54. 192:unformatted 193:unformatted 194:unformatted 195:unformatted
  55. 196:unformatted 197:unformatted 198:unformatted 199:unformatted
  56. 200:unformatted 201:unformatted 202:unformatted 203:unformatted
  57. 204:unformatted 205:unformatted 206:unformatted 207:unformatted
  58. 208:unformatted 209:unformatted 210:unformatted 211:unformatted
  59. 212:unformatted 213:unformatted 214:unformatted 215:unformatted
  60. 216:unformatted 217:unformatted 218:unformatted 219:unformatted
  61. 220:unformatted 221:unformatted 222:unformatted 223:unformatted
  62. 224:unformatted 225:unformatted 226:unformatted 227:unformatted
  63. 228:unformatted 229:unformatted 230:unformatted 231:unformatted
  64. 232:unformatted 233:unformatted 234:unformatted 235:unformatted
  65. 236:unformatted 237:unformatted 238:unformatted 239:unformatted
  66. 240:unformatted 241:unformatted 242:unformatted 243:unformatted
  67. 244:unformatted 245:unformatted 246:unformatted 247:unformatted
  68. 248:unformatted 249:unformatted 250:unformatted 251:unformatted
  69. 252:unformatted 253:unformatted 254:unformatted 255:unformatted
  70. --------------------------------------------------------
  71. End dump data blocks tsn: 8 file#: 5 minblk 8448 maxblk 8448

    256个块，除了第一个是L1元数据之外，没有一个格式化的，这就达到了我们的目的，我现在要插入一条数据，迫使高水位推动，猜一下高水位会在哪？在L1管理的最后一个块后面么？
看插入一行数据之后的8448号块

  1. DBA Ranges :
  2. --------------------------------------------------------
  3. 0x01402100 Length: 128 Offset: 0
  4. 0x01402180 Length: 128 Offset: 128
  6. 0:Metadata 1:unformatted 2:unformatted 3:unformatted
  7. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  8. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  9. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  10. 16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
  11. 20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
  12. 24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
  13. 28:FULL 29:75-100% free 30:75-100% free 31:75-100% free
  14. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  15. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  16. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  17. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  18. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  19. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  20. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  21. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  22. 64:unformatted 65:unformatted 66:unformatted 67:unformatted
  23. 68:unformatted 69:unformatted 70:unformatted 71:unformatted
  24. 72:unformatted 73:unformatted 74:unformatted 75:unformatted
  25. 76:unformatted 77:unformatted 78:unformatted 79:unformatted
  26. 80:unformatted 81:unformatted 82:unformatted 83:unformatted
  27. 84:unformatted 85:unformatted 86:unformatted 87:unformatted
  28. 88:unformatted 89:unformatted 90:unformatted 91:unformatted
  29. 92:unformatted 93:unformatted 94:unformatted 95:unformatted
  30. 96:unformatted 97:unformatted 98:unformatted 99:unformatted
  31. 100:unformatted 101:unformatted 102:unformatted 103:unformatted
  32. 104:unformatted 105:unformatted 106:unformatted 107:unformatted
  33. 108:unformatted 109:unformatted 110:unformatted 111:unformatted
  34. 112:unformatted 113:unformatted 114:unformatted 115:unformatted
  35. 116:unformatted 117:unformatted 118:unformatted 119:unformatted
  36. 120:unformatted 121:unformatted 122:unformatted 123:unformatted
  37. 124:unformatted 125:unformatted 126:unformatted 127:unformatted
  38. 128:unformatted 129:unformatted 130:unformatted 131:unformatted
  39. 132:unformatted 133:unformatted 134:unformatted 135:unformatted
  40. 136:unformatted 137:unformatted 138:unformatted 139:unformatted
  41. 140:unformatted 141:unformatted 142:unformatted 143:unformatted
  42. 144:unformatted 145:unformatted 146:unformatted 147:unformatted
  43. 148:unformatted 149:unformatted 150:unformatted 151:unformatted
  44. 152:unformatted 153:unformatted 154:unformatted 155:unformatted
  45. 156:unformatted 157:unformatted 158:unformatted 159:unformatted
  46. 160:unformatted 161:unformatted 162:unformatted 163:unformatted
  47. 164:unformatted 165:unformatted 166:unformatted 167:unformatted
  48. 168:unformatted 169:unformatted 170:unformatted 171:unformatted
  49. 172:unformatted 173:unformatted 174:unformatted 175:unformatted
  50. 176:unformatted 177:unformatted 178:unformatted 179:unformatted
  51. 180:unformatted 181:unformatted 182:unformatted 183:unformatted
  52. 184:unformatted 185:unformatted 186:unformatted 187:unformatted
  53. 188:unformatted 189:unformatted 190:unformatted 191:unformatted
  54. 192:unformatted 193:unformatted 194:unformatted 195:unformatted
  55. 196:unformatted 197:unformatted 198:unformatted 199:unformatted
  56. 200:unformatted 201:unformatted 202:unformatted 203:unformatted
  57. 204:unformatted 205:unformatted 206:unformatted 207:unformatted
  58. 208:unformatted 209:unformatted 210:unformatted 211:unformatted
  59. 212:unformatted 213:unformatted 214:unformatted 215:unformatted
  60. 216:unformatted 217:unformatted 218:unformatted 219:unformatted
  61. 220:unformatted 221:unformatted 222:unformatted 223:unformatted
  62. 224:unformatted 225:unformatted 226:unformatted 227:unformatted
  63. 228:unformatted 229:unformatted 230:unformatted 231:unformatted
  64. 232:unformatted 233:unformatted 234:unformatted 235:unformatted
  65. 236:unformatted 237:unformatted 238:unformatted 239:unformatted
  66. 240:unformatted 241:unformatted 242:unformatted 243:unformatted
  67. 244:unformatted 245:unformatted 246:unformatted 247:unformatted
  68. 248:unformatted 249:unformatted 250:unformatted 251:unformatted
  69. 252:unformatted 253:unformatted 254:unformatted 255:unformatted
  70. --------------------------------------------------------
  71. End dump data blocks tsn: 8 file#: 5 minblk 8448 maxblk 8448

    有一个块已经满了，揭晓答案的时候来了，dump出段头


  1. Extent Control Header
  2. -----------------------------------------------------------------
  3. Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
  4. last map 0x00000000 #maps: 0 offset: 2716
  5. Highwater:: 0x01402180 ext#: 65 blk#: 128 ext size: 128
  6. #blocks in seg. hdr's freelists: 0
  7. #blocks below: 8318
  8. mapblk 0x00000000 offset: 65
    高水位是0x01402180，这个地址在哪呢？0x01402180比0x01402100多了十六进制80个块，就是十进制的128个块，仅仅是推动了一个区的大小，并不是一个L1的大小，这说明常规路径下插入高水位的推动是以L1和区中小的那个单位来推动的，就是L1小就推动L1里面所有块的大小，区小就推动一个区的大小，可见所谓的高并发并非像宣传的那样，还是受高水位的限制的，这个时候的区块大概像下面这个样子：

    开几个session做下插入试试
 
  1. SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp where trim(des1)='b';
  3. 5 8474
  4. 5 8482
  5. 5 8484
  6. 5 8488
  7. 5 8489
  8. 5 8490
  9. 5 8491
  10. 5 8492
  11. 5 8493
  12. 5 8494
  13. 5 8495
  14. 5 8496
  15. 5 8498
  16. 5 8500
  17. 5 8504
  18. 5 8506
  19. 5 8512
  20. 5 8514
  21. 5 8520
  22. 5 8522
  24. 20 rows selected.
   全部是在高水位之下


     这个实验有一点值得思考，就是PCTFREE如何设置，在插入删除频繁的段，PCTFREE的值应该要远离三个百分比线，就是25%/50%/75%，避免频繁插入删除的时候块状态频繁改变，由full变为非full，如此就会修改L1块的内容，有可能造成buffer busy waits。

   而大家熟知的直接路径下的插入是直接在高水位之上插入并且绕过cache buffer的，这种情况下的高水位是如何推动的呢？我们再慢慢分析.




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

1
0
上一篇： RMAN异机异目录完全恢复
下一篇： ASSM三级位图结构与高水位的探究（下）
请登录后发表评论 登录
全部评论
liupzmin

注册时间：2014-05-17

  • 博文量
    29
  • 访问量
    117429

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593