[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 <= 读表段一条记录
