概述
前言：
本问题由之前的实验拓展遗留的一些疑问，详见: http://blog.itpub.net/30174570/viewspace-2140241/ 。
排除db_file_multiblock_read_count参数和动态采样(详见: http://blog.itpub.net/30174570/viewspace-2140240/ )的影响。
操作系统环境：
数据库版本：
构造测试数据：
引出问题：
sql语句"select count(*) from t where rownum<=171"只读取了131,132两个块，但是130也被读取进去，这里读取该块的作用是什么？
实验过程：
上述实验过程第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信息：
其他拓展：
本问题由之前的实验拓展遗留的一些疑问，详见: http://blog.itpub.net/30174570/viewspace-2140241/ 。
排除db_file_multiblock_read_count参数和动态采样(详见: http://blog.itpub.net/30174570/viewspace-2140240/ )的影响。
操作系统环境：
- [oracle@oracle ~]$ uname -a
- 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
- [oracle@oracle ~]$ lsb_release -a
- 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
- Distributor ID: RedHatEnterpriseServer
- Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
- Release: 6.5
- Codename: Santiago
数据库版本：
- SYS@proc> select * from v$version where rownum=1;
-
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
构造测试数据：
- SYS@proc> drop table t purge;
-
- Table dropped.
-
- SYS@proc> create table t as select * from dba_objects where rownum<=1200;
-
- Table created.
-
- SYS@proc> alter table t move tablespace test;
-
- Table altered.
-
- 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);
-
- BLOCK# MIN(ROWNUM) MAX(ROWNUM)
- ---------- ----------- -----------
- 131 1 88
- 132 89 171
- 133 172 251
- 134 252 329
- 135 330 407
- 136 408 487
- 137 488 567
- 138 568 646
- 139 647 724
- 140 725 798
- 141 799 873
-
- BLOCK# MIN(ROWNUM) MAX(ROWNUM)
- ---------- ----------- -----------
- 142 874 946
- 143 947 1022
- 145 1023 1104
- 146 1105 1179
- 147 1180 1200
-
- 16 rows selected.
-
- SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='T';
-
- EXTENT_ID FILE_ID BLOCK_ID BLOCKS
- ---------- ---------- ---------- ----------
- 0 6 128 8 --128 129 130 131 132 133 134 135
- 1 6 136 8
- 2 6 144 8
引出问题：
- SYS@proc> alter system set db_file_multiblock_read_count=1; --避免该参数的影响
-
- System altered.
-
- SYS@proc> show parameter db_file_multiblock_read_count
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_file_multiblock_read_count integer 1
-
- SYS@proc> analyze table t compute statistics; --避免动态采样的影响
-
- Table analyzed.
-
- SYS@proc> alter system flush buffer_cache;
-
- System altered.
-
- 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;
-
- no rows selected
-
- SYS@proc> select count(*) from t where rownum<=171;
-
- COUNT(*)
- ----------
- 171
-
- 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;
-
- FILE# DBABLK STATE
- ---------- ---------- ----------
- 6 130 1
- 6 131 1
- 6 132 1
实验过程：
- SYS@proc> alter system flush buffer_cache;
-
- System altered.
-
- 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;
-
- no rows selected
-
- SYS@proc> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='SYS' and segment_name='T'; --该语句可确定段头块是130
-
- HEADER_FILE HEADER_BLOCK
- ----------- ------------
- 6 130
-
- 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;
-
- no rows selected
-
- SYS@proc> select count(*) from t where rownum<=171;
-
- COUNT(*)
- ----------
- 171
-
- SYS@proc> set autotrace on
- SYS@proc> select count(*) from t where rownum<=171;
-
- COUNT(*)
- ----------
- 171
-
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 239743108
-
- --------------------------------------------------------------------
- | Id | Operation | Name | Rows | Cost (%CPU)| Time |
- --------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 21 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | | |
- |* 2 | COUNT STOPKEY | | | | |
- | 3 | TABLE ACCESS FULL| T | 1200 | 21 (0)| 00:00:01 |
- --------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - filter(ROWNUM<=171)
-
-
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 4 consistent gets
- 0 physical reads
- 0 redo size
- 527 bytes sent via SQL*Net to client
- 523 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
-
- SYS@proc> set autotrace off
- 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;
-
- FILE# DBABLK STATE
- ---------- ---------- ----------
- 6 130 1
- 6 131 1
- 6 132 1
为什么该处的逻辑读是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信息：
- Extent Control Header
- -----------------------------------------------------------------
- Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 24
- last map 0x00000000 #maps: 0 offset: 2716
- Highwater:: 0x01800094 ext#: 2 blk#: 4 ext size: 8
- #blocks in seg. hdr's freelists: 0
- #blocks below: 20
- mapblk 0x00000000 offset: 2
- Unlocked
- --------------------------------------------------------
- Low HighWater Mark :
- Highwater:: 0x01800094 ext#: 2 blk#: 4 ext size: 8
- #blocks in seg. hdr's freelists: 0
- #blocks below: 20
- mapblk 0x00000000 offset: 2
- Level 1 BMB for High HWM block: 0x01800090
- Level 1 BMB for Low HWM block: 0x01800090
- --------------------------------------------------------
- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
- L2 Array start offset: 0x00001434
- First Level 3 BMB: 0x00000000
- L2 Hint for inserts: 0x01800081
- Last Level 1 BMB: 0x01800090
- Last Level II BMB: 0x01800081
- Last Level III BMB: 0x00000000
- Map Header:: next 0x00000000 #extents: 3 obj#: 89405 flag: 0x10000000
- Inc # 0
- Extent Map
- -----------------------------------------------------------------
- 0x01800080 length: 8
- 0x01800088 length: 8
- 0x01800090 length: 8
-
- Auxillary Map
- --------------------------------------------------------
- Extent 0 : L1 dba: 0x01800080 Data dba: 0x01800083 "0x01800083"->二进制：00000001 10000000 00000000 10000011
- Extent 1 : L1 dba: 0x01800080 Data dba: 0x01800088 前10位是文件号，后22位是块号，0000000110->文件号：6，000000 00000000 10000011->数据块：131
- Extent 2 : L1 dba: 0x01800090 Data dba: 0x01800091
- --------------------------------------------------------
-
- Second Level Bitmap block DBAs
- --------------------------------------------------------
- DBA 1: 0x01800081
-
- End dump data blocks tsn: 9 file#: 6 minblk 130 maxblk 130
- SYS@proc> select to_number('01800083','xxxxxxxx') from dual;
-
- TO_NUMBER('01800083','XXXXXXXX')
- --------------------------------
- 25165955
-
- SYS@proc> select dbms_utility.data_block_address_file(25165955) from dual;
-
- DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165955)
- ----------------------------------------------
- 6
-
- SYS@proc> select dbms_utility.data_block_address_block(25165955) from dual;
-
- DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(25165955)
- -----------------------------------------------
- 131
其他拓展：
- SYS@proc> --16进制数0x01800080转换为10进制数
- SYS@proc> select to_number('01800080','xxxxxxxx') from dual;
-
- TO_NUMBER('01800080','XXXXXXXX')
- --------------------------------
- 25165952
-
- SYS@proc> --10进制25165952转换为16进制
- SYS@proc> select to_char(25165952,'xxxxxxxx') from dual;
-
- TO_CHAR(2
- ---------
- 1800080
-
- SYS@proc> --2进制转换为10进制
- SYS@proc> select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual;
-
- A B
- ---------- ----------
- 13 2
-
- SYS@proc> select bin_to_num(1,1,1,0,1) from dual;
-
- BIN_TO_NUM(1,1,1,0,1)
- ---------------------
- 29
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30174570/viewspace-2140813/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30174570/viewspace-2140813/
最后
以上就是落寞裙子为你收集整理的Oracle中ASSM模式下,全表扫描的L3块的逻辑读的影响的全部内容，希望文章能够帮你解决Oracle中ASSM模式下,全表扫描的L3块的逻辑读的影响所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复