环境：oracle 817 + linux + 阵列柜

swd_billdetail 表5000万条数据

SUPER_USER 表2800条数据

连接列上都有索引，而且super_user中的一条对应于swd_billdetail表中的很多条记录

表与索引都做了分析。

实际应用的查询为：

select a.CHANNEL, B.user_class

from swd_billdetail B, SUPER_USER A

where A.cn = B.cn;

这样在分析时导致查询出的数据过多，不方便，所以用count（a.CHANNEL||B.user_class）来代替，而且count（a.CHANNEL||B.user_class）操作本身并不占用过多的时间，所以可以接受此种替代。

利用索引查询出SWD_BILLDETAIL表中所有记录的方法

SQL> select count（id） from SWD_BILLDETAIL;

COUNT（ID）

----------

53923574

Elapsed: 00:02:166.00

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE （Cost=18051 Card=1）

1 0 SORT （AGGREGATE）

2 1 INDEX （FAST FULL SCAN） OF 'SYS_C001851' （UNIQUE） （Cost=18051 Card=54863946）

Statistics

----------------------------------------------------------

0 recursive calls

1952 db block gets

158776 consistent gets

158779 physical reads

1004 redo size

295 bytes sent via SQL*Net to client

421 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts （memory）

0 sorts （disk）

1 rows processed

利用全表扫描从SWD_BILLDETAIL表中取出全部数据的方法。

SQL> select count（user_class） from swd_billdetail;

COUNT（USER_CLASS）

-----------------

53923574

Elapsed: 00:11:703.07

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE （Cost=165412 Card=1 Bytes=2）

1 0 SORT （AGGREGATE）

2 1 TABLE ACCESS （FULL） OF 'SWD_BILLDETAIL' （Cost=165412 Card=54863946 Bytes=109727892）

Statistics

----------------------------------------------------------

0 recursive calls

8823 db block gets

1431070 consistent gets

1419520 physical reads

0 redo size

303 bytes sent via SQL*Net to client

421 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts （memory）

0 sorts （disk）

1 rows processed

select count（a.CHANNEL||B.user_class）

from swd_billdetail B, SUPER_USER A

where A.cn = B.cn;

EXEC_ORDER PLANLINE

---------- -----------------------------------------------------------------------------------------------------------

6 SELECT STATEMENT OPT_MODE:CHOOSE （COST=108968,CARD=1,BYTES=21）

5 SORT （AGGREGATE） （COST=,CARD=1,BYTES=21）

4 NESTED LOOPS （COST=108968,CARD=1213745,BYTES=25488645）

1 TABLE ACCESS （FULL） OF 'SWORD.SUPER_USER' （COST=2,CARD=2794,BYTES=27940）

3 TABLE ACCESS （BY INDEX ROWID） OF 'SWORD.SWD_BILLDETAIL' （COST=39,CARD=54863946,BYTES=603503406）

2 INDEX （RANGE SCAN） OF 'SWORD.IDX_DETAIL_CN' （NON-UNIQUE） （COST=3,CARD=54863946,BYTES=）

这个查询耗费的时间很长，需要1个多小时。

运行后的信息如下：

COUNT（A.CHANNEL||B.USER_CLASS）

------------------------------

1186387

Elapsed: 01:107:6429.87

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE （Cost=108968 Card=1 Bytes=21）

1 0 SORT （AGGREGATE）

2 1 NESTED LOOPS （Cost=108968 Card=1213745 Bytes=25488645）

3 2 TABLE ACCESS （FULL） OF 'SUPER_USER' （Cost=2 Card=2794Bytes=27940）

4 2 TABLE ACCESS （BY INDEX ROWID） OF 'SWD_BILLDETAIL' （Cost=39 Card=54863946 Bytes=603503406）

5 4 INDEX （RANGE SCAN） OF 'IDX_DETAIL_CN' （NON-UNIQUE） （Cost=3 Card=54863946）

Statistics

----------------------------------------------------------

0 recursive calls

4 db block gets

1196954 consistent gets

1165726 physical reads

0 redo size

316 bytes sent via SQL*Net to client

421 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts （memory）

0 sorts （disk）

1 rows processed

将语句中加入hints，让oracle的优化器使用嵌套循环，并且大表作为驱动表，生成新的执行计划：

select /*+ ORDERED USE_NL（A） */ count（a.CHANNEL||B.user_class）

from swd_billdetail B, SUPER_USER A

where A.cn = B.cn;

EXEC_ORDER PLANLINE

---------- -----------------------------------------------------------------------------------------------------

6 SELECT STATEMENT OPT_MODE:CHOOSE （COST=109893304,CARD=1,BYTES=21）

5 SORT （AGGREGATE） （COST=,CARD=1,BYTES=21）

4 NESTED LOOPS （COST=109893304,CARD=1213745,BYTES=25488645）

1 TABLE ACCESS （FULL） OF 'SWORD.SWD_BILLDETAIL' （COST=165412,CARD=54863946,BYTES=603503406）

3 TABLE ACCESS （BY INDEX ROWID） OF 'SWORD.SUPER_USER' （COST=2,CARD=2794,BYTES=27940）

2 INDEX （RANGE SCAN） OF 'SWORD.IDX_SUPER_USER_CN' （NON-UNIQUE） （COST=1,CARD=2794,BYTES=）

这个查询耗费的时间较短，才20分钟，性能比较好。

运行后的信息如下：

COUNT（A.CHANNEL||B.USER_CLASS）

------------------------------

1186387

Elapsed: 00:20:1208.87

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE （Cost=109893304 Card=1 Bytes=21）

1 0 SORT （AGGREGATE）

2 1 NESTED LOOPS （Cost=109893304 Card=1213745 Bytes=25488645）

3 2 TABLE ACCESS （FULL） OF 'SWD_BILLDETAIL' （Cost=165412 Card=54863946 Bytes=603503406）

4 2 TABLE ACCESS （BY INDEX ROWID） OF 'SUPER_USER' （Cost=2Card=2794 Bytes=27940）

5 4 INDEX （RANGE SCAN） OF 'IDX_SUPER_USER_CN' （NON-UNIQUE） （Cost=1 Card=2794）

Statistics

----------------------------------------------------------

0 recursive calls

8823 db block gets

56650250 consistent gets

1413250 physical reads

0 redo size

316 bytes sent via SQL*Net to client

421 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts （memory）

0 sorts （disk）

1 rows processed


总结：

因为上两个查询都是采用nested loop循环，这时采用哪个表作为driving table就很重要。在第一个sql中，小表（SUPER_USER）作为driving table，符合oracle优化的建议，但是由于SWD_BILLDETAIL表中cn列的值有很多重复的，这样对于SUPER_USER中的每一行，都会在SWD_BILLDETAIL中有很多行，利用索引查询出这些行的rowid很快，但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_class列的值，就比较慢了。原因是这些rowid是随机的，而且该表比较大，不可能缓存到内存，所以几乎每次按照rowid查询都需要读物理磁盘，这就是该执行计划比较慢的真正原因。从结果可以得到验证：查询出1186387行，需要利用rowid从SWD_BILLDETAIL表中读取1186387次，而且大部分为从硬盘上读取。

反其道而行之，利用大表（SWD_BILLDETAIL）作为driving表，这样大表只需要做一次全表扫描（而且会使用多块读功能，每次物理I/O都会读取几个oracle数据块，从而一次读取很多行，加快了执行效率），对于读出的每一行，都与SUPER_USER中的行进行匹配，因为SUPER_USER表很小，所以可以全部放到内存中，这样匹配操作就极快，所以该sql执行的时间与SWD_BILLDETAIL表全表扫描的时间差不多（SWD_BILLDETAIL全表用11分钟，而此查询用20分钟）。

另外：如果SWD_BILLDETAIL表中cn列的值唯一，则第一个sql执行计划执行的结果或许也会不错。如果SUPER_USER表也很大，如500万行，则第2个sql执行计划执行的结果反而又可能会差。其实，如果SUPER_USER表很小，则第2个sql语句的执行计划如果不利用SUPER_USER表的索引，查询或许会更快一些，我没有对此进行测试。

所以在进行性能调整时，具体问题要具体分析，没有一个统一的标准。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html