Oracle SQL Perfomance Tuning

一个项目碰到性能问题，要我去优化。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的使用效率很高。