Performance Issue

From: <hami_hce2002_at_yahoo.com>
Date: 11 Aug 2005 07:42:21 -0700
Message-ID: <1123771341.530949.279270_at_g47g2000cwa.googlegroups.com>

Hi,

A query involving around 10 table joins is going for a full table scan of a huge table TB_ALL_SETTLEMENT_DOC_DETAILS. The table is been partioned on a particular field (N_CYCLE_ID) which is being used in the join clause.
The same query is using partitions in the 1 database(local) but going for a full table scan in another database(remote).The statistics of the table, indexes are all updated.The amount of data is more or less the same in both dbs .Also there is data spanning across all the partitions.

1. Could any of you tell me why the query is going for a full table scan instead of partition scan in remote db.
2. Using an EXISTS instead of IN - Cost is higher but it seems faster in the remote db.But query using EXISTS is running slower in Local db.
3. Using the clause SDD.N_CYCLE_ID = (SELECT SC.N_CYCLE_ID FROM TB_SETTLEMENT_CYCLES WHERE SC.N_SETTLEMENT_MONTH_YEAR BETWEEN 200311 AND 200311 AND SC.V_CYCLE_TYPE LIKE 'AT%' AND SDD.N_CYCLE_ID = SC.N_CYCLE_ID)

instead of

       SDD.N_CYCLE_ID IN
         (SELECT
          N_CYCLE_ID
          FROM TB_SETTLEMENT_CYCLES
          WHERE
          N_SETTLEMENT_MONTH_YEAR BETWEEN 200311  AND 200311 AND
          V_CYCLE_TYPE LIKE 'AT%')

seems to be better but it is still not using Partition Scan.

Could any of help me in figuring out y the query is going for FTS and hence running slower in the remote Db?

The query is as follows

SELECT
   AGREEMENT,
'A|BRAND ID|' || BRAND_ID || '|',
'A|TYPE OF SERVICE|' || TOS || '|',
'A|CONTENT ID|' || CID || '|',
'A|END USER PRICE|' || EUP || '|',
'P|CP|' || PARTNER_ID || '|',

   DECODE(TOP,'MCOMM', DECODE(MID, 2, 'AP',FIN_FLOW), FIN_FLOW),    MATRIX_ID,
   CDR_AGGREGATOR_ID,
   CDR_AGGREGATOR_TYPE,

   DECODE(RESULT_TYPE,'V',VOLUME,'R',AMOUNT) VOLUME,
   DECODE(TOP,'MCOMM',
   DECODE(MID, 2, RETAIL, 3, WHOLESALE, 4, RETAIL + WHOLESALE),
   DECODE(FIN_FLOW, 'AP',RETAIL,'AR',WHOLESALE))  REVENUE

FROM(
    SELECT

	SDD.N_AGREEMENT_ID AGREEMENT,
	NVL(SDD.N_BRAND_ID,0) BRAND_ID,
	TOS1.V_TYPE_OF_SERVICE TOS,
	TOS1.V_TYPE_OF_PRODUCT TOP,
	BONUS.N_BONUS_MODEL_ID MID,
	SDD.V_CONTENT_ID CID,
	SDD.N_END_USER_PRICE EUP,
	SDD.N_PARTNER_ID PARTNER_ID,
	CH.C_FINANCIAL_FLOW FIN_FLOW,
	MA.V_MATRIX_ID MATRIX_ID,
	MA.C_RESULT_TYPE RESULT_TYPE,
	CDR_AG.N_CDR_AGGREGATOR CDR_AGGREGATOR_ID,
	CDR_AG.V_AGGREGATOR_TYPE_ID CDR_AGGREGATOR_TYPE,
	SUM(SDD.N_VOLUME) VOLUME,
	SUM(SDD.N_AMOUNT) AMOUNT,
	DECODE(SUM(SDD.N_VOLUME),0,SUM(SDD.N_AMOUNT),SUM(SDD.N_VOLUME*SDD.N_END_USER_PRICE))
RETAIL,
	SUM(NVL(SDD.N_PROMO_AMOUNT, SDD.N_FINAL_AMOUNT))  WHOLESALE
    FROM
	TB_BONUS BONUS,
	TB_BONUS_TYPE_OF_SERVICE BONUS_TOS,
	TB_CHARGES CH,
	TB_ALL_SETTLEMENT_DOC_DETAILS   SDD,
	TB_SETTLEMENT_DOCUMENTS SDOC,
	TB_MATRIX_CDR_AGGREG_CHARGES MA_CDR_AG_CH,
	TB_CDR_AGGREGATORS CDR_AG,
	TB_MATRIX MA,
	TB_BONUS_MODEL_TOS_COMP_UNIT BONUS_TOS_COMP,
	TB_TYPE_OF_SERVICE TOS1
    WHERE
	SDOC.N_PARTNER_ID = 40016 AND
	SDOC.N_DOCUMENT_ID = SDD.N_DOCUMENT_ID AND
	BONUS.N_BONUS_ID = 4 AND
	BONUS.N_BONUS_ID = BONUS_TOS.N_BONUS_ID AND
	SDD.V_TYPE_OF_SERVICE = BONUS_TOS.V_TYPE_OF_SERVICE AND
	BONUS_TOS.V_TYPE_OF_SERVICE = TOS1.V_TYPE_OF_SERVICE AND
	SDD.N_CYCLE_ID IN
	    (SELECT
	    N_CYCLE_ID
	    FROM TB_SETTLEMENT_CYCLES
	    WHERE
	    N_SETTLEMENT_MONTH_YEAR BETWEEN 200311  AND 200311 AND
	    V_CYCLE_TYPE LIKE 'AT%') AND
	NVL(SDD.N_PROMO_CHARGE_ID, SDD.N_CHARGE_ID) = CH.N_CHARGE_ID AND
	CH.C_CHARGE_TYPE = 'U' AND
	CH.C_CHARGE_LEVEL = 'A' AND
	BONUS.N_BONUS_MODEL_ID = BONUS_TOS_COMP.N_BONUS_MODEL_ID AND
	TOS1.V_TYPE_OF_SERVICE = BONUS_TOS_COMP.V_TYPE_OF_SERVICE AND
	CH.C_FINANCIAL_FLOW = BONUS_TOS_COMP.C_FINANCIAL_FLOW AND
	SDD.V_COMP_UNIT = BONUS_TOS_COMP.V_COMP_UNIT AND
	NVL(SDD.N_PROMO_CHARGE_ID, SDD.N_CHARGE_ID) = MA_CDR_AG_CH.N_CHARGE_ID
AND
	MA_CDR_AG_CH.N_CDR_AGGREGATOR = CDR_AG.N_CDR_AGGREGATOR AND
	MA_CDR_AG_CH.V_MATRIX_ID = MA.V_MATRIX_ID
    GROUP BY
	SDD.N_AGREEMENT_ID,
	SDD.N_BRAND_ID,
	TOS1.V_TYPE_OF_SERVICE,
	TOS1.V_TYPE_OF_PRODUCT,
	BONUS.N_BONUS_MODEL_ID,
	SDD.V_CONTENT_ID,
	SDD.N_END_USER_PRICE,
	SDD.N_PARTNER_ID,
	CH.C_FINANCIAL_FLOW,
	MA.V_MATRIX_ID,
	MA.C_RESULT_TYPE,
	CDR_AG.N_CDR_AGGREGATOR,
	CDR_AG.V_AGGREGATOR_TYPE_ID
    ORDER BY
	SDD.N_AGREEMENT_ID,
	MA.V_MATRIX_ID,

    CDR_AG.V_AGGREGATOR_TYPE_ID ); Thanks,
Hamidha Received on Thu Aug 11 2005 - 16:42:21 CEST