索引范围扫描

上篇说的是唯一扫描

我们继续在上面的表和唯一索引上做DEBUG

三检查信息
1 对象ID
select object_name,object_id,data_object_id from dba_objects where owner=user and object_name in (‘ZFK’,’ZFK_PK_OBJID’);
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
ZFK 119734 120052
ZFK_PK_OBJID 120051 120053
OBJECT_ID 十六进制
119734=>1D3B6
120051=>1D4F3
120052=>1D4F4 =>0x0001d4f4
120053=>1D4F5 =>0x0001d4f5
SQL> show array
arraysize 50

select object_id,object_name,object_type from DBA_MONITER.zfk where object_type='INDEX' and OWNER!='SYS' and object_id between 1 and 9999;

已选择217行。
已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 1077372689
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    94 |  4230 |   142   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ZFK      |    94 |  4230 |   142   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | ZFK_PK_OBJID |  7325 |       |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX' AND "OWNER"<>'SYS')
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=9999)
统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
183  consistent gets
      0  physical reads
      0  redo size
       8138  bytes sent via SQL*Net to client
    564  bytes received via SQL*Net from client
      6  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    217  rows processed

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid;
已处理的语句
SQL> oradebug event 10200 trace name context forever,level 1;
已处理的语句
SQL> select object_id,object_name,object_type from DBA_MONITER.zfk where object_type='INDEX' and OWNER!='SYS' and object_id between 1 and 9999; 
    已选择217行。

已用时间:  00: 00: 00.05
SQL> oradebug event 10200 trace name context off;
已处理的语句
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_123822.trc

统计stared 关键字 发现183个 这个就是逻辑读
[oracle@svr3 trace]grep “started” orcl_ora_123822.trc |wc
183 1647 12626
* 2017-03-06 16:53:45.494
Processing Oradebug command ‘event 10200 trace name context forever,level 1’
* 2017-03-06 16:53:45.501
Oradebug command ‘event 10200 trace name context forever,level 1’ console output:
* 2017-03-06 16:53:55.041
ktrgtc2(): started for block <0x0004 : 0x0100021b> objd: 0x0001d4f5
读主键根块
ktrgtc2(): completed for block <0x0004 : 0x0100021b> objd: 0x0001d4f5

ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
读主键索引的叶块
ktrget3(): completed for block <0x0004 : 0x0100021c> objd: 0x0001d4f5

ktrget2(): started for block <0x000d : 0x0300014b> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x0300014b> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x0300014c> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x0300014c> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x0300014d> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x0300014d> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x0300014e> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x0300014e> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x0300014f> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x0300014f> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000150> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000150> objd: 0x0001d4f4

ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
读主键索引的叶块
ktrget3(): completed for block <0x0004 : 0x0100021c> objd: 0x0001d4f5

ktrget2(): started for block <0x000d : 0x03000150> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000150> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000151> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000151> objd: 0x0001d4f4

ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
读主键索引的叶块
ktrget3(): completed for block <0x0004 : 0x0100021d> objd: 0x0001d4f5

ktrget2(): started for block <0x000d : 0x03000152> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000152> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000153> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000153> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000154> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000154> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000155> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000156> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000156> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000155> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000156> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000156> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000157> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000157> objd: 0x0001d4f4

ktrget2(): started for block <0x000d : 0x03000159> objd: 0x0001d4f4
读数据块
ktrget3(): completed for block <0x000d : 0x03000159> objd: 0x0001d4f4

基本上是读个索引块后,就去读表块,接着继续访问索引块.

所以我们基本确定索引范围扫描基本动作是: 索引根节点->叶节点->表块->叶节点->表块………………..

所以聚集因子在这里深刻影响着. 所谓聚集因子就是表的数据存放的无序程度和索引的有序程度的对比值. 说明要重复读取相同的数据块的次数问题。会导致性能下降！！

有人说 当array size 影响着逻辑读, 影响回表的次数。我这次实验把
SQL> set array 300 设置成300后 也发现读表是没什么规律的，还有发现读了两次相同的索引叶块。
请关注公众号: