解析《提高Oracle性能--经验实谈》一文

      在6月24日，这天我写了《提高Oracle性能--经验实谈》http://rdqwhr.iteye.com/blog/207320这篇文章，一直想争得JAVAEYE的Oracle专家给予解答。作为技术上的问题受多方面的因素制约，比如说机器的软硬件环境，再加上Oracle中是否使用到视图、同义词等。迫于给大家一个完整的解释，我做了多方面的测试，以供参考（注：以下数据建立在多次结果的平均值上）。

 

      我的机器运行环境是：Pentium D 2.8, 1G RAM, 160G 5400转硬盘；Oracle9i.对于该SQL文所用到的表未使用视图、同义词和序列。

 

      大家从我给出的SQL文中不难看出，我将原SQL文的第二个检索条件

(A.ZK_SK_CD='1'    
AND A.ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD')    
AND DN_DY<TO_DATE('20080601',    
'YYYYMMDD'))    
OR (A.ZK_SK_CD='1'    
AND A.DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD')    
AND TO_DATE('20080623','YYYYMMDD'))    

      作了变更。因为这段SQL文慢就慢在这里。

      于是，我就开始做了第一个测试：我将我数据库中的数据删减至4000条以内（这是一个比较值得参考的数据，验证所得），整个SQL的执行效率就变得非常高了，速度可以控制在12S以内。在这里可以说明一点，数据库中数据记录过多的话，OR的执行效率明显降低，更何况我的库里有超15万条得记录。

 

      接着我又作了第二个测试：不改动SQL文，直接添加如下索引，里面包括查询、计算、分组等所有列（在对日开发中，要增加一个索引非常麻法，还得经过层层的申请；而日方已在我的障害要领书上注明，最好更改SQL文。）

CREATE INDEX KMJ.TRNSCTNFK16
ON KMJ.TRNSCTN
(HNSHTN_CD, ZK_SK_CD, ZK_KSHN_DY, DN_DY, DN_GYBN_KB, DN_KB, GY_NB, KKR_KB)
PCTFREE 20
INITRANS 2
MAXTRANS 255
TABLESPACE KMJDB_IDX
STORAGE(INITIAL 16K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
/

      此时，SQL的性能也有较大的改变，性能提高到了20s左右。毕竟此方法在外不得已的情况下是不会被日方所采纳的，因此行不通。所以我只有在OR关键字上下一番功夫或许能够进一步优化此SQL语句了。

 

      紧接着，我就进行了第三次测试：想来想去只能用UNION ALL(UNION)了。由于原SQL文涉及到分组计算，不便在WHERE关键字之后进行UNION或UNION ALL的操作。我将这两个条件作为两个查询，将得到的两张表UNION ALL（从这两个条件可以看出，检索出来不可能有重复的记录）成一张虚表。这样有一个好处：因为这样一张表将位于高速缓存区中，提高了后面主查询的效率。照这样修改之后整条SQL语句的执行时间也超不过9S。

 

      通过以上的测试说明两个问题：

      一是OR在少量的数据中执行效率确实比较高，一旦数据接近四千条以后，性能明显地降低，有必要考虑UNION或者UNION ALL；

      二是在海量数据的查询中，仅仅依靠索引并不一定是最佳的优化方案，视具体情况而定。