ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OR EXISTS语句的优化方法

OR EXISTS语句的优化方法

原创 Linux操作系统 作者:china_yebin 时间:2011-09-07 15:56:39 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-11

  • 博文量
    32
  • 访问量
    51969