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

前言：
本问题由之前的实验拓展遗留的一些疑问，详见: 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;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

构造测试数据：

1. SYS@proc> drop table t purge;
   
2. 
   
3. Table dropped.
   
4. 
   
5. SYS@proc> create table t as select * from dba_objects where rownum<=1200;
   
6. 
   
7. Table created.
   
8. 
   
9. SYS@proc> alter table t move tablespace test;
   
10. 
    
11. Table altered.
    
12. 
    
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);
    
14. 
    
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
    
28. 
    
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
    
36. 
    
37. 16 rows selected.
    
38. 
    
39. SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='T';
    
40. 
    
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; --避免该参数的影响
   
2. 
   
3. System altered.
   
4. 
   
5. SYS@proc> show parameter db_file_multiblock_read_count
   
6. 
   
7. NAME                                 TYPE        VALUE
   
8. ------------------------------------ ----------- ------------------------------
   
9. db_file_multiblock_read_count        integer     1
10. 
    
11. SYS@proc> analyze table t compute statistics;   --避免动态采样的影响
    
12. 
    
13. Table analyzed.
    
14. 
    
15. SYS@proc> alter system flush buffer_cache;
    
16. 
    
17. System altered.
    
18. 
    
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;
    
20. 
    
21. no rows selected
    
22. 
    
23. SYS@proc> select count(*) from t where rownum<=171;
    
24. 
    
25.   COUNT(*)
    
26. ----------
    
27.        171
    
28. 
    
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;
    
30. 
    
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;
   
2. 
   
3. System altered.
   
4. 
   
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;
   
6. 
   
7. no rows selected
   
8. 
   
9. SYS@proc> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='SYS' and segment_name='T';   --该语句可确定段头块是130
   
10. 
    
11. HEADER_FILE HEADER_BLOCK
    
12. ----------- ------------
    
13.           6          130
    
14. 
    
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;
    
16. 
    
17. no rows selected
    
18. 
    
19. SYS@proc> select count(*) from t where rownum<=171;
    
20. 
    
21.   COUNT(*)
    
22. ----------
    
23.        171
    
24. 
    
25. SYS@proc> set autotrace on
    
26. SYS@proc> select count(*) from t where rownum<=171;
    
27. 
    
28.   COUNT(*)
    
29. ----------
    
30.        171
    
31. 
    
32. 
    
33. Execution Plan
    
34. ----------------------------------------------------------
    
35. Plan hash value: 239743108
    
36. 
    
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. --------------------------------------------------------------------
    
45. 
    
46. Predicate Information (identified by operation id):
    
47. ---------------------------------------------------
    
48. 
    
49.    2 - filter(ROWNUM<=171)
    
50. 
    
51. 
    
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
    
65. 
    
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;
    
68. 
    
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
    
33. 
    
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.   --------------------------------------------------------
    
40. 
    
41.    Second Level Bitmap block DBAs
    
42.    --------------------------------------------------------
    
43.    DBA 1: 0x01800081
    
44. 
    
45. End dump data blocks tsn: 9 file#: 6 minblk 130 maxblk 130

1. SYS@proc> select to_number('01800083','xxxxxxxx') from dual;
   
2. 
   
3. TO_NUMBER('01800083','XXXXXXXX')
   
4. --------------------------------
   
5.                         25165955
   
6. 
   
7. SYS@proc> select dbms_utility.data_block_address_file(25165955) from dual;
   
8. 
   
9. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165955)
   
10. ----------------------------------------------
    
11.                                              6
    
12. 
    
13. SYS@proc> select dbms_utility.data_block_address_block(25165955) from dual;
    
14. 
    
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;
   
3. 
   
4. TO_NUMBER('01800080','XXXXXXXX')
   
5. --------------------------------
   
6.                         25165952
   
7. 
   
8. SYS@proc> --10进制25165952转换为16进制
   
9. SYS@proc> select to_char(25165952,'xxxxxxxx') from dual;
   
10. 
    
11. TO_CHAR(2
    
12. ---------
    
13.   1800080
    
14. 
    
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;
    
17. 
    
18.          A          B
    
19. ---------- ----------
    
20.         13          2
    
21. 
    
22. SYS@proc> select bin_to_num(1,1,1,0,1) from dual;
    
23. 
    
24. BIN_TO_NUM(1,1,1,0,1)
    
25. ---------------------
    
26.                    29

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

转载于:http://blog.itpub.net/30174570/viewspace-2140813/