Oracle中ASSM模式下,全表扫描的L3块的逻辑读的影响

  • PiscesCanon

    2017-06-15 23:18:39
  • Oracle
  • 原创
前言：
本问题由之前的实验拓展遗留的一些疑问，详见:http://blog.itpub.net/30174570/viewspace-2140241/。
排除db_file_multiblock_read_count参数和动态采样(详见:http://blog.itpub.net/30174570/viewspace-2140240/)的影响。

操作系统环境：
  1. [oracle@oracle ~]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle ~]$ lsb_release -a
  4. LSB Version:     :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID:  RedHatEnterpriseServer
  6. Description:     Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:         6.5
  8. Codename:        Santiago

数据库版本：
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

构造测试数据：
  1. SYS@proc> drop table t purge;

  3. Table dropped.

  5. SYS@proc> create table t as select * from dba_objects where rownum<=1200;

  7. Table created.

  9. SYS@proc> alter table t move tablespace test;

  11. Table altered.

  13. SYS@proc> select dbms_rowid.rowid_block_number(rowid) block#,min(rownum),max(rownum) from t group by dbms_rowid.rowid_block_number(rowid) order by dbms_rowid.rowid_block_number(rowid);

  15.     BLOCK# MIN(ROWNUM) MAX(ROWNUM)
  16. ---------- ----------- -----------
  17.        131           1          88
  18.        132          89         171
  19.        133         172         251
  20.        134         252         329
  21.        135         330         407
  22.        136         408         487
  23.        137         488         567
  24.        138         568         646
  25.        139         647         724
  26.        140         725         798
  27.        141         799         873

  29.     BLOCK# MIN(ROWNUM) MAX(ROWNUM)
  30. ---------- ----------- -----------
  31.        142         874         946
  32.        143         947        1022
  33.        145        1023        1104
  34.        146        1105        1179
  35.        147        1180        1200

  37. 16 rows selected.

  39. SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='T';

  41.  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
  42. ---------- ---------- ---------- ----------
  43.          0          6        128          8    --128 129 130 131 132 133 134 135
  44.          1          6        136          8
  45.          2          6        144          8

引出问题：
  1. SYS@proc> alter system set db_file_multiblock_read_count=1; --避免该参数的影响

  3. System altered.

  5. SYS@proc> show parameter db_file_multiblock_read_count

  7. NAME                                 TYPE        VALUE
  8. ------------------------------------ ----------- ------------------------------
  9. db_file_multiblock_read_count        integer     1

  11. SYS@proc> analyze table t compute statistics;   --避免动态采样的影响

  13. Table analyzed.

  15. SYS@proc> alter system flush buffer_cache;

  17. System altered.

  19. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  21. no rows selected

  23. SYS@proc> select count(*) from t where rownum<=171;

  25.   COUNT(*)
  26. ----------
  27.        171

  29. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  31.      FILE# DBABLK     STATE
  32. ---------- ---------- ----------
  33.      6     130     1
  34.      6     131     1
  35.      6     132     1
sql语句"select count(*) from t where rownum<=171"只读取了131,132两个块，但是130也被读取进去，这里读取该块的作用是什么？

实验过程：
  1. SYS@proc> alter system flush buffer_cache;

  3. System altered.

  5. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  7. no rows selected

  9. SYS@proc> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='SYS' and segment_name='T';   --该语句可确定段头块是130

  11. HEADER_FILE HEADER_BLOCK
  12. ----------- ------------
  13.           6          130

  15. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  17. no rows selected

  19. SYS@proc> select count(*) from t where rownum<=171;

  21.   COUNT(*)
  22. ----------
  23.        171

  25. SYS@proc> set autotrace on
  26. SYS@proc> select count(*) from t where rownum<=171;

  28.   COUNT(*)
  29. ----------
  30.        171


  33. Execution Plan
  34. ----------------------------------------------------------
  35. Plan hash value: 239743108

  37. --------------------------------------------------------------------
  38. | Id  | Operation           | Name | Rows  | Cost (%CPU)| Time     |
  39. --------------------------------------------------------------------
  40. |   0 | SELECT STATEMENT    |      |     1 |    21   (0)| 00:00:01 |
  41. |   1 |  SORT AGGREGATE     |      |     1 |            |          |
  42. |*  2 |   COUNT STOPKEY     |      |       |            |          |
  43. |   3 |    TABLE ACCESS FULL| T    |  1200 |    21   (0)| 00:00:01 |
  44. --------------------------------------------------------------------

  46. Predicate Information (identified by operation id):
  47. ---------------------------------------------------

  49.    2 - filter(ROWNUM<=171)


  52. Statistics
  53. ----------------------------------------------------------
  54.      0 recursive calls
  55.      0 db block gets
  56.      4 consistent gets
  57.      0 physical reads
  58.      0 redo size
  59.    527 bytes sent via SQL*Net to client
  60.    523 bytes received via SQL*Net from client
  61.      2 SQL*Net roundtrips to/from client
  62.      0 sorts (memory)
  63.      0 sorts (disk)
  64.      1 rows processed

  66. SYS@proc> set autotrace off
  67. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  69.      FILE#     DBABLK      STATE
  70. ---------- ---------- ----------
  71.          6        130          1
  72.          6        131          1
  73.          6        132          1
上述实验过程第9行处，得出段头块是130号块，在ASSM中，段头是第一个L3块。
为什么该处的逻辑读是4呢？全表扫描下，会跳过L1(块128)和L2(块129)，直接读取段头L3和高水位线以下的所有块(为什么全表扫描只读了131和132，受到rownum的影响，详情见http://blog.itpub.net/30174570/viewspace-2140240/)。但是L3要读取两次，所以逻辑读为4。读取L3两次，一次读取Extent Map，一次读取Auxillary Map。

回到一开始的问题，Oracle通过读取L3段头块确定全表扫描应该读取的区和区中的数据块，这个就是为什么除了131和132这两个实际包含数据的数据块以外，还要读取130块的原因。

数据块130的部分dump信息：
  1. Extent Control Header
  2.   -----------------------------------------------------------------
  3.   Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 24
  4.                   last map 0x00000000 #maps: 0 offset: 2716
  5.       Highwater:: 0x01800094 ext#: 2 blk#: 4 ext size: 8
  6.   #blocks in seg. hdr's freelists: 0
  7.   #blocks below: 20
  8.   mapblk 0x00000000 offset: 2
  9.                    Unlocked
  10.   --------------------------------------------------------
  11.   Low HighWater Mark :
  12.       Highwater:: 0x01800094 ext#: 2 blk#: 4 ext size: 8
  13.   #blocks in seg. hdr's freelists: 0
  14.   #blocks below: 20
  15.   mapblk 0x00000000 offset: 2
  16.   Level 1 BMB for High HWM block: 0x01800090
  17.   Level 1 BMB for Low HWM block: 0x01800090
  18.   --------------------------------------------------------
  19.   Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
  20.   L2 Array start offset: 0x00001434
  21.   First Level 3 BMB: 0x00000000
  22.   L2 Hint for inserts: 0x01800081
  23.   Last Level 1 BMB: 0x01800090
  24.   Last Level II BMB: 0x01800081
  25.   Last Level III BMB: 0x00000000
  26.      Map Header:: next 0x00000000 #extents: 3 obj#: 89405 flag: 0x10000000
  27.   Inc # 0
  28.   Extent Map
  29.   -----------------------------------------------------------------
  30.    0x01800080 length: 8
  31.    0x01800088 length: 8
  32.    0x01800090 length: 8

  34.   Auxillary Map
  35.   --------------------------------------------------------
  36.    Extent 0 : L1 dba: 0x01800080 Data dba: 0x01800083           "0x01800083"->二进制：00000001 10000000 00000000 10000011
  37.    Extent 1 : L1 dba: 0x01800080 Data dba: 0x01800088            前10位是文件号，后22位是块号，0000000110->文件号：6，000000 00000000 10000011->数据块：131
  38.    Extent 2 : L1 dba: 0x01800090 Data dba: 0x01800091
  39.   --------------------------------------------------------

  41.    Second Level Bitmap block DBAs
  42.    --------------------------------------------------------
  43.    DBA 1: 0x01800081

  45. End dump data blocks tsn: 9 file#: 6 minblk 130 maxblk 130

  1. SYS@proc> select to_number('01800083','xxxxxxxx') from dual;

  3. TO_NUMBER('01800083','XXXXXXXX')
  4. --------------------------------
  5.                         25165955

  7. SYS@proc> select dbms_utility.data_block_address_file(25165955) from dual;

  9. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165955)
  10. ----------------------------------------------
  11.                                              6

  13. SYS@proc> select dbms_utility.data_block_address_block(25165955) from dual;

  15. DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(25165955)
  16. -----------------------------------------------
  17.                                             131

其他拓展：
  1. SYS@proc> --16进制数0x01800080转换为10进制数
  2. SYS@proc> select to_number('01800080','xxxxxxxx') from dual;

  4. TO_NUMBER('01800080','XXXXXXXX')
  5. --------------------------------
  6.                         25165952

  8. SYS@proc> --10进制25165952转换为16进制
  9. SYS@proc> select to_char(25165952,'xxxxxxxx') from dual;

  11. TO_CHAR(2
  12. ---------
  13.   1800080

  15. SYS@proc> --2进制转换为10进制
  16. SYS@proc> select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual;

  18.          A          B
  19. ---------- ----------
  20.         13          2

  22. SYS@proc> select bin_to_num(1,1,1,0,1) from dual;

  24. BIN_TO_NUM(1,1,1,0,1)
  25. ---------------------
  26.                    29


请使用浏览器的分享功能分享到微信等