今天下午朋友QQ给我说他遇到一个SQL相关的性能问题，执行查询相当…的慢，发SQL发过来之后，发现跟上次让我看的是一样的，上次调整过一次，似乎效果不是很理想…，进入正题。

select * from
(select * from U_MSG
where MS='DELIVRD'
and SA = '1066898829'
and ICP_ID='91188'
and SCTS>'2011-02-01'
and SIGN is null ) C
where rownum=1;

告诉朋友如何收集执行计划，发过来的结果为：

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=217)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'U_MSG' (Cost=4 Card=1 Bytes=217)
3 2 INDEX (RANGE SCAN) OF 'INDEX_NEW_SCTS' (NON-UNIQUE) (Cost=3 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
128746 consistent gets
28245 physical reads
0 redo size
2451 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

1 rows processed

从autotrace的结果可以看出，在SQL执行时，走的索引是INDEX_NEW_SCTS，且有相当严重的逻辑读和物理读，我们也知道，大量的物理读势必会影响到系统的性能。

通过观察SQL语句和让他发过来的表结构明细，了解到列MS、SA、ICP_ID、SCTS均是varchar2类型。然后看了下MS列的选择性：

MS COUNT(*)
------------ ----------
DB:0101 791
DB:0102 721
DB:0107 33
DB:0115 9309
DB:0116 9
DB:0182 35
DELIVRD 268197
EXPIRED 3532
MB:1042 4
MC:0055 5
MI:0000 202
MI:0057 4
MK:0000 151
MK:0029 2
MS_MO 213517
MS_MT 298401
REJECTD 47723
UNDELIV 153652
可以看出在整张表中，MS的distinct值不是很多，但每个值的重复度不一样，甚至差距较大，这种情况下，可以考虑采用直方图，步骤如下：

在MS列创建索引：
SQL>create index GJPT.U_MSG_1IX on GJPT.U_MSG(ms) tablespace idx_tbs;

收集列MS上的直方图统计信息：
SQL>exec dbms_stats.gather_table_stats('GJPT','U_MSG',cascade=>true,method_opt => 'for columns MS size 10');

做完以上两步之后，再次看执行计划时发现执行计划没有任何变化，汗…

思考一会之后，决定把整张表重新analyze下，方法如下：
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'GJPT',
3 tabname=>'U_MSG',
4 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
5 cascade=>true);
6 end;
7 /
PL/SQL 过程已成功完成。

分析完之后，再此看执行计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36.58368284467 Card=1
ytes=2280)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'U_MSG' (Cost=36.583682 84467 Card=10 Bytes=2280)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP AND
5 4 BITMAP CONVERSION (FROM ROWIDS)
6 5 INDEX (RANGE SCAN) OF 'INDEX_NEW_SA' (NON-UNIQUE) Cost=6 Card=828)
7 4 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'U_MSG_1IX' (NON-UNIQUE)
Cost=230 Card=828)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10253 consistent gets
0 physical reads
0 redo size
2457 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

从这个执行计划可以看出，physical reads降至0，consistent gets从之前的128746降至10253，其实10253这个值也不算小，实际环境中OS的内存和DB_CACHE_SIZE设置的也都比较小，如果加大sga，还可以更好的改善性能，更重要的是执行时间从原来的十余分钟降至0.2毫秒左右。

--- END ---