title: Tuning SQL Query in Oracle
body: <p>I would really appreciate your help in tuning the below SQL query. It kept running for 10 mins when I cancelled it.</p>
  
  <p>MARC_SEL gave me 31,253 records in 52 seconds
  and MVKE_SEL gave me 431,060 records in 22 seconds</p>
  
  <p>I refactored it to use with clause but nothing much changed.What else can I incorporate to make it faster. Please help.</p>
  
  <pre><code>    WITH ALL_XSAP_MATNR
      AS (SELECT DISTINCT XSAP.MATNR,XSAP.MTART,XSAP.SOURCE FROM XXX_MAIN.XXX_XSAP XSAP
          WHERE SOURCE = 'SP' )
      , MARC_SEL AS
      ( SELECT    DISTINCT   A.SOURCE
                    ,MARA.MATNR  
                    ,MARA.MTART      
                    ,MARA.MBRSH   
                    ,MARC.WERKS      
                    ,NVL(PX.WERKS,'/') DWERK   
                    ,NVL(MBEW.HKMAT,'/') HKMAT   
                    ,NVL(MBEW.EKALR,'/')   EKALR   
                    ,NVL(MARC.STAWN,'/')     STAWN  
      FROM ALL_XSAP_MATNR A
             , XXX_MAIN.XXX_SAP_MARA MARA 
             , XXX_MAIN.XXX_SAP_MARC MARC
             , XXX_MAIN.XXX_MP_WERKS_PLANT_XREF PX
             , XXX_MAIN.XXX_SAP_MBEW MBEW
       WHERE  A.MATNR = MARA.MATNR 
          AND A.MTART = MARA.MTART
          AND MARA.MATNR = MARC.MATNR
          AND MARC.MATNR = MBEW.MATNR
          AND MARC.WERKS = MBEW.BWKEY
          AND PX.LEGACY_PLANT = MARC.WERKS
          AND PX.SOURCE = 'SP'                                   
      )
      , MVKE_SEL AS
      (    SELECT  DISTINCT 
                    MVKE.MATNR
                    ,'/' LEGACY_ORG    
                    ,'/' LEGACY_MATNR       
                    ,NVL(MX_VKORG.SAP_DE_VAL,'/') VKORG         
                    ,NVL(SUBSTR(MX_VKORG.SAP_DE,6,2),'/') VTWEG 
               --     ,NVL(TVRKME.MSEH3,'/') VRKME         
                    ,NVL(MVKE.KONDM,'/') KONDM          
                   ,NVL(MVKE.VERSG,'/') VERSG
                    ,'/' IPRKZ         
                    ,'/' MHDRZ,NVL(MVKE.VMSTA,'/')     VMSTA   
                    ,NVL(TO_CHAR(MVKE.VMSTD ,'YYYYMMDD' ),'/')    VMSTD    
                    ,NVL(MVKE.PMATN,'/')    PMATN        
                    ,NVL(MVKE.MVGR2,'/')      MVGR2       
                    ,NVL(MVKE.MVGR3,'/')      MVGR3         
                    ,NVL(MVKE.VAVME,'/')     VAVME         
                    ,'/' MVGR4         
                    ,'/' MVGR5         
                    ,NVL(MVKE.MTPOS,'/')    MTPOS         
                    ,NVL(MVKE.PRAT1,'/')   PRAT1        
                    ,NVL(MVKE.SKTOF,'/')    SKTOF         
                    ,'/' AUMNG         
                    ,NVL(MVKE.PRODH,'/')  PRODH       
                    ,'/' MVGR1               
                    ,NVL(MVKE.KTGRM,'/')    KTGRM  
                    ,MX_VKORG.DESC4  
      FROM     XXX_MAIN.XXX_SAP_MVKE MVKE
             , XXX_MAIN.XXX_MP_VKVT_XREF MX_VKORG    
       WHERE MX_VKORG.SOURCE_DE_VAL = MVKE.VKORG
          AND SUBSTR(MX_VKORG.SAP_DE,6,2) = MVKE.VTWEG
          AND MX_VKORG.SOURCE_TBL = 'SP'
          AND MX_VKORG.SOURCE_DE = 'MVKE'
          AND SUBSTR(MX_VKORG.SAP_DE,1,5)= 'VKORG'
          AND MX_VKORG.DESC2 IS NULL  )
      SELECT DISTINCT
                          MARC.SOURCE
                         ,MARC.MATNR   
                         ,MARC.MTART      
                         ,MARC.MBRSH   
                         ,MARC.WERKS      
                         ,MARC.DWERK   
                         ,MARC.HKMAT   
                         ,MARC.EKALR   
                         ,MARC.STAWN 
                         ,MVKE.LEGACY_ORG
                         ,MVKE.LEGACY_MATNR
                         ,MVKE.VKORG
                         ,MVKE.VTWEG 
                         ,MVKE.KONDM
                         ,MVKE.VERSG
                         ,MVKE.VMSTA
                         ,MVKE.VMSTD
                         ,MVKE.PMATN
                         ,MVKE.MVGR2
                         ,MVKE.MVGR3
                         ,MVKE.VAVME
                         ,MVKE.MTPOS
                         ,MVKE.PRAT1
                         ,MVKE.SKTOF
                         ,MVKE.PRODH
                         ,MVKE.KTGRM
       FROM   MARC_SEL MARC
            , MVKE_SEL MVKE
        WHERE MARC.MATNR = MVKE.MATNR   
          AND MARC.WERKS = MVKE.DESC4
  </code></pre>