Home » RDBMS Server » Performance Tuning » Need to improve query performance
Need to improve query performance [message #287579] Wed, 12 December 2007 14:30 Go to next message
vijay_82
Messages: 1
Registered: December 2007
Junior Member
I have this query

create table ZZTSR071ABMMD000 nologging as
select a16.STATE_ID STATE_ID,
a15.CAL_QTR_START_DT CAL_QTR_START_DT,
a13.AZ_BRD_PRDGRP_ID AZ_BRAND_ID,
a12.MMA_PAYER_ID AZ_HCA_CUST_ID,
a18.AZ_MKT_ID AZ_MKT_ID,
sum(a11.TRX_COUNT) TRX
from CDW_RS.MTHLY_RX_DATA_BRAND_HCP_FCT a11
join CDW_RS.CUST_IMSPLAN_MMAPAYER_REL_V a12
on (a11.AZ_PLAN_CUST_ID = a12.IMS_PLAN_ID)
join CDW_RS.PROD_BRDPRDGRP_FCT_REL_LKP_V a13
on (a11.AZ_PROD_ID = a13.AZ_BRD_JOIN_ID)
join CDW_ADS.DIST_CHAN_DIM a14
on (a11.DIST_CHAN_SK = a14.DIST_CHAN_SK)
join CDW_RS.CAL_MONTH_LKP_V a15
on (a11.CAL_MONTH_DT_SK = a15.CAL_DT_SK)
join CDW_ADS.GEO_DIM a16
on (a11.CUST_INCTV_ZIP_CD = a16.ZIP)
join CDW_RS.PROD_BRAND_MKT_REL_LKP_V a17
on (a13.AZ_BRD_PRDGRP_ID = a17.AZ_BRAND_ID)
join CDW_RS.PROD_SBMKT_LKP_V a18
on (a17.AZ_SUB_MKT_ID = a18.AZ_SUB_MKT_ID)
where (a18.AZ_MKT_ID in (104485)
and a15.CAL_QTR_START_DT in (To_Date('2006-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2006-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2007-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
and a16.STATE_ID in ('CA', 'AL', 'FL', 'TX', 'NY', 'PA', 'NC', 'OH', 'TN', 'MO', 'GA')
and a14.DIST_CHAN_LEVEL_1_ID in ('M', 'R'))
group by a16.STATE_ID,
a15.CAL_QTR_START_DT,
a13.AZ_BRD_PRDGRP_ID,
a12.MMA_PAYER_ID,
a18.AZ_MKT_ID

This query takes about 25 mins to execute. I need to reduce the time as much as possible. I'm attaching the plan for the same.
Any suggestions in improving the performance is welcome.

Thanks,
Vijay
  • Attachment: SQL Plan.xls
    (Size: 19.50KB, Downloaded 313 times)
Re: Need to improve query performance [message #287580 is a reply to message #287579] Wed, 12 December 2007 14:35 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Which of the suggestions in the following URL have you tried & what were the results?
http://www.orafaq.com/forum/t/84315/74940/
Previous Topic: IO Performance Problems
Next Topic: DBLink performance issues
Goto Forum:
  


Current Time: Thu Dec 08 14:10:14 CST 2016

Total time taken to generate the page: 0.10521 seconds