关注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的命中而不断变化，而逻辑读在多次反复执行后基本保持不变

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来的
所以，索引访问并不总是比全表扫描快

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%的测试

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走索引

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秒