• 博客访问: 539466
  • 博文数量: 119
  • 用 户 组: 普通用户
  • 注册时间: 2009-04-14 11:09
个人简介

问题比答案重要! 见识比信息重要!

文章存档

2017年(2)

2016年(5)

2015年(15)

2014年(4)

2011年(16)

2010年(47)

2009年(30)

我的朋友

分类: Linux操作系统

2010-08-05 14:16:55

昨天工程师反映客户一个sql的统计等的n慢,我plsql跑了一下,需要1110秒,近20分钟,这个现状肯定无法忍受。后进行分析调整之后查询速度在6-12秒之内,调整步骤如下:

现状:linux, oracle 10g ,4G内存,sga1.5G, 调整shared_pool300M,这个shared_pool的调整有些怀疑。先搁置。
表BCM_MONTHGASFEE 数据=26494361
原sql
-————————————————————————
select substr(f.dataenddate, 0, 7) gasdate,
       min(o.name) officename,
       to_char(sum(f.gasmonthcost)) cost
  from BCM_MONTHGASFEE f, OPM_ORGAN O
 where f.officecode = o.code
   and f.ChargeMethodCode = '1'
   and (f.bcharge = 0 or
       (f.bcharge = 1 and
       f.ChargeTime >
       to_timestamp('2010-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
   and (f.officecode like '110%'
   and f.dataenddate between '2005-01-01' and '2010-07-31')  
 group by substr(f.dataenddate, 0, 7), o.code
 order by substr(f.dataenddate, 0, 7), o.code

OPM_ORGAN O=90条
——————————————————————————

进行sql语句分析,得结果如下:
select substr(f.dataenddate, 0, 7) gasdate,
            f.officecode,
            to_char(sum(f.gasmonthcost)) cost
       from BCM_MONTHGASFEE f
      where 
         ((f.ChargeTime > to_timestamp('2009-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
        and f.bcharge = 1) or f.bcharge = 0 )    
        and f.ChargeMethodCode = '1'
        and f.dataenddate between '2005-01-01' and '2010-07-31'
        and f.officecode like '110%'  
      group by substr(f.dataenddate, 0, 7), f.officecode
-----------------------------------
进行计划分析
 explain plan for
   ....{sql语句}
 select * from table(dbms_xplan.display());
是全表扫描,后来建立索引1:officecode, dataenddate ;索引2:bcharge, ChargeTime 进行逐步分析,仍然是全表扫描。
后删除索引1和索引2,建立索引3:OFFICECODE, DATAENDDATE, BCHARGE, CHARGETIME, ChargeMethodCode, 到最后仍然是全表扫描,后来发现,原因在
sum(f.gasmonthcost)这个条语句上,怀疑是sum,去掉sum仍然全表,后来看因为gasmonthcost列未在索引范围之内,后把该索引加上,最终索引是:create index IDX_BCM_MONTHGASFEE_OFF1
 on BCM_MONTHGASFEE (OFFICECODE, DATAENDDATE, BCHARGE, CHARGETIME, ChargeMethodCode, GASMONTHCOST) local;

语句调整如下:
select a.gasdate, b.name, a.cost
   from (select substr(f.dataenddate, 0, 7) gasdate,
            f.officecode,
            to_char(sum(f.gasmonthcost)) cost
       from BCM_MONTHGASFEE f
      where 
         ((f.ChargeTime > to_timestamp('2009-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
        and f.bcharge = 1) or f.bcharge = 0 )    
        and f.ChargeMethodCode = '1'
        and f.dataenddate between '2005-01-01' and '2010-07-31'
        and f.officecode like '110%'  
      group by substr(f.dataenddate, 0, 7), f.officecode
      )a, opm_organ b
  where b.officecode=a.code
  order by a.gasdate, a.officecode;

执行Sql,查询出结果25秒.

经过以上的Sql调整逻辑读和物理读已经大大缩小了
但是逻辑读还是特别大
   112514  consistent gets
   72207  physical reads
下面进行调整逻辑读
调整sql如下:
--------------------------------------------

select a.gasdate, b.name, a.cost
   from (select gasdate, officecode,  to_char(sum(cost)) cost
        from (select substr(f.dataenddate, 0, 7) gasdate,
                 f.officecode,
                 f.gasmonthcost cost
            from BCM_MONTHGASFEE f
           where 
              (f.ChargeTime > to_timestamp('2009-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
             and f.bcharge = 1)     
             and f.ChargeMethodCode = '1'
             and f.dataenddate between '2005-01-01' and '2010-07-31'
             and f.officecode like '110%'        
           union
           select substr(f.dataenddate, 0, 7) gasdate,
                 f.officecode,
                 f.gasmonthcost cost
            from BCM_MONTHGASFEE f
           where f.bcharge = 0
             and f.ChargeMethodCode = '1'
             and f.dataenddate between '2005-01-01' and '2010-07-31'
             and f.officecode like '110%'  
             ) x       
     group by x.gasdate, x.officecode
    )a, opm_organ b
  where a.officecode=b.code
  order by a.gasdate, a.officecode;

经调整之后:
 54533  consistent gets
  8392  physical reads

执行Sql,查询出结果9秒.
达到预期。

但是数据和原sql查询出来的数据有出入,调整之后的数据512条,原sql查询是477条,有些疑惑!!!

但是查询数的数据一样的啊,奇怪!!

 ---一下查询结果一样 26494957条数据
  select count(1)
  from BCM_MONTHGASFEE f, OPM_ORGAN O
 where f.officecode = o.code  ;
 
  select count(1)
  from BCM_MONTHGASFEE f;


---关于sql优化,望大家讨论

阅读(4273) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册