Tuning SQL Query in Oracle

Question

I would really appreciate your help in tuning the below SQL query. It kept running for 10 mins when I cancelled it.

MARC_SEL gave me 31,253 records in 52 seconds and MVKE_SEL gave me 431,060 records in 22 seconds

I refactored it to use with clause but nothing much changed.What else can I incorporate to make it faster. Please help.

    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

Please don't use `WHERE` statements for joining but use the `LEFT|RIGHT|INNER JOIN ... ON` statements. The `WHERE` statements for joining are legacy and should not be used. — Munir, May 23 '16 at 22:23
Please edit your question and include the `CREATE TABLE` and/or `CREATE VIEW` for the above tables/views. Include the indexes which are defined. Also include the output from EXPLAIN PLAN for the query. Thanks. — Bob Jarvis - Слава Україні, May 23 '16 at 23:17

score 2 · Answer 1 · answered May 23 '16 at 22:40

Start by simplifying your query as you do not need to do multiple DISTINCTs (it's only necessary in the final output) and you are selecting many columns that you are not outputting. You are also joining some tables which you are not selecting from and if there are multiple matching rows for these then it may generate duplicate rows - using something like EXISTS can eliminate these joins.

Like this:

WITH MARC_SEL AS (
  SELECT A.SOURCE,
         MARA.MATNR,
         MARC.WERKS
  FROM   XXX_MAIN.XXX_XSAP A
         INNER JOIN XXX_MAIN.XXX_SAP_MARA MARA
         ON (    A.MATNR = MARA.MATNR 
             AND A.MTART = MARA.MTART )
         INNER JOIN XXX_MAIN.XXX_SAP_MARC MARC
         ON (    MARA.MATNR = MARC.MATNR )
  WHERE  EXISTS( SELECT 'X'
                 FROM   XXX_MAIN.XXX_MP_WERKS_PLANT_XREF PX
                 WHERE  PX.LEGACY_PLANT = MARC.WERKS
                 AND    PX.SOURCE = 'SP' )
  AND    EXISTS( SELECT 'X'
                 FROM   XXX_MAIN.XXX_SAP_MBEW MBEW
                 WHERE  MARC.MATNR = MBEW.MATNR
                 AND    MARC.WERKS = MBEW.BWKEY )
  AND    A.SOURCE  = 'SP'
)
, MVKE_SEL AS (
  SELECT  NVL(MX_VKORG.SAP_DE_VAL,'/') VKORG,
          NVL(SUBSTR(MX_VKORG.SAP_DE,6,2),'/') VTWEG,
          MX_VKORG.DESC4 
  FROM    XXX_MAIN.XXX_MP_VKVT_XREF MX_VKORG
  WHERE   EXISTS ( SELECT 'X'
                   FROM   XXX_MAIN.XXX_SAP_MVKE MVKE
                   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,
       MVKE.VKORG,
       MARC.WERKS,
       MVKE.VTWEG  
FROM   MARC_SEL MARC
       INNER JOIN MVKE_SEL MVKE
       ON (    MARC.MATNR = MVKE.MATNR   
           AND MARC.WERKS = MVKE.DESC4 )

Apologies @MT0, I do want values from other tables: I edited the code in my original question to show all the values that I need in my output. — Tina, May 24 '16 at 02:12

score 0 · Accepted Answer · answered Jun 07 '16 at 16:28

Added hints to subqueries and it came back in a minute.

WITH ALL_XSAP_MATNR
AS (SELECT /*+ materialize */ DISTINCT XSAP.MATNR,XSAP.MTART,XSAP.SOURCE FROM XXX_MAIN.XXX_XSAP XSAP
    WHERE SOURCE = 'SP' )
, MARC_SEL AS
( SELECT /*+ materialize */   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 /*+ materialize */ 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  /*+ use_hash(MARC,MVKE ) */
                    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

Tuning SQL Query in Oracle

2 Answers2

Linked