Query Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query Tuning

  1. #1
    Join Date
    Dec 2002
    Location
    Singapore
    Posts
    27

    Query Tuning

    Hi,
    Can any one guide me on how to tune this query for
    optimal performance.The query and my explain plan are
    as stated below.Is there any other alternative for me
    to tune this query to get better performance
    benefits.Say lower the cost.And faster time.Your suggestions are
    greatly appreciated.

    1. Original Query with cus_account, cus_pers,
    cus_card_iss
    ============================================================================
    =====================
    SELECT ccmr.CARD_NAME, ccis.CUS_ID,
    ccac.TIER_STATUS_IND, ccac.ACCT_STATUS_IND,
    NVL(to_char(ccis.CARD_EXP_DT,'dd/mm/yyyy hh:mm:ss'),
    'NIL') as EXP_DATE
    from cus_account ccac, cus_card_iss ccis, cus_pers
    ccmr
    where ccac.PRG_CD='KF' and
    ccac.ACCT_STATUS_IND in ('A','D','E') and
    ccac.TIER_STATUS_IND in ('Q','T','L') and
    ccac.IGNORE_FLG='N' and
    (ccac.CARD_VALID_TILL_DT is null or
    ccac.CARD_VALID_TILL_DT >=SYSDATE) and
    ccac.int_id=ccmr.int_id and
    (ccac.int_id=ccis.int_id and
    ccis.IGNORE_FLG='N' and
    (ccis.CARD_IND!='S' or ccis.CARD_IND is null) and
    (ccis.CARD_VALID_TILL_DT is null or
    ccis.CARD_VALID_TILL_DT >=SYSDATE)
    )
    >> 36436 records
    *** Explain Plan***
    SELECT STATEMENT Optimizer=CHOOSE (Cost=13006
    Card=202238 Bytes=13954422)
    HASH JOIN (Cost=13006 Card=202238 Bytes=13954422)
    HASH JOIN (Cost=5853 Card=130656 Bytes=5487552)
    INLIST ITERATOR
    TABLE ACCESS (BY GLOBAL INDEX ROWID) OF
    CUS_ACCOUNT (Cost=64 Card=130656 Bytes=2482464)
    INDEX (RANGE SCAN) OF IX_CUS_ACCOUNT_08
    (NON-UNIQUE) (Cost=3 Card=145675)
    PARTITION HASH (ALL)
    TABLE ACCESS (FULL) OF CUS_PERS (Cost=3615
    Card=1796560 Bytes=41320880)
    PARTITION HASH (ALL)
    TABLE ACCESS (FULL) OF CUS_CARD_ISS (Cost=3784
    Card=1495095 Bytes=40367565)


    Thanks,
    Rama
    K.M.Ramakrishnan

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    its really hard to read that code and explain plan.

    format your code, using the [ code ] and [ /code ] tags (no spaces)

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    You need to ask your self, why a Full Table Scan is being performed on the partitioned tables CUS_PERS and CUS_CARD_ISS.
    PARTITION HASH (ALL)
    TABLE ACCESS (FULL) OF CUS_PERS (Cost=3615
    Card=1796560 Bytes=41320880)
    PARTITION HASH (ALL)
    TABLE ACCESS (FULL) OF CUS_CARD_ISS (Cost=3784
    Card=1495095 Bytes=40367565)
    Are there any Indexes on the table ?
    If not, you can create Global/Local Prefixed/Non-Prefixed Indexes on the table, in order to improve table access.

    Prefixed Local Index - The index key is identical to the partitioning key.
    Non-Prefixed Local Index - The index key is not identical to partitioning key.
    Global Prefixed Index - The index key is identical to the partitioning key, but allows different partitioning from that of the underlying table.

    Identify the candidate columns for indexes and create proper indexes on the partitioned tables.

    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Whether or not you need to do a full table scan accross your partitions depends on whether you are selecting results from more than one partition as well as what local and global indexes you have. Since I don't know either this is my suggestion.

    Code:
    SELECT ccmr.card_name,       ccis.cus_id, 
           ccac.tier_status_ind, ccac.acct_status_ind,
           NVL( TO_CHAR( ccis.card_exp_dt, 'dd/mm/yyyy hh:mm:ss' ), 'NIL' ) 
           AS exp_date
    FROM   ( SELECT int_id, tier_status_ind, acct_status_ind
               FROM cus_account 
                 prg_cd              = 'KF'                        AND
                 acct_status_ind    IN ( 'A', 'D', 'E' )           AND
                 tier_status_ind    IN ( 'Q', 'T', 'L' )           AND
                 ignore_flg          = 'N'                         AND
               ( card_valid_till_dt IS NULL                        OR
                 card_valid_till_dt >= SYSDATE ) ) ccac,  
           ( SELECT int_id, cus_id
               FROM cus_card_iss
                 ignore_flg          = 'N'                         AND
               ( card_ind           != 'S'                         OR
                 card_ind IS NULL )                                AND
               ( card_valid_till_dt IS NULL                        OR
                 card_valid_till_dt >= SYSDATE ))  ccis, 
            cus_pers ccmr
    WHERE  ccac.int_id              = ccmr.int_id                  AND
           ccac.int_id              = ccis.int_id;
    this space intentionally left blank

Posting Permissions

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



Click Here to Expand Forum to Full Width