[20170209]索引范围访问2.txt

news/2024/12/6 18:39:44/

[20170209]索引范围访问2.txt

--ITPUB网友问的问题:

http://www.itpub.net/thread-2083504-1-1.html
--索引范围扫描是如何访问数据块的?
1 FOR  (根节点-> 分支节点->叶节点->表) 这循环吗?
2 还是(根节点-> 分支节点->叶节点->叶节点->叶节点->叶节点->表)?
3 还是 (根节点-> 分支节点->叶节点->表->叶节点->表->叶节点->表->叶节点->表)?

--我认为是3,还是测试来说明问题:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t (id number,v1 varchar2(20),v2 varchar2(4000),v3 varchar2(3000));
insert into t as select rownum ,rownum||'aaaa',lpad('a',4000,'a'),lpad('b',3000,'b') from dual connect by level<=200;
commit ;
create unique index i_t_id on t(id) pctfree 80;
--分析略.

SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where file_id=4 and segment_name in ('T','I_T_ID');
OWNER  SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- --------- ------- -------- ------- ------ ------------
SCOTT  I_T_ID       INDEX        USERS                   0       4     1200   65536      8            4
SCOTT  T            TABLE        USERS                   0       4      816   65536      8            4
SCOTT  T            TABLE        USERS                   1       4      824   65536      8            4
SCOTT  T            TABLE        USERS                   2       4      832   65536      8            4
SCOTT  T            TABLE        USERS                   3       4      840   65536      8            4
SCOTT  T            TABLE        USERS                   4       4      848   65536      8            4
SCOTT  T            TABLE        USERS                   5       4      856   65536      8            4
SCOTT  T            TABLE        USERS                   6       4      864   65536      8            4
SCOTT  T            TABLE        USERS                   7       4      872   65536      8            4
SCOTT  T            TABLE        USERS                   8       4      880   65536      8            4
SCOTT  T            TABLE        USERS                   9       4      888   65536      8            4
SCOTT  T            TABLE        USERS                  10       4     1152   65536      8            4
SCOTT  T            TABLE        USERS                  11       4     1160   65536      8            4
SCOTT  T            TABLE        USERS                  12       4     1168   65536      8            4
SCOTT  T            TABLE        USERS                  13       4     1176   65536      8            4
SCOTT  T            TABLE        USERS                  14       4     1184   65536      8            4
SCOTT  T            TABLE        USERS                  15       4     1192   65536      8            4
SCOTT  T            TABLE        USERS                  16       4     1280 1048576    128            4
18 rows selected.

SCOTT@book> select object_name,object_id,data_object_id from dba_objects where owner=user and  object_name in ('T','I_T_ID');
OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
I_T_ID                    89842          89842   <==0x15ef2
T                         89841          89841   <==0x15ef1

SCOTT@book> select segment_name,header_file,header_block from dba_segments where segment_name in ('T','I_T_ID');
SEGMENT_NAME         HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
T                              4          818
I_T_ID                         4         1202

--这样基本1条记录1块.

SCOTT@book> alter session set events 'immediate trace name treedump level 89842';
Session altered.

*** 2017-02-09 15:02:47.723
branch: 0x10004b3 16778419 (0: nrow: 2, level: 1)          => dba=4,1203
   leaf: 0x10004b4 16778420 (-1: nrow: 111 rrow: 111)      => dba=4,1204
   leaf: 0x10004b5 16778421 (0: nrow: 89 rrow: 89)         => dba=4,1205
----- end tree dump
--//索引记录分部在2块.

2.测试:
SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> show arraysize
arraysize 200

SCOTT@book> select /*+ index( t i_t_id ) */ id,v1 from t where id between 101 and 120;
ID V1
--- --------------------
101 101aaaa
102 102aaaa
103 103aaaa
104 104aaaa
105 105aaaa
106 106aaaa
107 107aaaa
108 108aaaa
109 109aaaa
110 110aaaa
111 111aaaa
112 112aaaa
113 113aaaa
114 114aaaa
115 115aaaa
116 116aaaa
117 117aaaa
118 118aaaa
119 119aaaa
120 120aaaa
20 rows selected.

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cx4u60mg16ksy, child number 0
-------------------------------------
select /*+ index( t i_t_id ) */ id,v1 from t where id between 101 and 120
Plan hash value: 4153437776
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |    24 (100)|          |     20 |00:00:00.01 |      24 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     21 |   252 |    24   (0)| 00:00:01 |     20 |00:00:00.01 |      24 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     21 |       |     2   (0)| 00:00:01 |     20 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=101 AND "ID"<=120)
26 rows selected.
--//逻辑读是24. (arraysize=200)

SCOTT@book> set array 2
SCOTT@book> select /*+ index( t i_t_id ) */ id,v1 from t where id between 101 and 120;
...
Plan hash value: 4153437776
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |    24 (100)|          |     20 |00:00:00.01 |      32 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     21 |   252 |    24   (0)| 00:00:01 |     20 |00:00:00.01 |      32 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     21 |       |     2   (0)| 00:00:01 |     20 |00:00:00.01 |      12 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=101 AND "ID"<=120)
--//逻辑读是24. (arraysize=2)

3.跟踪看看:
--//如何跟踪逻辑读,想了N久才想起来10200事件.
$ oerr ora 10200
10200, 00000, "consistent read buffer status"
// *Cause:
// *Action:

SCOTT@book> ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.

SCOTT@book> select /*+ index( t i_t_id ) */ id,v1 from t where id between 101 and 120;
        ID V1
---------- --------------------
       101 101aaaa
...
       120 120aaaa

20 rows selected.

SCOTT@book> ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT OFF';
Session altered.

--//检查转储:
ktrgtc2(): started for block <0x0004 : 0x010004b3> objd: 0x00015ef2
  env [0x7fadbb8f6fdc]: (scn: 0x0003.175073c2  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0003.1750734b  flg: 0x00000661)
ktrexc(): returning 2 on:  0xc0f4928  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0004 : 0x010004b3> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2
env [0x7fadbb8f6fdc]: (scn: 0x0003.175073c2  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0003.1750734b  flg: 0x00000660)
ktrexf(): returning 9 on:  0xc0f4928  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0004 : 0x010004b4> objd: 0x00015ef2
ktrget3(): completed for  block <0x0004 : 0x010004b4> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x0100049f> objd: 0x00015ef1

$ grep "started" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_53991.trc|wc
     32     288    2207
--//正好对上.

$ grep "started" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_53991.trc
ktrgtc2(): started for block <0x0004 : 0x010004b3> objd:  0x00015ef2    <= dba=4,1203,索引的根节点块
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2    <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x0100049f> objd: 0x00015ef1    <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2    <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x01000498> objd: 0x00015ef1    <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x01000499> objd: 0x00015ef1    <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2    <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004a6> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004a7> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004a1> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004a4> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004a5> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004a2> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004a3> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004aa> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004ab> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004ac> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004ad> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004ae> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004af> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004a8> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x010004a9> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x0100055a> objd: 0x00015ef1
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2
ktrget2(): started for block  <0x0004 : 0x0100055e> objd: 0x00015ef1

--//仔细看后面的规律都是
--//先访问0x00015ef2对象一次,再访问0x00015ef1对象2次.我注解在后面.
--//你可以注意一个小细节,第一次读表是读一条记录.可以看看我以前写的帖子:
http://blog.itpub.net/267265/viewspace-1430902/

--//设置为3重复测试看看.
SCOTT@book> set array 3
SCOTT@book> ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';
SCOTT@book> select /*+ index( t i_t_id ) */ id,v1 from t where id between 101 and 120;
Plan hash value: 4153437776
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |    24 (100)|          |     20 |00:00:00.01 |      30 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     21 |   252 |    24   (0)| 00:00:01 |     20 |00:00:00.01 |      30 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     21 |       |     2   (0)| 00:00:01 |     20 |00:00:00.01 |      10 |
--------------------------------------------------------------------------------------------------------------------------------
SCOTT@book> ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT OFF';

$ grep "started" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54066.trc|wc
     30     270    2069
--//也执行计划看到的逻辑读一致.

$ grep "started" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54066.trc
ktrgtc2(): started for block <0x0004 : 0x010004b3> objd: 0x00015ef2       <= dba=4,1203,索引的根节点块
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2      <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x0100049f> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2      <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x01000498> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x01000499> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004a6> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2      <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004a7> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004a1> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004a4> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2      <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004a5> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004a2> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004a3> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b4> objd: 0x00015ef2      <= dba=4,1204,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004aa> objd: 0x00015ef1      <= 读表段一条记录 ,注意这里仅仅剩下1条记录. 也就是前面分支仅仅11个键值.
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2      <= dba=4,1205,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004ab> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004ac> objd: 0x00015ef1      <= 读表段一条记录,注意这里仅仅访问2条,因为arraysize=3.
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2      <= dba=4,1205,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004ad> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004ae> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004af> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2      <= dba=4,1205,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x010004a8> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004a9> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x0100055a> objd: 0x00015ef1      <= 读表段一条记录
ktrget2(): started for block  <0x0004 : 0x010004b5> objd: 0x00015ef2      <= dba=4,1205,索引的叶子块
ktrget2(): started for block  <0x0004 : 0x0100055e> objd: 0x00015ef1      <= 读表段一条记录


http://www.ppmy.cn/news/192184.html

相关文章

TMS320F28335之外部中断和自定义中断

外部中断 外部中断和其他模块中断中断原理一样&#xff0c;也是先设置中断级&#xff0c;然后PIE级&#xff0c;最后CPU级&#xff0c;外部中断的中断寄存器在中断头文件中&#xff0c;中断触发设置在中断C文件中&#xff0c;外部中断作为一个单独的模块使用。28335支持8 个可…

写给音响工程师的科普文章(一)——从模电角度去了解调音台的信号流程

很多自称做了多年调音师的音响工程师&#xff0c;由于没有模电基础&#xff0c;外加网络教程千篇一律&#xff0c;说法不一&#xff0c;并没有从根本上去解释一些很常见的问题&#xff0c;比如说最简单的&#xff0c;动圈话筒线1和3要不要短接&#xff0c;DI盒有没有必要使用&a…

写给音响工程师的科普文章(二)——从模电角度去了解调音台话放

上一篇讲到调音台第一级放大电路为差分放大器&#xff0c;关于差分放大器的基础知识可以参考以下资料&#xff1a;https://wenku.baidu.com/view/e6a46aaa2af90242a995e537 在教材中的差分放大器基本形式是这样子的&#xff0c;在实际电路中把RE电阻换成两个2倍RE&#xff0c;…

magento head.phtml 加载a target=_parent

<script language"javascript">function alinks(){ var links document.getElementsByTagName(a); for(var i0; i<links.length; i ){ links[i].target "_parent"; }}window.onload alinks;</script> 添加风控代码 /app/design/fron…

中间件

中间件&#xff08;Middleware&#xff09;是处于操作系统和应用程序之间的软件&#xff0c;也有人认为它应该属于操作系统中的一部分。人们在使用中间件时&#xff0c;往往是一组中间件集成在一起&#xff0c;构成一个平台(包括开发平台和运行平台)&#xff0c;但在这组中间件…

kvm

kvm部署 环境说明&#xff1a; 系统类型IPRHEL8192.168.236.131 kvm安装 部署前请确保你的CPU虚拟化功能已开启。分为两种情况&#xff1a; 虚拟机要关机设置CPU虚拟化 物理机要在BIOS里开启CPU虚拟化 yum install -y https://mirrors.aliyun.com/epel/epel-release-latest…

算法设计与分析_《算法设计与分析》(2017年8月版)修订

2019.11.20 注 新版的一些错误修订&#xff0c;旧版中也存在&#xff0c;就不再copy过来了。大家同时留意新版的修订记录。 2019.04.16 注 大家在版权页看到“版次&#xff1a;2019年3月第1版第2次印刷”&#xff0c;即为重印后的新版。重印的版本中&#xff0c;下面的错误都已…

oracle for扫描行,请教索引范围扫描具体IO行为?

本帖最后由 lfree 于 2017-2-9 16:04 编辑 --测试随手写的,不知道是否正确. [20170209]索引范围访问2.txt --ITPUB网友问的问题: --索引范围扫描是如何访问数据块的? 1 FOR (根节点-> 分支节点->叶节点->表) 这循环吗? 2 还是(根节点-> 分支节点->叶节点->…