-
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
-
its really hard to read that code and explain plan.
format your code, using the [ code ] and [ /code ] tags (no spaces)
-
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
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|