关注SQL执行计划中的两个比率环境：www.2cto.com[sql]sys@ORCL>select*fromv$version;BANNER----------------------------------------------------------------OracleDatabase10gEnter
关注SQL执行计划中的两个比率
环境： www.2cto.com
[sql]
sys@ORCL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for
Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
㈠ 返回行与逻辑读的比率
每获取一行开销5个以下的逻辑读是大致可接受范围
物理读会随着buffer cache的命中而不断变化，而逻辑读在多次反复执行后基本保持不变
[sql]
hr@ORCL> drop table t;
hr@ORCL> create table t as select * from dba_objects;
hr@ORCL> insert into t select * from t;
hr@ORCL> insert into t select * from t;
hr@ORCL> insert into t select * from t;
hr@ORCL> insert into t select * from t;
hr@ORCL> insert into t select * from t;
hr@ORCL> analyze table t compute statistics for table for all indexes for all indexed columns;
Table analyzed.
hr@ORCL> select count(*) from t;
COUNT(*)
----------
1611392
hr@ORCL> set autot traceonly
hr@ORCL> set timing on
hr@ORCL> select * from t where object_id<101;
3168 rows selected.
Elapsed: 00:00:00.62
Execution 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来的
所以，索引访问并不总是比全表扫描快
[sql]
hr@ORCL> select count(*) from t;
COUNT(*)
----------
1611392
hr@ORCL> create index ind_t on t (object_id);
hr@ORCL> alter table t modify object_id number not null;
下面是返回行和记录总数的比率为50%的测试
[sql]
hr@ORCL> select * from t where rownum<800000;
799999 rows selected.
Elapsed: 00:00:45.74
Execution 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走索引
[sql]
hr@ORCL> select /*+ index(t ind_t)*/* from t where rownum<800000;
799999 rows selected.
Elapsed: 00:00:52.48
Execution 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秒.
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 