oracle sql优化案例2（RBO下调整表连接的顺序）

发现问题：

通过statspack（statspack的bug导致buffer gets为负值，部分top sql无法正确列出），经观察，由于两个数据库同在一台主机，c数据库也消耗了主机1/4的CPU资源，也存在着未优化的sql语句，具体如下:该语句从2012年1月13日凌晨开始出现，消耗了C数据库80%的资源，即整个主机15%的CPU。需要提前说明一，由于历史原因，该数据库使用RBO。

CPU Elapsd

Buffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value

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

3,968,965,486 26914,754,518.5 81.9 ######## 36067.8626012873

Module: JDBC ConnectClient

select distinct a.transactionid, a.tansactioncode, a.brand,a.m

sisdn, a.new_msisdn,a.user_status, a.status_chg_time,a.status

_opr_time,a.create_time, a.rec_sts, '08' as src,b.BILL_FLG,b.SP

_ID,b.BIZ_CODE from aicbs_usc_proc_batcha,aicbs_spu_biz_order

b where a.msisdn = b.msisdn and rec_sts=:v0 andb.biz_type = :v

1 and b.STS != 'D' and a.user_status!='05' order bya.create_ti

me asc

优化方法：

调整表B和表A的顺序（具体如下），使oracleRBO优化器使用小表作为驱动表，经测试单个执行时间从120秒可以下降到1秒的时间，估计能使主机CPU利用率下降10%。

修改后的sql如下：

select distincta.transactionid,

a.tansactioncode,

a.brand,

a.msisdn,

a.new_msisdn,

a.user_status,

a.status_chg_time,

a.status_opr_time,

a.create_time,

a.rec_sts,

'08' as src,

b.BILL_FLG,

b.SP_ID,

b.BIZ_CODE

fromaicbs_spu_biz_order b,aicbs_usc_proc_batch a,

where a.msisdn =b.msisdn

and rec_sts = :v0

and b.biz_type = :v1

and b.STS != 'D'

and a.user_status != '05'

order bya.create_time asc

原理补充：

基于规则的优化器（RBO）使用oracle内部的执行规则来确认sql的执行路径。另外，ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(我们称它为驱动表或基础表,drivingtable)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

参考资料：

http://blog.csdn.net/zhangao0086/article/details/6250777