Oracle中ASSM模式下,全表掃描的L3塊的邏輯讀的影響

PiscesCanon發表於2017-06-15
OracleSSM模式
前言：
本問題由之前的實驗擴充遺留的一些疑問，詳見: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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2140813/，如需轉載，請註明出處，否則將追究法律責任。

相關文章