Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: Query Optimization required

    A ref type cursor is created in a procedure; select query used is defined below. Please help to reduce the execution time of this query.

    OPEN arm_cure_acct_collectables_cv FOR
    SELECT /*+ rule */ AC.account_no,
    d_null,
    d_null,
    d_null,
    SUM(CB.balance_due + CB.dispute_amt) balance,
    AC.tracking_id,
    AC.tracking_id_serv,
    AC.ext_event_id,
    AC.ext_event_type,
    AC.collectable_id,
    AC.collectable_serv,
    C.currency_code
    FROM CMF_BALANCE CB,
    CMF C,
    ARM_COLLECTABLE AC,
    BH_ARM_THRESH_PARAMETERS BATP
    WHERE CB.account_no = C.account_no
    AND (CB.bill_ref_no = 0 OR
    --CB.ppdd_date <= dateadd('day', 0 - BATP.grace_period, TRUNC(SYSDATE)))
    CB.PPDD_DATE <= (TRUNC(SYSDATE)-BATP.GRACE_PERIOD))
    AND AC.account_no = C.account_no
    AND AC.account_no > 0
    AND (( C.collection_indicator =1 AND AC.apn_tracking_id IS NULL) OR (C.collection_indicator =0 AND AC.apn_tracking_id IS NOT NULL))
    AND BATP.BUSINESS_UNIT = (SELECT BU.PARAM_VALUE
    FROM CMF_EXT_DATA BU
    WHERE BU.ACCOUNT_NO = C.ACCOUNT_NO
    AND BU.PARAM_ID = v_business_unit)
    AND BATP.CUSTOMER_TYPE = (SELECT CT.PARAM_VALUE
    FROM CMF_EXT_DATA CT
    WHERE CT.ACCOUNT_NO = C.ACCOUNT_NO
    AND CT.PARAM_ID = v_customer_type)
    AND (BATP.CUSTOMER_CLASSIFICATION = (SELECT CC.PARAM_VALUE
    FROM CMF_EXT_DATA CC
    WHERE CC.ACCOUNT_NO = C.ACCOUNT_NO
    AND CC.PARAM_ID = v_customer_class)
    OR BATP.CUSTOMER_CLASSIFICATION IS NULL)
    AND (BATP.PRODUCT_TYPE = (SELECT PT.PARAM_VALUE
    FROM CMF_EXT_DATA PT
    WHERE PT.ACCOUNT_NO = C.ACCOUNT_NO
    AND PT.PARAM_ID = v_product_type)
    OR BATP.PRODUCT_TYPE IS NULL)
    AND (BATP.CREDIT_SCORE = (SELECT CS.PARAM_VALUE
    FROM CMF_EXT_DATA CS
    WHERE CS.ACCOUNT_NO = C.ACCOUNT_NO
    AND CS.PARAM_ID = v_credit_score)
    OR BATP.CREDIT_SCORE IS NULL)
    AND BATP.CURRENCY_CODE = C.CURRENCY_CODE
    AND (BATP.MKT_CODE = C.MKT_CODE OR BATP.MKT_CODE IS NULL)
    AND (BATP.ACCOUNT_CATEGORY = C.ACCOUNT_CATEGORY OR BATP.ACCOUNT_CATEGORY IS NULL)
    AND (BATP.VIP_CODE = C.VIP_CODE OR BATP.VIP_CODE IS NULL)
    GROUP BY AC.account_no, C.currency_code, C.account_status,AC.tracking_id,
    AC.collectable_id,AC.tracking_id_serv, AC.ext_event_id,AC.ext_event_type,
    AC.collectable_serv,exit_thresh_amt,BATP.exit_thre sh_pct
    HAVING (SUM(CB.balance_due + CB.dispute_amt) <= (GREATEST(BATP.exit_thresh_amt,
    BATP.exit_thresh_pct * (SELECT ED.param_value
    FROM CMF_EXT_DATA ED
    WHERE ED.account_no = AC.account_no AND
    ED.param_id = v_col_enter_bal /*ID of Col_Enter_Bal*/ )/100 ))
    AND C.account_status < 1) OR (SUM(CB.balance_due + CB.dispute_amt) <= 0 AND C.account_status > 0);

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN
    Code:
    SELECT /*+ rule */ AC.account_no, 
                       d_null, 
                       d_null, 
                       d_null, 
                       SUM(CB.balance_due + CB.dispute_amt) balance, 
                       AC.tracking_id, 
                       AC.tracking_id_serv, 
                       AC.ext_event_id, 
                       AC.ext_event_type, 
                       AC.collectable_id, 
                       AC.collectable_serv, 
                       C.currency_code 
    FROM   cmf_balance CB, 
           cmf C, 
           arm_collectable AC, 
           bh_arm_thresh_parameters BATP 
    WHERE  CB.account_no = C.account_no 
           AND ( CB.bill_ref_no = 0 
                  OR 
                 --CB.ppdd_date <= dateadd('day', 0 - BATP.grace_period, TRUNC(SYSDATE))) 
                 CB.ppdd_date <= ( Trunc(SYSDATE) - BATP.grace_period ) ) 
           AND AC.account_no = C.account_no 
           AND AC.account_no > 0 
           AND ( ( C.collection_indicator = 1 
                   AND AC.apn_tracking_id IS NULL ) 
                  OR ( C.collection_indicator = 0 
                       AND AC.apn_tracking_id IS NOT NULL ) ) 
           AND BATP.business_unit = (SELECT BU.param_value 
                                     FROM   cmf_ext_data BU 
                                     WHERE  BU.account_no = C.account_no 
                                            AND BU.param_id = v_business_unit) 
           AND BATP.customer_type = (SELECT CT.param_value 
                                     FROM   cmf_ext_data CT 
                                     WHERE  CT.account_no = C.account_no 
                                            AND CT.param_id = v_customer_type) 
           AND ( BATP.customer_classification = (SELECT CC.param_value 
                                                 FROM   cmf_ext_data CC 
                                                 WHERE  CC.account_no = C.account_no 
                                                        AND CC.param_id = 
                                                            v_customer_class) 
                  OR BATP.customer_classification IS NULL ) 
           AND ( BATP.product_type = (SELECT PT.param_value 
                                      FROM   cmf_ext_data PT 
                                      WHERE  PT.account_no = C.account_no 
                                             AND PT.param_id = v_product_type) 
                  OR BATP.product_type IS NULL ) 
           AND ( BATP.credit_score = (SELECT CS.param_value 
                                      FROM   cmf_ext_data CS 
                                      WHERE  CS.account_no = C.account_no 
                                             AND CS.param_id = v_credit_score) 
                  OR BATP.credit_score IS NULL ) 
           AND BATP.currency_code = C.currency_code 
           AND ( BATP.mkt_code = C.mkt_code 
                  OR BATP.mkt_code IS NULL ) 
           AND ( BATP.account_category = C.account_category 
                  OR BATP.account_category IS NULL ) 
           AND ( BATP.vip_code = C.vip_code 
                  OR BATP.vip_code IS NULL ) 
    GROUP  BY AC.account_no, 
              C.currency_code, 
              C.account_status, 
              AC.tracking_id, 
              AC.collectable_id, 
              AC.tracking_id_serv, 
              AC.ext_event_id, 
              AC.ext_event_type, 
              AC.collectable_serv, 
              exit_thresh_amt, 
              BATP.exit_thresh_pct 
    HAVING ( SUM(CB.balance_due + CB.dispute_amt) <= ( 
                      Greatest(BATP.exit_thresh_amt, BATP.exit_thresh_pct * 
                                                     (SELECT ED.param_value 
                          FROM   cmf_ext_data ED 
                          WHERE  ED.account_no = 
                                 AC.account_no 
                                 AND 
                      ED.param_id = v_col_enter_bal 
                      /*ID of Col_Enter_Bal*/) / 100) ) 
             AND C.account_status < 1 ) 
            OR ( SUM(CB.balance_due + CB.dispute_amt) <= 0 
                 AND C.account_status > 0 );
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to rewrite the query.

    Not tested.
    Because, no DDLs and no INSERT statements to populate test data.
    Code:
    SELECT /*+ rule */
           AC.account_no
         , d_null
         , d_null
         , d_null
         , CB.balance
         , AC.tracking_id
         , AC.tracking_id_serv
         , AC.ext_event_id
         , AC.ext_event_type
         , AC.collectable_id
         , AC.collectable_serv
         , C .currency_code
         , C .account_status
         , BATP.exit_thresh_amt
         , BATP.exit_thresh_pct
     FROM  ARM_COLLECTABLE          AC
         , CMF                      C
         , (SELECT MAX(CASE ced.PARAM_ID WHEN v_business_unit  THEN ed.PARAM_VALUE END) AS business_unit
                 , MAX(CASE ced.PARAM_ID WHEN v_customer_type  THEN ed.PARAM_VALUE END) AS customer_type
                 , MAX(CASE ced.PARAM_ID WHEN v_customer_class THEN ed.PARAM_VALUE END) AS customer_class
                 , MAX(CASE ced.PARAM_ID WHEN v_product_type   THEN ed.PARAM_VALUE END) AS product_type
                 , MAX(CASE ced.PARAM_ID WHEN v_credit_score   THEN ed.PARAM_VALUE END) AS credit_score
                 , MAX(CASE ced.PARAM_ID WHEN v_col_enter_bal  THEN ed.PARAM_VALUE END) AS col_enter_bal
             FROM  CMF_EXT_DATA ced
             WHERE ced.account_no = AC.account_no
               AND ced.PARAM_ID
                   IN (  v_business_unit
                       , v_customer_type
                       , v_customer_class
                       , v_product_type
                       , v_credit_score
                       , v_col_enter_bal
                      )
             GROUP BY
                   ced.account_no
             /* or AC.account_no ? */
           ) ced
         , BH_ARM_THRESH_PARAMETERS BATP
         , (SELECT SUM(CB.balance_due + CB.dispute_amt) balance
             FROM  CMF_BALANCE      CB
             WHERE CB.account_no = AC.account_no
               AND
               (   CB.bill_ref_no = 0
                OR
                   CB.PPDD_DATE <= TRUNC(SYSDATE) - BATP.GRACE_PERIOD
               )
             GROUP BY
                   CB.account_no
             /* or AC.account_no ? */
           ) CB
     WHERE
           AC.account_no > 0
       AND C .account_no = AC.account_no
       AND
       (    AC.apn_tracking_id IS NULL
        AND C .collection_indicator = 1
        OR
            AC.apn_tracking_id IS NOT NULL
        AND C .collection_indicator = 0
       )
       AND BATP.CURRENCY_CODE = C.CURRENCY_CODE
       AND
       (   BATP.MKT_CODE = C.MKT_CODE
        OR BATP.MKT_CODE IS NULL
       )
       AND
       (   BATP.ACCOUNT_CATEGORY = C.ACCOUNT_CATEGORY
        OR BATP.ACCOUNT_CATEGORY IS NULL
       )
       AND
       (   BATP.VIP_CODE = C.VIP_CODE
        OR BATP.VIP_CODE IS NULL
       )
       AND BATP.BUSINESS_UNIT = ced.business_unit
       AND BATP.CUSTOMER_TYPE = ced.customer_type
       AND
       (   BATP.CUSTOMER_CLASSIFICATION = ced.customer_class
        OR BATP.CUSTOMER_CLASSIFICATION IS NULL
       )
       AND
       (   BATP.PRODUCT_TYPE = ced.product_type
        OR BATP.PRODUCT_TYPE IS NULL
       )
       AND
       (   BATP.CREDIT_SCORE = ced.credit_score
        OR BATP.CREDIT_SCORE IS NULL
       )
       AND CB.balance
           <= CASE
              WHEN C .account_status <= 0 THEN /* assumed account_status is INEGER */
                   GREATEST(  BATP.exit_thresh_amt
                            , BATP.exit_thresh_pct * ced.col_enter_bal / 100
                           )
              ELSE 0
              END
    ;

  4. #4
    Join Date
    Apr 2013
    Posts
    3
    Explain Plan is below: Please check


    SELECT STATEMENT HINT: RULE
    26 FILTER
    23 SORT GROUP BY
    22 FILTER
    15 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_BALANCE
    14 NESTED LOOPS
    12 NESTED LOOPS
    5 NESTED LOOPS
    2 TABLE ACCESS BY INDEX ROWID ARBOR.ARM_COLLECTABLE
    1 INDEX RANGE SCAN NON-UNIQUE ARBOR.ARM_COLLECTABLE_XCC_PN_NTR
    4 TABLE ACCESS BY INDEX ROWID ARBOR.CMF
    3 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_PK
    11 TABLE ACCESS BY INDEX ROWID NCORE.BH_ARM_THRESH_PARAMETERS
    10 INDEX RANGE SCAN NON-UNIQUE NCORE.TEST_IDX
    7 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_EXT_DATA
    6 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_EXT_DATA_PK
    9 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_EXT_DATA
    8 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_EXT_DATA_PK
    13 INDEX RANGE SCAN UNIQUE ARBOR.CMF_BALANCE_PK
    17 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_EXT_DATA
    16 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_EXT_DATA_PK
    19 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_EXT_DATA
    18 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_EXT_DATA_PK
    21 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_EXT_DATA
    20 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_EXT_DATA_PK
    25 TABLE ACCESS BY INDEX ROWID ARBOR.CMF_EXT_DATA
    24 INDEX UNIQUE SCAN UNIQUE ARBOR.CMF_EXT_DATA_PK

  5. #5
    Join Date
    Apr 2013
    Posts
    3
    Please can any one help in reducing the execution time of the above query.

  6. #6
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I'm afraid, with the information you provided, it is very hard to give you any advice:

    you did not mention:

    - the Oracle Version
    - the cardinalities of the tables
    - the indexes on the tables
    - a COMPLETE execution plan WITHOUT the rule hint, that is formatted using CODE - tags ("set autotrace on" in the SQL*Plus - session)
    - the tuning goal (execution time now vs. desired execution time)
    - the OS
    - the reason for the RULE hint

    All our crystal balls are broken, we need your input ...
    Last edited by magicwand; 04-11-13 at 19:46.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •