Oracle SQL Perfomance Tuning

weixin_34377065發表於2007-03-22
OracleSQL
    一個專案碰到效能問題，要我去優化。Oracle資料庫，專案日誌記錄一個查詢語句執行4個小時。因為對Oracle DB管理調優不熟，一開始從邏輯層面優化SQL語句，對照執行計劃覺得應當差不多了，測試執行時間比較長（等了1分鐘多沒有執行完，就Cancel掉）。進一步的分析發現：
    1. PL/SQL Developer的Explain Plan Window中執行計劃顯示不準確。
    可能是SQL語句比較複雜，加了Hints，做了某些修改之後重新執行，PL/SQL只是將之前快取的執行計劃調了出來。如果發現其顯示的執行計劃不準確，可以新開一個Explain Plan Window重新執行，這樣顯示的執行計劃是準確的。
    2. 某些情況下Oracle選擇索引有問題。
    發現這種情況後通過Hint指定索引。
    3. 有時Oracle對Join方法的選擇不準確。
    Oracle特別喜歡使用NESTED LOOPS，這種Join方法有其適用的前提條件，例如資料量很大、NESTED LOOPS迴圈次數很多、沒有高效的索引等，都會導致NESTED LOOPS效率急劇下降。因此適當的使用MERGE JOIN、HASH JOIN對效率的提升將相當明顯。
    使用Hint指定Join方法。

    遠端登入到專案的正式環境測試，初步優化調整之後，語句的執行時間為10秒鐘。
    另外老觀點：應用中不要用複雜的SQL，從業務分析設計、表結構設計上避免。

    優化前的SQL:
Select MRDemand.TranCode,MRDemand.CompanyCode,MRDemand.PlantCode,
       MRDemand.ItemCode,MRDemand.DemandDate,MRDemand.VendorCode,MRDemand.DemandQty,
       CASE WHEN (MRDemand.AdjustQty-NVL(ViewTow.SHIPQTY,0))<0 THEN 0  
                ELSE (MRDemand.AdjustQty-NVL(ViewTow.SHIPQTY,0)) END AdjustQty, 
       MRDemand.PreDemandQty,MRDemand.PreAdjustQty,
       MRDemand.MRType,MRDemand.PlanUGCode,MRDemand.PurchUGCode,MRDemand.OverDueQty, 
       MRDemand.CreateUser,MRDemand.CreateDate,MRDemand.CreateTime,
       MRDemand.LogUser,MRDemand.LogDate,MRDemand.LogTime, 
       MRDemand.Addition1,MRDemand.Addition2 
From MRDemand  
Inner Join ( 
            Select PI.CompanyCode,PI.PlantCode,PI.ItemCode  
            From Plant2Item PI,ChianTypeDef CTD  
            Where PI.CompanyCode=CTD.CompanyCode And PI.PlantCode=CTD.PlantCode 
                  And PI.ChianSubType=CTD.ChianSubType And CTD.CHIANTYPE<>'Indirect' 
    ) ViewOne 
    On MRDemand.CompanyCode=ViewOne.CompanyCode And MRDemand.PlantCode=ViewOne.PlantCode
            And MRDemand.ItemCode=ViewOne.ItemCode 
Inner Join ( 
            Select Distinct a.CompanyCode,a.PlantCode,a.ItemCode 
            From (
                 Select Distinct CompanyCode, PlantCode, ItemCode 
                 From MRDemand 
                 Where DemandDate>=to_number(to_char(Sysdate,'YYYYMMDD')) And AdjustQty>0 
                       And VendorCode='*' And PlantCode In ('','2000')) d 
            Inner Join PlantItem2Vendor a On d.CompanyCode=a.CompanyCode
                           And d.PlantCode=a.PlantCode And d.ItemCode=a.ItemCode 
            Inner Join PlantItemVAssign b On a.CompanyCode=b.CompanyCode
                           And a.PlantCode=b.PlantCode And a.ItemCode=b.ItemCode 
            Inner Join PlantItemVAssignDetail c On c.AssignCode=b.AssignCode And c.VendorCode=a.VendorCode 
            Where b.InvalidDate>=to_number(to_char(Sysdate,'YYYYMMDD')) 
    ) View3 On MRDemand.CompanyCode=View3.CompanyCode And MRDemand.PlantCode=View3.PlantCode
                     And MRDemand.ItemCode=View3.ItemCode 
Left Join ( 
            Select ASN.COMPANYCODE,ASN.PLANTCODE,ASNDetail.ITEMCODE,20070321 PLANDATE,
                     Sum(ASNDetail.SHIPQTY) SHIPQTY  
            From ASN,ASNDetail 
            Where ASN.STNo=ASNDetail.STNo And ASN.STTYPE='JIT'  
                And ASN.STStatus In ('Release','WaitCheck') 
                And ASNDetail.STDSTATUS In ('Release','WaitCheck')
                And ASNDetail.CHECKSTATUS Not In ('Qualified','UnQualified')  
                And ASNDetail.SSDate>19010101 And ASNDetail.SSDate<20070321 
            Group By ASN.COMPANYCODE,ASN.PLANTCODE,ASNDetail.ITEMCODE 
    ) ViewTow 
    On MRDemand.CompanyCode=ViewTow.CompanyCode And MRDemand.PlantCode=ViewTow.PlantCode  
        And MRDemand.ItemCode=ViewTow.ItemCode And MRDemand.DemandDate=ViewTow.PLANDATE 
Where MRDemand.AdjustQty>0 And MRDemand.VendorCode='*'
      And MRDemand.DemandDate Between :V00001 And :V00002 
      and MRDemand.PlantCode in ('','2000') 
Union 
Select MRDemand.CompanyCode,MRDemand.PlantCode,MRDemand.ItemCode,MRDemand.DemandDate,
          MRDemand.VendorCode,MRDemand.TranCode,
          MRDemand.DemandQty,MRDemand.AdjustQty,MRDemand.PreDemandQty,MRDemand.PreAdjustQty,
          MRDemand.MRType,MRDemand.PlanUGCode, 
          MRDemand.PurchUGCode,MRDemand.OverDueQty,
          MRDemand.CreateUser,MRDemand.CreateDate,MRDemand.CreateTime,
          MRDemand.LogUser, MRDemand.LogDate,MRDemand.LogTime,MRDemand.Addition1,MRDemand.Addition2 
From MRDemand  
Inner Join ( 
            Select PI.CompanyCode,PI.PlantCode,PI.ItemCode  
            From Plant2Item PI,ChianTypeDef CTD  
            Where PI.CompanyCode=CTD.CompanyCode And PI.PlantCode=CTD.PlantCode 
                And PI.ChianSubType=CTD.ChianSubType And CTD.CHIANTYPE<>'Indirect' 
    ) ViewOne 
    On MRDemand.CompanyCode=ViewOne.CompanyCode And MRDemand.PlantCode=ViewOne.PlantCode
         And MRDemand.ItemCode=ViewOne.ItemCode     
Inner Join PlantItem2Vendor On MRDemand.CompanyCode=PlantItem2Vendor.CompanyCode 
      And MRDemand.PlantCode=PlantItem2Vendor.PlantCode  And MRDemand.ItemCode=PlantItem2Vendor.ItemCode  
      And MRDemand.VendorCode=PlantItem2Vendor.VendorCode 
Where MRDemand.AdjustQty>0 And MRDemand.VendorCode<>'*' And MRDemand.DemandDate Between :V00003 
      And :V00004 and MRDemand.PlantCode in ('','2000')
    優化前執行計劃:
   

    優化後的測試SQL:
Select /*+ ordered use_hash(t3 d) use_hash(t3 t2) */
       d.CompanyCode,d.PlantCode,d.ItemCode,d.DemandDate,d.VendorCode,d.TranCode,d.DemandQty,
       Case When (d.AdjustQty-NVL(t2.ShipQty,0))<0 Then 0 Else (d.AdjustQty-NVL(t2.ShipQty,0)) End AdjustQty,
       d.PreDemandQty,d.PreAdjustQty,d.MRType,d.PlanUGCode,d.PurchUGCode,d.OverDueQty,
       d.CreateUser,d.CreateDate,d.CreateTime,d.LogUser,d.LogDate,d.LogTime,
       d.Addition1,d.Addition2
From (
     Select Distinct t1.*
     From (Select Distinct CompanyCode, PlantCode, ItemCode
          From MRDemand
          Where DemandDate>=20070322 and PlantCode in ('2000') And VendorCode='*' And AdjustQty>0) t1 
    Inner Join Plant2Item pi On pi.CompanyCode=t1.CompanyCode And pi.PlantCode=t1.PlantCode And pi.ItemCode=t1.ItemCode
    Inner Join ChianTypeDef ct On ct.CompanyCode=pi.CompanyCode 
          And ct.PlantCode=pi.PlantCode And ct.ChianSubType=pi.ChianSubType
          And ct.ChianType In ('Self','Direct') And ct.PlantCode In ('2000')
    Inner Join PlantItem2Vendor vi On vi.CompanyCode=t1.CompanyCode And vi.PlantCode=t1.PlantCode 
          And vi.ItemCode=t1.ItemCode
    Inner Join PlantItemVAssign via On via.CompanyCode=t1.CompanyCode And via.PlantCode=t1.PlantCode 
          And via.ItemCode=t1.ItemCode And via.InvalidDate>=20070322
    Inner Join PlantItemVAssignDetail viad On viad.AssignCode=via.AssignCode And viad.VendorCode=vi.VendorCode
) t3 
Inner Join MRDemand d On d.CompanyCode=t3.CompanyCode And d.PlantCode=t3.PlantCode And d.ItemCode=t3.ItemCode
Left Join(Select /*+ ordered index(snd PK_ASNDETAIL) */
                      sn.CompanyCode,sn.PlantCode,snd.ItemCode,20070322 As PlanDate,Sum(snd.ShipQty) As ShipQty
              From ASN sn
              Inner Join ASNDetail snd On sn.STNO=snd.STNO
              Where sn.PlantCode In ('2000') And sn.STStatus In ('Release','WaitCheck') And sn.STType='JIT'
                   And snd.STDStatus In ('Release','WaitCheck') And snd.CheckStatus Not In ('Qualified','UnQualified')
                   And snd.SSDate>20070222 And snd.SSDate<20070322
              Group By sn.CompanyCode,sn.PlantCode,snd.ItemCode
) t2 On t2.CompanyCode=d.CompanyCode And t2.PlantCode=d.PlantCode
           And t2.ItemCode=d.ItemCode And t2.PlanDate=d.DemandDate
Where d.DemandDate>=20070322 and d.PlantCode in ('2000') And d.VendorCode='*' And d.AdjustQty>0
Union All
Select /*+ ordered use_hash(t1 d) */
       d.CompanyCode,d.PlantCode,d.ItemCode,d.DemandDate,d.VendorCode,d.TranCode,
       d.DemandQty,d.AdjustQty,d.PreDemandQty,d.PreAdjustQty,d.MRType,d.PlanUGCode,
       d.PurchUGCode,d.OverDueQty,d.CreateUser,d.CreateDate,d.CreateTime,d.LogUser,
       d.LogDate,d.LogTime,d.Addition1,d.Addition2
From
(
    Select Distinct PlantCode, CompanyCode, ItemCode, VendorCode
    From MRDemand
    Where DemandDate>=20070322 And PlantCode in ('2000') And VendorCode<>'*' And AdjustQty>0
) t1
Inner Join PlantItem2Vendor vi On vi.PlantCode=t1.PlantCode And vi.CompanyCode=t1.CompanyCode 
      And vi.ItemCode=t1.ItemCode And vi.VendorCode=t1.VendorCode
Inner Join Plant2Item pi
      On pi.CompanyCode=t1.CompanyCode And pi.PlantCode=t1.PlantCode And pi.ItemCode=t1.ItemCode
Inner Join (
        Select Distinct CompanyCode, PlantCode, ChianSubType 
        From ChianTypeDef 
        Where ChianType In ('Self','Direct') And PlantCode In ('2000')
      ) ct On ct.CompanyCode=pi.CompanyCode And ct.PlantCode=pi.PlantCode And ct.ChianSubType=pi.ChianSubType
Inner Join MRDemand d On d.CompanyCode=t1.CompanyCode And d.PlantCode=t1.PlantCode 
      And d.ItemCode=t1.ItemCode And d.VendorCode=t1.VendorCode
Where d.DemandDate>=20070322 and d.PlantCode in ('2000') And d.VendorCode<>'*' And d.AdjustQty>0
    優化後的執行計劃:
   
    執行計劃中收縮起來的部分，基本都是INDEX UNIQUE SCAN，其它部分INDEX RANGE SCAN的，可以確定掃描的範圍很小，保證INDEX的使用效率很高。

相關文章