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

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

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

原创 Oracle 作者：liupzmin 时间：2015-09-30 11:35:21 0 删除 编辑

    上一篇文章介绍了ASSM三级位图管理下的数据插入和高水位推进的关系，我们得出的结论是随着数据不断的插入，段大小的不断增长，L1中挂载的数据块的数量也会增长，而高水位是以L1中数据块大小的总和与区大小之间最小的一方为单位进行推进，而且我们知道要想应对大并发插入，就要使高水位之下的空闲块的数量尽可能多，但是如此一来就有可能造成空间的浪费，而oracle的初心也是本着节省空间的目的来设计的。
    之前我们用的是常规路径插入，能以L1和区的单位推动高水位，前提是要填满L1或区，而我们熟知的直接路径插入是在高水位之上插入，那是不是可以用直接路径插入快速增加高水位呢？我们来一探究竟。
    
    首先构造测试的表空间和表，跟上一篇一样

  1. SQL> drop tablespace lp including contents and datafiles;

  3. Tablespace dropped.

  5. SQL> create tablespace lp datafile '/home/oracle/app/oracle/oradata/DG43/lp.dbf' size 2048M uniform size 1m;
  6. create table lp (id number,des1 char(2000),des2 char(2000),des3 char(2000),des4 char(500)) tablespace lp;

  8. Tablespace created.

  10. SQL>
  11. Table created.

  13. SQL> alter table lp pctfree 24;

  15. Table altered.


  18. SQL> select object_id,object_name from dba_objects where object_name='LP';

  20.  OBJECT_ID OBJECT_NAME
  21. ---------- ------------------------------
  22.      78760 LP
首先看一下，78760这个对象在buffer里的数据块有哪些
  1. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  3.      FILE# BLOCK# D OBJD
  4. ---------- ---------- - ----------
  5.          5 131 N 78760
  6.          5 129 N 78760
  7.          5 130 N 78760
  8.          5 128 N 78760
有没有眼熟呢，128、129、130、131四个块刚好是两个L1、L2、L3
我们定位段头块，看一下此时的高水位
  1. SQL> select segment_name ,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='LP';
  3. SEGMENT_NAME HEADER_FILE HEADER_BLOCK
  4. ------------------------------ ----------- ------------
  5. LP 5 131
  7. Extent Control Header
  8. -----------------------------------------------------------------
  9. Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
  10. last map 0x00000000 #maps: 0 offset: 2716
  11. Highwater:: 0x01400084 ext#: 0 blk#: 4 ext size: 128
  12. #blocks in seg. hdr's freelists: 0
  13. #blocks below: 0
  14. mapblk 0x00000000 offset: 0
  16. SQL> select dbms_utility.data_block_address_file(to_number('01400084', 'xxxxxxxx')) file#,
  17. 2 dbms_utility.data_block_address_block(to_number('01400084', 'xxxxxxxx')) block#
  18. 3 from dual;
  20. FILE# BLOCK#
  21. ---------- ----------
  22. 5 132
可以看出在没有任何数据的情况下，高水位是在第一个数据块上的，下面常规插入一行数据
  1. insert into lp values(1,'a','a','a','a');
看一下高水位和buffer中的块
  1. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  3.      FILE# BLOCK# D OBJD
  4. ---------- ---------- - ----------
  5.          5 165 Y 78760
  6.          5 131 Y 78760
  7.          5 173 Y 78760
  8.          5 160 Y 78760
  9.          5 168 Y 78760
  10.          5 163 Y 78760
  11.          5 129 N 78760
  12.          5 171 Y 78760
  13.          5 166 Y 78760
  14.          5 174 Y 78760
  15.          5 161 Y 78760

  17.      FILE# BLOCK# D OBJD
  18. ---------- ---------- - ----------
  19.          5 169 Y 78760
  20.          5 164 Y 78760
  21.          5 130 N 78760
  22.          5 172 Y 78760
  23.          5 167 Y 78760
  24.          5 175 Y 78760
  25.          5 162 Y 78760
  26.          5 128 Y 78760
  27.          5 170 Y 78760

  29. 20 rows selected.

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

  33. DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  34. ------------------------------------ ------------------------------------
  35.                                    5 160

  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

发现数据插入到160号块中，buffer中却多了16个块，高水位已移动到第二个L1中的第一个块上，看一下第一个L1
  1. HWM Flag: HWM Set
  2. Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
  3. #blocks in seg. hdr's freelists: 0
  4. #blocks below: 60
  5. mapblk 0x00000000 offset: 0
  6. --------------------------------------------------------
  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:unformatted 17:unformatted 18:unformatted 19:unformatted
  16. 20:unformatted 21:unformatted 22:unformatted 23:unformatted
  17. 24:unformatted 25:unformatted 26:unformatted 27:unformatted
  18. 28:unformatted 29:unformatted 30:unformatted 31:unformatted
  19. 32:FULL 33:75-100% free 34:75-100% free 35:75-100% free
  20. 36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
  21. 40:75-100% free 41:75-100% free 42:75-100% free 43:75-100% free
  22. 44:75-100% free 45:75-100% free 46:75-100% free 47:75-100% free
  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

  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
可以发现，buffer中新增的16个块是一次性格式化的这16个块，而高水位指在192号块上，直接路径插入一行
  1. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  3.      FILE# BLOCK# D OBJD
  4. ---------- ---------- - ----------
  5.          5 165 N 78760
  6.          5 131 N 78760
  7.          5 173 N 78760
  8.          5 160 N 78760
  9.          5 168 N 78760
  10.          5 163 N 78760
  11.          5 129 N 78760
  12.          5 171 N 78760
  13.          5 166 N 78760
  14.          5 174 N 78760
  15.          5 161 N 78760

  17.      FILE# BLOCK# D OBJD
  18. ---------- ---------- - ----------
  19.          5 169 N 78760
  20.          5 164 N 78760
  21.          5 130 N 78760
  22.          5 172 N 78760
  23.          5 167 N 78760
  24.          5 175 N 78760
  25.          5 162 N 78760
  26.          5 128 N 78760
  27.          5 170 N 78760

  29. 20 rows selected.


  32. SQL> insert /*+ append_values(lp)*/ into lp values(2,'b','b','b','b');

  34. 1 row created.

  36. SQL> commit;

  38. Commit complete.

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

  42. DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  43. ------------------------------------ ------------------------------------
  44.                                    5 160
  45.                                    5 192

  47. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  49.      FILE# BLOCK# D OBJD
  50. ---------- ---------- - ----------
  51.          5 165 N 78760
  52.          5 131 Y 78760
  53.          5 173 N 78760
  54.          5 160 N 78760
  55.          5 168 N 78760
  56.          5 163 N 78760
  57.          5 129 Y 78760
  58.          5 171 N 78760
  59.          5 192 Y 78760
  60.          5 166 N 78760
  61.          5 174 N 78760

  63.      FILE# BLOCK# D OBJD
  64. ---------- ---------- - ----------
  65.          5 161 N 78760
  66.          5 169 N 78760
  67.          5 164 N 78760
  68.          5 130 N 78760
  69.          5 172 N 78760
  70.          5 167 N 78760
  71.          5 175 N 78760
  72.          5 162 N 78760
  73.          5 128 Y 78760
  74.          5 170 N 78760

  76. 21 rows selected.

  78. SQL> alter system checkpoint;

  80. System altered.
确实插入到了192号块，看一下此时的高水位
  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:: 0x014000c1 ext#: 0 blk#: 65 ext size: 128
  6. #blocks in seg. hdr's freelists: 0
  7. #blocks below: 65
  8. mapblk 0x00000000 offset: 0
  10. HWM Flag: HWM Set
  11. Highwater:: 0x014000c1 ext#: 0 blk#: 65 ext size: 128
  12. #blocks in seg. hdr's freelists: 0
  13. #blocks below: 65
  14. mapblk 0x00000000 offset: 0
  15. --------------------------------------------------------
  16. DBA Ranges :
  17. --------------------------------------------------------
  18. 0x014000c0 Length: 64 Offset: 0
  20. 0:FULL 1:unformatted 2:unformatted 3:unformatted
  21. 4:unformatted 5:unformatted 6:unformatted 7:unformatted
  22. 8:unformatted 9:unformatted 10:unformatted 11:unformatted
  23. 12:unformatted 13:unformatted 14:unformatted 15:unformatted
  24. 16:unformatted 17:unformatted 18:unformatted 19:unformatted
  25. 20:unformatted 21:unformatted 22:unformatted 23:unformatted
  26. 24:unformatted 25:unformatted 26:unformatted 27:unformatted
  27. 28:unformatted 29:unformatted 30:unformatted 31:unformatted
  28. 32:unformatted 33:unformatted 34:unformatted 35:unformatted
  29. 36:unformatted 37:unformatted 38:unformatted 39:unformatted
  30. 40:unformatted 41:unformatted 42:unformatted 43:unformatted
  31. 44:unformatted 45:unformatted 46:unformatted 47:unformatted
  32. 48:unformatted 49:unformatted 50:unformatted 51:unformatted
  33. 52:unformatted 53:unformatted 54:unformatted 55:unformatted
  34. 56:unformatted 57:unformatted 58:unformatted 59:unformatted
  35. 60:unformatted 61:unformatted 62:unformatted 63:unformatted
  36. --------------------------------------------------------
  37. End dump data blocks tsn: 5 file#: 5 minblk 129 maxblk 129
发现高水位仅仅移动了1个block，再试一遍
  1. SQL> insert /*+ append_values(lp)*/ into lp values(3,'c','c','c','c');

  3. 1 row created.

  5. SQL> commit;

  7. Commit complete.

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

  11. DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  12. ------------------------------------ ------------------------------------
  13.                                    5 160
  14.                                    5 192
  15.                                    5 193

  17. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  19.      FILE# BLOCK# D OBJD
  20. ---------- ---------- - ----------
  21.          5 165 N 78760
  22.          5 131 Y 78760
  23.          5 173 N 78760
  24.          5 160 N 78760
  25.          5 168 N 78760
  26.          5 163 N 78760
  27.          5 129 Y 78760
  28.          5 171 N 78760
  29.          5 192 N 78760
  30.          5 166 N 78760
  31.          5 174 N 78760

  33.      FILE# BLOCK# D OBJD
  34. ---------- ---------- - ----------
  35.          5 161 N 78760
  36.          5 169 N 78760
  37.          5 164 N 78760
  38.          5 130 N 78760
  39.          5 172 N 78760
  40.          5 193 Y 78760
  41.          5 167 N 78760
  42.          5 175 N 78760
  43.          5 162 N 78760
  44.          5 128 N 78760
  45.          5 170 N 78760

  47. 22 rows selected.

  1. Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
  2. last map 0x00000000 #maps: 0 offset: 2716
  3. Highwater:: 0x014000c2 ext#: 0 blk#: 66 ext size: 128
  4. #blocks in seg. hdr's freelists: 0
  5. #blocks below: 66
  6. mapblk 0x00000000 offset: 0
仍然是只推动了一个block，但是发现为何每次直接路径插入的block都会出现在buffer里呢？难道是11g这个append_values新的hint的原因，再来试一下传统的append
  1. SQL> insert /*+ append*/ into lp select * from lp where trim(des1)='a';

  3. 1 row created.

  5. SQL> commit;

  7. Commit complete.

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

  11. DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  12. ------------------------------------ ------------------------------------
  13.                                    5 160
  14.                                    5 192
  15.                                    5 193
  16.                                    5 194

  18. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  20.      FILE# BLOCK# D OBJD
  21. ---------- ---------- - ----------
  22.          5 165 N 78760
  23.          5 131 Y 78760
  24.          5 173 N 78760
  25.          5 194 Y 78760
  26.          5 160 N 78760
  27.          5 168 N 78760
  28.          5 163 N 78760
  29.          5 129 Y 78760
  30.          5 171 N 78760
  31.          5 192 N 78760
  32.          5 166 N 78760

  34.      FILE# BLOCK# D OBJD
  35. ---------- ---------- - ----------
  36.          5 174 N 78760
  37.          5 161 N 78760
  38.          5 169 N 78760
  39.          5 164 N 78760
  40.          5 130 N 78760
  41.          5 172 N 78760
  42.          5 193 N 78760
  43.          5 167 N 78760
  44.          5 175 N 78760
  45.          5 162 N 78760
  46.          5 128 N 78760

  48.      FILE# BLOCK# D OBJD
  49. ---------- ---------- - ----------
  50.          5 170 N 78760

  52. 23 rows selected.
依然如故，不知道大家有没有发现，每次我执行完插入，都会执行一条select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;
这其实是全表扫描，本来块没在buffer里，一个FTS把块全给整进来了（小表buffer读，大表在11g中通常情况是直接路径读），知道原因了，再试一遍
  1. SQL> insert /*+ append*/ into lp select * from lp where trim(des1)='b';

  3. 1 row created.

  5. SQL> commit;

  7. Commit complete.

  9. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  11.      FILE# BLOCK# D OBJD
  12. ---------- ---------- - ----------
  13.          5 165 N 78760
  14.          5 131 Y 78760
  15.          5 173 N 78760
  16.          5 194 N 78760
  17.          5 160 N 78760
  18.          5 168 N 78760
  19.          5 163 N 78760
  20.          5 129 Y 78760
  21.          5 171 N 78760
  22.          5 192 N 78760
  23.          5 166 N 78760

  25.      FILE# BLOCK# D OBJD
  26. ---------- ---------- - ----------
  27.          5 174 N 78760
  28.          5 161 N 78760
  29.          5 169 N 78760
  30.          5 164 N 78760
  31.          5 130 N 78760
  32.          5 172 N 78760
  33.          5 193 N 78760
  34.          5 167 N 78760
  35.          5 175 N 78760
  36.          5 162 N 78760
  37.          5 128 N 78760

  39.      FILE# BLOCK# D OBJD
  40. ---------- ---------- - ----------
  41.          5 170 N 78760

  43. 23 rows selected.

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

  47. DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  48. ------------------------------------ ------------------------------------
  49.                                    5 160
  50.                                    5 192
  51.                                    5 193
  52.                                    5 194
  53.                                    5 195

  55. SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

  57.      FILE# BLOCK# D OBJD
  58. ---------- ---------- - ----------
  59.          5 165 N 78760
  60.          5 131 Y 78760
  61.          5 173 N 78760
  62.          5 194 N 78760
  63.          5 160 N 78760
  64.          5 168 N 78760
  65.          5 163 N 78760
  66.          5 129 Y 78760
  67.          5 171 N 78760
  68.          5 192 N 78760
  69.          5 166 N 78760

  71.      FILE# BLOCK# D OBJD
  72. ---------- ---------- - ----------
  73.          5 174 N 78760
  74.          5 195 Y 78760
  75.          5 161 N 78760
  76.          5 169 N 78760
  77.          5 164 N 78760
  78.          5 130 N 78760
  79.          5 172 N 78760
  80.          5 193 N 78760
  81.          5 167 N 78760
  82.          5 175 N 78760
  83.          5 162 N 78760

  85.      FILE# BLOCK# D OBJD
  86. ---------- ---------- - ----------
  87.          5 128 N 78760
  88.          5 170 N 78760

  90. 24 rows selected.
终于看到了想要的结果，总结一下吧，直接路径下高水位的推进大概如下图的样子：


不知道大家有没有注意到，直接路径插入完后再读入buffer中居然是个脏块，这是不是跟延迟提交清除有关呢？我们以后再慢慢分析！



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

0
0
上一篇： ASSM三级位图结构与高水位的探究（上）
下一篇： Redis 集群原理与使用
请登录后发表评论 登录
全部评论
liupzmin

注册时间：2014-05-17

  • 博文量
    29
  • 访问量
    117423

最新文章

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

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

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

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