OR EXISTS语句的优化方法：
这库一直很空闲，但无意中看了一下，发现其中很多语句都很有问题，都是典型的OR问题语句，如果并发量大的话，CPU一下子就飙高了。
OR语句一直是性能杀手，当存在一两个的时候一般可以用union和union all来优化，请看以下例子。
1.在原句中使用了or语句并且or语句里面使用exists语句，这样给优化器造成了很大的迷惑。
2.从执行计划看来，貌似没有什么问题。
3.但从统计数据来看，一致读非常高，达到了4百多万次。
select count(*)
from UNIOMS0808.SETTLEMENT s
where (settlementStatus = 3)
and (companyCode like '%GDSPID01687%' or exists
(select 1
from UNIOMS0808.PROVIDER pro
where s.companyCode = pro.cpId
and pro.companyId in
(select com.companyId
from UNIOMS0808.COMPANY com
where com.companyCode like '%GDSPID01687%')));
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=164 Card=1 Bytes=14)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SETTLEMENT' (Cost=164 Card=1939 Bytes=27146)
4 2 NESTED LOOPS (Cost=375 Card=190 Bytes=6840)
5 4 TABLE ACCESS (FULL) OF 'PROVIDER' (Cost=20 Card=355 Bytes=4615)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY' (Cost=1 Card=1 Bytes=23)
7 6 INDEX (UNIQUE SCAN) OF 'PK_COMPANY' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4726442 consistent gets
0 physical reads
0 redo size
406 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
优化思路：直接将or语句转换为union语句观察一下。
select count(1)
from
(
select SETTLEMENTID
from UNIOMS0808.SETTLEMENT s
where (settlementStatus = 3)
and (companyCode like '%GDSPID01687%')
union
select SETTLEMENTID
from UNIOMS0808.SETTLEMENT s
where (settlementStatus = 3)
and exists(select 1
from UNIOMS0808.PROVIDER pro, UNIOMS0808.COMPANY com
where s.companyCode = pro.cpId
and pro.companyId =com.companyId
and com.companyCode like '%GDSPID01687%')
);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=376 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=376 Card=1199)
3 2 SORT (UNIQUE) (Cost=376 Card=1199 Bytes=37355)
4 3 UNION-ALL
5 4 TABLE ACCESS (FULL) OF 'SETTLEMENT' (Cost=164 Card=994 Bytes=24850)
6 4 HASH JOIN (Cost=199 Card=205 Bytes=12505)
7 6 NESTED LOOPS (Cost=34 Card=14 Bytes=504)
8 7 TABLE ACCESS (FULL) OF 'PROVIDER' (Cost=20 Card=14 Bytes=182)
9 7 TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY' (Cost=1 Card=1 Bytes=23)
10 9 INDEX (UNIQUE SCAN) OF 'PK_COMPANY' (UNIQUE)
11 6 TABLE ACCESS (FULL) OF 'SETTLEMENT' (Cost=164 Card=19884 Bytes=497100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1917 consistent gets
0 physical reads
0 redo size
406 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
观察效果：效果非常明显，一致读下降到2千个，是原来的千分之0.4。从执行计划中看最大不同在于新的执行计划使用了hash join，而且显示的cost比原来的还要大。
问题分析：由于or exists语句导致优化器选择了nested loops进行表连接，而nested loops对于少数据量处理是很好的，但对于全表扫描来说则效率更低，最终导致大量的一致读产生。
有兴趣的可以进一步使用10046事件进行分析，确定问题的症结。
以下提供可重现的测试方法
create table test as select * from dba_objects;
create table test1 as select * from dba_objects;
优化前
select count(*)
from test s
where (OBJECT_TYPE = 'VIEW')
and (OBJECT_NAME like '%DBA_TABLES%' or exists
(select 1
from test1 pro
where s.object_id = pro.object_id
and pro.OBJECT_NAME = 'DBA_TABLES'));
优化后
select count(1)
from
(
select object_id
from test s
where (OBJECT_TYPE = 'VIEW')
and (OBJECT_NAME like '%DBA_TABLES%')
UNION
select object_id
from test s
where (OBJECT_TYPE = 'VIEW')
and exists
(select 1
from test1 pro
where s.object_id = pro.object_id
and pro.OBJECT_NAME = 'DBA_TABLES')
);
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13605188/viewspace-706829/，如需转载，请注明出处，否则将追究法律责任。
