ORACLE数据库中索引范围扫描是非常常见的扫描方式。
一 创建表和数据
create table zfk select * from dba_objects;
-- Create table
create table ZFK
(
owner VARCHAR2(30),
object_name VARCHAR2(128),
subobject_name VARCHAR2(30),
object_id NUMBER not null,
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE,
last_ddl_time DATE,
timestamp VARCHAR2(19),
status VARCHAR2(7),
temporary VARCHAR2(1),
generated VARCHAR2(1),
secondary VARCHAR2(1),
namespace NUMBER,
edition_name VARCHAR2(30),
cust_last_name VARCHAR2(30)
) pctfree 10
二创建唯一索引
-- Create/Recreate primary, unique and foreign key constraints
alter table ZFK add constraint ZFK_PK_OBJID primary key (OBJECT_ID) pctfree 10 using index ;
三检查信息
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
DATA_OBJECT_ID
120052=>1D4F4
120053=>1D4F5
在SQLPLUS中默认设置下运行下面的语句
select object_id,object_name,object_type from DBA_MONITER.zfk where object_id between 1 and 900;
执行计划
----------------------------------------------------------
Plan hash value: 1077372689
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 659 | 25701 | 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZFK | 659 | 25701 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZFK_PK_OBJID | 659 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=900)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
52 consistent gets
0 physical reads
0 redo size
29485 bytes sent via SQL*Net to client
707 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
883 rows processed
从上面返回信息当中，我们得知返回883行数据，52次逻辑读，发送了29.485KB字节去客户端，来回反复19次网络传输。
SQL> show array
arraysize 50
默认情况下是每50条发送一次给客户端。也就是说50条数据装车发货！
883/50=17次
使用SYS用户使用ORADEBUG工具10200来跟踪逻辑读
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_id between 1 and 900;
已用时间: 00: 00: 00.14
SQL> oradebug event 10200 trace name context off;
已处理的语句
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_38712.trc
下面统计下读块操作的次数, 你看得出0x0001d4f5是叶块,0x0001d4f4是数据块
[oracle@svr3 trace]cat u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_38712.trc |grep "started for block"
ktrgtc2(): started for block <0x0004 : 0x0100021b> objd: 0x0001d4f5
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014b> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014b> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014b> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014c> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014c> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014c> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014d> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014d> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014e> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014e> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014f> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014f> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014f> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000150> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000150> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000151> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000151> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000151> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000152> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000152> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000153> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000153> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000153> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000154> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000154> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000156> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000156> objd: 0x0001d4f4
然后把array值设置成1000
SQL> set array 1000
再运行跟踪,最好是退出重进SQLPLUS
oracle@svr3 trace]cat u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_38728.trc |grep "started for block"
ktrgtc2(): started for block <0x0004 : 0x0100021b> objd: 0x0001d4f5
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014b> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021c> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x0300014b> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014c> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014d> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014e> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x0300014f> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000150> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000151> objd: 0x0001d4f4
ktrget2(): started for block <0x0004 : 0x0100021d> objd: 0x0001d4f5
ktrget2(): started for block <0x000d : 0x03000152> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000153> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000154> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000156> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000155> objd: 0x0001d4f4
ktrget2(): started for block <0x000d : 0x03000156> objd: 0x0001d4f4
发现读块次数少了很多!!
执行计划
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
26374 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
883 rows processed
只有19次逻辑读,2次装货发车,
总结:
1 索引范围扫描方式 是先根节点块=>叶节点块=>表数据块=>叶节点块.....
2 SQLPLUS ARRAY的值 极大地影响了回表次数
3 JAVA语言 如下设置stmt.setFetchSize(100);
4 范围扫描是单块读取,可以从等待事件上可知
5 太多的逻辑读次数会消耗很多CPU的时间.
