Query Performance Enhancement

Question

I am new to SQL performance tuning. Below is the query that I need to enhance. Can someone please guide me on what or where should I start improving this query. I can only tweak this on query level, I am not allowed to alter the tables used in this SQL.

This is running on Oracle 12g.
This query is running for almost 10 hrs.
If possible, I want to make this query runs significantly with lower runtime.

    cursor cursor2 is --logic 2
        SELECT /*+ PARALLEL (PRD, 36) */ 
               AST.ASSET_NUM                                  
            ,AST.STATUS_CD                                  
            ,PRD.NAME                                      
            ,PR_ATTR.CHAR_VAL                               
            ,U_ATTR.CHAR_VAL                             
            ,SRV_AD.ADDR                                 
            ,SRV_AD.ADDR_LINE_2                          
            ,SRV_AD.CITY                                 
            ,UPPER(SRV_AD.STATE)                         
            ,SRV_AD.ZIPCODE                              
            ,SRV_AD.COUNTRY                              
            ,CX_ACT.OU_NUM                               
            ,CX_ACT.X_CCNA_ACNA_FRP                      
            ,CX_ACT.NAME                                 
            ,CX_ACT.OU_TYPE_CD                           
            ,CX_ACT_x.ATTRIB_46                          
            ,BILL_ACCT.OU_NUM                               
            ,DECODE(LIFE_LINE.LIFE_ELIG_FLG, 'Y', 'Y', 'N') 
            ,SR_AC_X.X_LOC_NUM                          
            ,SR_AC_X.X_BOOTH_LOC                        
            ,SR_AC_X.X_COINLESS_IND                     
            ,SERV_ACCT.X_WIRE_CENTER_FRP                    
            ,SALES_ORD.ORDER_NUM                            
            ,SALES_ORD_X.ATTRIB_06 
                ,TO_CHAR(GREATEST ( NVL(AST.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(PRD.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(PR_ATTR.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(U_ATTR.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(SERV_ACCT.LAST_UPD, TO_DATE('01011900', 
    'MMDDYYYY'))
                                , NVL(SR_AC_X.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(SRV_AD.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(CX_ACT.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(CX_ACT_X.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(LIFE_LINE.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))
                                , NVL(BILL_ACCT.LAST_UPD, TO_DATE('01011900', 'MMDDYYYY'))), 'DD-MM-YYYY HH24:MI:SS')
            ,CA.NC                                     
            ,CA.NCI                                    
            ,CA.SECNCI                                 
            ,CA.NC1                                    
            ,CA.ACT                                    
            ,CA.SPEC                                   
            ,CA.PIU                                    
            ,CA.ACTI                                   
            ,CA.CCNA                                   
          FROM A.S_ASSET     AST
             , A.S_PRD_INT  PRD
             , A.S_ASSET_XA  PR_ATTR
             , A.S_ASSET_XA  U_ATTR
             , A.S_ORG_EXT   SERV_ACCT
             , A.S_ORG_EXT_X SR_AC_X
             , A.S_ADDR_PER  SRV_AD
             , A.S_ORG_EXT   CX_ACT
             , A.S_ORG_EXT_X CX_ACT_X
             , A.S_SUBSIDY   LIFE_LINE
             , A.S_ORG_EXT   BILL_ACCT
             , A.S_BU        BU
             , A.S_ORDER     SALES_ORD
             , A.S_ORDER_X   SALES_ORD_X
             , B.CAMP_TEMP_XA CA
         WHERE AST.ROW_ID = CA.ASSET_ROW_ID (+)
           AND AST.ROW_ID           = AST.ROOT_ASSET_ID
           AND (AST.PRD_ID          = PRD.ROW_ID        AND PRD.part_num IN ('R', 'B', 'R_D', 'B_D', 'ND'))
           AND (AST.ROW_ID          = PR_ATTR.ASSET_ID    AND PR_ATTR.ATTR_NAME = 'NUMBER')
           AND (AST.ROW_ID          = U_ATTR.ASSET_ID  AND U_ATTR.ATTR_NAME = 'UNIVERSE')
           AND AST.SERV_ACCT_ID     = SERV_ACCT.ROW_ID (+)
           AND SERV_ACCT.PAR_ROW_ID = SR_AC_X.PAR_ROW_ID (+)
           AND SERV_ACCT.PR_ADDR_ID = SRV_AD.ROW_ID (+)
           AND AST.OWNER_ACCNT_ID   = CX_ACT.ROW_ID (+)
           AND CX_ACT.PAR_ROW_ID = CX_ACT_X.PAR_ROW_ID (+)
           AND CX_ACT.ROW_ID     = LIFE_LINE.ACCNT_ID (+)
           AND AST.X_FIRST_ORDER_NUM = SALES_ORD.ROW_ID (+)
           AND SALES_ORD.ROW_ID     = SALES_ORD_X.PAR_ROW_ID (+)
           AND AST.BILL_ACCNT_ID    = BILL_ACCT.ROW_ID (+)
           AND SERV_ACCT.BU_ID      = BU.ROW_ID
       AND UPPER(SRV_AD.STATE) IN ( 'AB', 'CD', 'EF')
       AND PR_ATTR.CHAR_VAL IS NOT NULL
           AND BU.NAME <> 'WS';

First you need to get the execution plan. You will likely need a DBA to help you with that. — Nathan Hughes, Sep 16 '21 at 16:17
Even if you can't _change_ the tables, it would still be helpful to know how they are constructed, indexed, etc. complete DDL (if possible), but particularly to know what indexes exist and what type they are. Also ask the DBA to make sure that all table statistics are up to date when the query is run. — pmdba, Sep 16 '21 at 16:21
Check [here](https://stackoverflow.com/a/34975420/4808122) what you need to post so you can expect realistic help. With the information you provided only *safe* advise would be *switch to ANSI SQL*... — Marmite Bomber, Sep 16 '21 at 18:15
That's not "just a query". It is a query defining a cursor. So it should be assumed that the cursor is to implement loop logic in PL/SQL. And as such, any performance issues are very likely to be in what is going on in the loop, and not simply the query that is defining the cursor. If I were contracted to improve the performance, I'd want to see the entire PL/SQL that is using said cursor. I'd focus on that before the query. — EdStevens, Sep 16 '21 at 18:15
Another thing to consider is, what is the current performance, and is that an actual problem that impacts business. A lot of people contract a case of Compulsive Tuning Disorder and set out to improve things without a demonstrated specific need. Consider how much effort is justified to gain a 50% performance improvement -- in a query that already executes in 0.25 seconds. — EdStevens, Sep 16 '21 at 18:18

Query Performance Enhancement

0 Answers0