一個2600萬資料的SQL統計,SQL調優(原創)

coolhe發表於2010-08-05

昨天工程師反映客戶一個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優化,望大家討論

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/3090/viewspace-670347/,如需轉載,請註明出處,否則將追究法律責任。

相關文章