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

    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;
   
2. 
   
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;
    
11. 
    
12. Tablespace created.
    
13. 
    
14. SQL> select file#,NAME,BYTES/1024/1204 from v$datafile;
    
15. 
    
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;
    
25. 
    
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';
   
8. 
   
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');
   
2. 
   
3. 1 row created.
   
4. 
   
5. SQL> commit;
   
6. 
   
7. Commit complete.
   
8. 
   
9. SQL> select segment_name ,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='LP';
   
10. 
    
11. SEGMENT_NAME HEADER_FILE HEADER_BLOCK
    
12. ------------------------------ ----------- ------------
    
13. LP 5 131
    
14. 
    
15. 
    
16. SQL> alter system checkpoint;
    
17. 
    
18. System altered.

LP的段头是5号文件，131号块，进行dump

1. SQL> alter system dump datafile 5 block 131;
   
2. 
   
3. SQL> col value for a65
   
4. SQL> select * from v$diag_info;
   
5. 
   
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
    
19. 
    
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;
   
4. 
   
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;
   
2. 
   
3. Table altered.
   
4. 
   
5. SQL> select TABLE_NAME,PCT_FREE from dba_tables where table_name='LP';
   
6. 
   
7. TABLE_NAME PCT_FREE
   
8. ------------------------------ ----------
   
9. LP 24
   
10. 
    
11. SQL> insert into lp values(2,'a','a','a','a');
    
12. 
    
13. 1 row created.
    
14. 
    
15. SQL> commit;
    
16. 
    
17. Commit complete.
    

 --手动触发检查点将内存中的块刷新到磁盘，本文每次dump之前都会做这个操作，以保证磁盘和内存的内容一致

1. SQL> alter system checkpoint;
   
2. 
   
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');
   
2. 
   
3. 1 row created.
   
4. 
   
5. SQL> insert into lp values(4,'a','a','a','a');
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> insert into lp values(5,'a','a','a','a');
   
10. 
    
11. 1 row created.
    
12. 
    
13. SQL> insert into lp values(6,'a','a','a','a');
    
14. 
    
15. 1 row created.
    
16. 
    
17. SQL> insert into lp values(7,'a','a','a','a');
    
18. 
    
19. 1 row created.
    
20. 
    
21. 单独session5条
    
22. 
    
23. SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;
    
24. 
    
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
    
39. 
    
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
24. 
    
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;
   
4. 
   
5.      FILE# BLOCK#
   
6. ---------- ----------
   
7.          5 192

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


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

首先给LP表多分配一些区

1. SQL> alter table lp allocate extent(size 100m);
   
2. 
   
3. Table altered.
   
4. 
   
5. SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID, BLOCKS from dba_extents where segment_name='LP';
   
6. 
   
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;
   
4. 
   
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做下插入试试
 

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp where trim(des1)='b'; 5 8474 5 8482 5 8484 5 8488 5 8489 5 8490 5 8491 5 8492 5 8493 5 8494 5 8495 5 8496 5 8498 5 8500 5 8504 5 8506 5 8512 5 8514 5 8520 5 8522 20 rows selected.

   全部是在高水位之下


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

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

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

转载于:http://blog.itpub.net/26838672/viewspace-1809264/