最近在看oracle11g官方的性能优化资料,看到“如果小表在高水位线下的块数小于db_file_multiblock_read_count参数时,全表扫描的代价可能回低于索引范围扫描。”。按照我的理解,对于这种情况全表扫描的I/O只需一次,而此时若走索引范围扫描,I/O却是多次的。对此我做了如下实验,(我预计应该会走全表扫描,可是优化器的执行计划是走索引范围扫描)
1、建立用于测试的临时表(小表)
create table t_1(id number(10),cust_name varchar2(30));
2、插入数据
insert into t_1 select level,'you' from dual connect by level<=50;
commit;
3、建立索引
create index ind_t_1 on t_1(id)
4、收集统计信息
exec dbms_stats.gather_table_stats('TEST','T_1',cascade => true);
5、查看执行计划
explain plan for select * from t_1 where id = 5;
select * from table(dbms_xplan.display());
执行计划如下:
1 Plan hash value: 923245147
2
3 ---------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ---------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
7 | 1 | TABLE ACCESS BY INDEX ROWID| T_1 | 1 | 7 | 2 (0)| 00:00:01 |
8 |* 2 | INDEX RANGE SCAN | IND_T_1 | 1 | | 1 (0)| 00:00:01 |
9 ---------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - access("ID"=5)
此时竟然是走索引扫描,而不是全表扫描,按照我理解走索引扫描要两次I/O,而全表扫描只需一I/O,应该要走全表扫描才对。可是优化器给出的是索引扫描。
为此我做了如下检查:
6、查了下db_file_multiblock_read_count参数,库中的为16
7、表的数据块数
select ut.table_name,ut.blocks,ut.empty_blocks from user_tables ut where ut.table_name = 'T_1'
结果为: TABLE_NAME BLOCKS EMPTY_BLOCKS
T_1 5 0
8、此时,可以发现表中的数据块是小于db_file_multiblock_read_count参数设置的值。
9、对此,我想到是不是由于操作系统的单次I/O的限制而使得产生此执行计划,因此又做了如下实验
测试每次物理读取多少数据库
(1)建立表t_20
create table t_20 as select * from dba_objects;
(2)查看全表扫描前数据文件物理读的次数和块数
select f.phyrds, f.phyblkrd
from v$filestat f
where file# in (select dt.file_id
from dba_data_files dt, dba_tables dt1
where dt.tablespace_name = dt1.tablespace_name
and dt1.owner = 'TEST'
and dt1.table_name = 'T_20')
结果:phyrds phyblkrd
15 15
(3)对t_20表做全表扫描
select count(*) from t_20;
(4)查看执行完后数据文件的物理读次数和物理读块数,执行第(2)语句
结果为:phyrds phyblkrd
97 708
(5)计算每次I/O物理读数据块为:floor((708-15)/(97-15))=8 blocks
10、对于此结果,操作系统每次I/O限制在8个数据块左右,而小表(T_1)中的数据块也就5块,因此,我觉得走全表扫描的的代价应该要低于索引扫描,可是优化器就是走索引范围扫描。
11、我将db_file_multiblock_read_count参数修改为8后,又重新做了次实验,结果也是一样~~~~
请教下各位大侠~~~为什么会产生这种情况呢?