环境：
[email protected]> select * from v$version;BANNER----------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
㈠ 返回行与逻辑读的比率
每获取一行开销5个以下的逻辑读是大致可接受范围
物理读会随着buffer cache的命中而不断变化，而逻辑读在多次反复执行后基本保持不变
[email protected]> drop table t;[email protected]> create table t as select * from dba_objects;[email protected]> insert into t select * from t;[email protected]> insert into t select * from t;[email protected]> insert into t select * from t;[email protected]> insert into t select * from t;[email protected]> insert into t select * from t;[email protected]> analyze table t compute statistics for table for all indexes for all indexed columns;Table analyzed.[email protected]> select count(*) from t; COUNT(*)---------- 1611392[email protected]> set autot traceonly[email protected]> set timing on[email protected]> select * from t where object_id<101;3168 rows selected.Elapsed: 00:00:00.62Execution Plan----------------------Plan hash value: 1601196873--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 80570 | 7632K| 4948 (2)| 00:01:00 ||* 1 | TABLE ACCESS FULL| T | 80570 | 7632K| 4948 (2)| 00:01:00 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID"<101)Statistics---------------------- 0 recursive calls 0 db block gets 22431 consistent gets 0 physical reads 0 redo size 111535 bytes sent via SQL*Net to client 2706 bytes received via SQL*Net from client 213 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3168 rows processed
返回行：3168
逻辑读：22431
比率 ：7
也就是平均每获取一行开销7个逻辑读，还有优化的空间
㈡ 返回行与记录总数的比率
有这样一个理论，叫QUBE，大致是讲：
当返回行和记录总数的比值大于20%时，就一定要使用全表扫描
而在0.1%~20%之间，可以自行取舍
Oracle的CBO在比较索引访问和全表扫描的成本时，也基本是基于QUBE来的
所以，索引访问并不总是比全表扫描快
[email protected]> select count(*) from t; COUNT(*)---------- 1611392[email protected]> create index ind_t on t (object_id);[email protected]> alter table t modify object_id number not null;
下面是返回行和记录总数的比率为50%的测试
[email protected]> select * from t where rownum<800000;799999 rows selected.Elapsed: 00:00:45.74Execution Plan----------------------Plan hash value: 508354683---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------| 0 | SELECT STATEMENT | | 799K| 74M| 2471 (3)| 00:00:30 ||* 1 | COUNT STOPKEY | | | | | || 2 | TABLE ACCESS FULL| T | 799K| 74M| 2471 (3)| 00:00:30 |---------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<800000)Statistics---------------------- 1 recursive calls 0 db block gets 63568 consistent gets 2309 physical reads 0 redo size 85856864 bytes sent via SQL*Net to client 587048 bytes received via SQL*Net from client 53335 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 799999 rows processed
Oracle CBO根据QUBE，采用TABLE ACCESS FULL的访问路径，代价cost=4942，执行时间为45.74秒
下面强制让CBO走索引
[email protected]> select /*+ index(t ind_t)*/* from t where rownum<800000; 799999 rows selected.Elapsed: 00:00:52.48Execution Plan----------------------Plan hash value: 1019664585--------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------| 0 | SELECT STATEMENT | | 799K| 74M| 802K (1)| 02:40:32 ||* 1 | COUNT STOPKEY | | | | | || 2 | TABLE ACCESS BY INDEX ROWID| T | 799K| 74M| 802K (1)| 02:40:32 || 3 | INDEX FULL SCAN | IND_T | 1611K| | 1790 (2)| 00:00:22 |--------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<800000)Statistics---------------------- 1 recursive calls 0 db block gets 854966 consistent gets 795 physical reads 0 redo size 84677684 bytes sent via SQL*Net to client 587048 bytes received via SQL*Net from client 53335 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 799999 rows processed
这次走INDEX FULL SCAN，代价cost=1604k+1790，执行时间是52.48秒