Home » SQL & PL/SQL » SQL & PL/SQL » Slow response
Slow response [message #187330] Sat, 12 August 2006 04:16 Go to next message
avi123india
Messages: 5
Registered: December 2005
Junior Member
Hi,

I'm running a query it's taking more than hrs to refresh for a month. could anyone help me to tune.

table asl_transaction_fact_view has 33 milion, asl_customer_fact_view has 268 million rows.

The cost of this line is very high. pls note transaction_date in asl_transaction_fact_view table has bitmap and activity_date in asl_mv_activity_date has btree index. will this cause a problem ?
and both are join with date_key as below

ASL_MV_ACTIVITY_DATE.DATE_KEY=ASL_TRANSACTION_FACT_VIEW.ACTIVITY_DATE_KEY )


Query
======

SELECT ASL_TIER.TIER_CODE,
ASL_PARTNER_TRN.PARTNER_CODE,
NVL(Sum(Decode(( ASL_TRANSACTION_FACT_VIEW.ACTIVITY_INDICATOR ), 'EMA', ( ASL_TRANSACTION_FACT_VIEW.REDEMPTION_MILES ) * ( ASL_TRANSACTION_FACT_VIEW.TRNS_ENTRY_TYPE ), NULL)),0),
NVL(Count ( Distinct ( ASL_TRANSACTION_FACT_VIEW.CUSTOMER_KEY ) ),0),
Count ( Distinct ( ASL_CUSTOMER_FACT_VIEW.CUSTOMER_KEY ) )
FROM
ASL_TIER,
ASL_PARTNER ASL_PARTNER_TRN,
ASL_TRANSACTION_FACT_VIEW,
ASL_CUSTOMER_FACT_VIEW,
ASL_MV_ACTIVITY_DATE,
ASL_ACTIVITY_TYPE,
ASL_CUSTOMER
WHERE
( ASL_TIER.TIER_KEY=ASL_CUSTOMER_FACT_VIEW.TIER_KEY )
AND
(ASL_CUSTOMER.CUSTOMER_KEY=ASL_CUSTOMER_FACT_VIEW.CUSTOMER_KEY )
AND ( ASL_MV_ACTIVITY_DATE.DATE_KEY=ASL_TRANSACTION_FACT_VIEW.ACTIVITY_DATE_KEY )
AND ( ASL_ACTIVITY_TYPE.ACTIVITY_TYPE_KEY=ASL_TRANSACTION_FACT_VIEW.ACTIVITY_TYPE_KEY )
AND ( ASL_CUSTOMER.CUSTOMER_KEY=ASL_TRANSACTION_FACT_VIEW.CUSTOMER_KEY )
AND ( ASL_PARTNER_TRN.PARTNER_KEY=ASL_TRANSACTION_FACT_VIEW.PARTNER_KEY )
AND (
ASL_MV_ACTIVITY_DATE.ACTIVITY_DATE between '1-Jul-2006' and '31-Jul-2006' -- 1 hrs
AND ASL_PARTNER_TRN.PARTNER_CODE = 'EK'
AND ASL_ACTIVITY_TYPE.ACTIVITY_TYPE_CODE = 'FLT'
)
GROUP BY
ASL_TIER.TIER_CODE,
ASL_PARTNER_TRN.PARTNER_CODE


Expain Plan :
==============
SELECT STATEMENT, GOAL = ALL_ROWS Cost=21 Cardinality=1 Bytes=93
SORT GROUP BY Cost=21 Cardinality=1 Bytes=93
NESTED LOOPS Cost=19 Cardinality=1 Bytes=93
NESTED LOOPS Cost=18 Cardinality=1 Bytes=86
NESTED LOOPS Cost=17 Cardinality=1 Bytes=66
HASH JOIN Cost=16 Cardinality=1 Bytes=61
NESTED LOOPS Cost=8 Cardinality=8 Bytes=184
NESTED LOOPS Cost=3 Cardinality=1 Bytes=14
TABLE ACCESS BY INDEX ROWID Object name=ASL_ACTIVITY_TYPE Cost=2 Cardinality=1 Bytes=7
INDEX UNIQUE SCAN Object name=ACTIVITY_TYPE_CODE_UK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object name=ASL_PARTNER Cost=2 Cardinality=1 Bytes=7
INDEX UNIQUE SCAN Object name=PARTNER_CODE_UK Cardinality=1
TABLE ACCESS FULL Object name=ASL_TIER Cost=5 Cardinality=8 Bytes=72
TABLE ACCESS BY INDEX ROWID Object name=ASL_TRANSACTION_FACT Cost=10 Cardinality=38 Bytes=1444
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP MERGE
BITMAP KEY ITERATION
TABLE ACCESS BY INDEX ROWID Object name=ASL_PARTNER Cost=2 Cardinality=1 Bytes=7
INDEX UNIQUE SCAN Object name=PARTNER_CODE_UK Cardinality=1
BITMAP INDEX RANGE SCAN Object name=TRANSF_PARTNER_BIDX
BITMAP MERGE
BITMAP KEY ITERATION
TABLE ACCESS BY INDEX ROWID Object name=ASL_ACTIVITY_TYPE Cost=2 Cardinality=1 Bytes=7
INDEX UNIQUE SCAN Object name=ACTIVITY_TYPE_CODE_UK Cardinality=1
BITMAP INDEX RANGE SCAN Object name=TRANSF_ACT_TYPE_BIDX
BITMAP MERGE
BITMAP KEY ITERATION
TABLE ACCESS BY INDEX ROWID Object name=ASL_MV_ACTIVITY_DATE Cost=2 Cardinality=4 Bytes=44
INDEX RANGE SCAN Object name=ACTIVITY_DATE_UK Cost=2 Cardinality=4
BITMAP INDEX RANGE SCAN Object name=TRANSF_ACTIVITY_DATE_BIDX
INDEX UNIQUE SCAN Object name=CUSTOMER_KEY_PK Cardinality=1 Bytes=5
TABLE ACCESS BY INDEX ROWID Object name=ASL_CUSTOMER_FACT Cost=2 Cardinality=1 Bytes=20
INDEX RANGE SCAN Object name=CUSTOMER_FACT_CUSTKEY_IDX Cost=2 Cardinality=4
INDEX RANGE SCAN Object name=SNAPSHOT_DATE_UK Cost=1 Cardinality=1 Bytes=7


Would appreciate your early response.

Regards,
Avi
Re: Slow response [message #187348 is a reply to message #187330] Sat, 12 August 2006 08:40 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Remove the following table out of the FROM clause
ASL_PARTNER ASL_PARTNER_TRN,
ASL_MV_ACTIVITY_DATE,
ASL_ACTIVITY_TYPE,
ASL_CUSTOMER
since they contribute no columns to the SELECT clause
The can & should be subordinated into the WHERE clause using EXISTS
Previous Topic: Composite index usage
Next Topic: Frequent COMMIT is Culprit or anything else?
Goto Forum:
  


Current Time: Sat Dec 10 04:59:05 CST 2016

Total time taken to generate the page: 0.09043 seconds