Home » RDBMS Server » Performance Tuning » Tuning of query (Not DBA Level Tuning)
Tuning of query (Not DBA Level Tuning) [message #268558] Wed, 19 September 2007 01:38 Go to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
hi,
i got a query regarding how to tune this for least possible execution time.i want to make it sure that i don't have DBA rights.
Other things are tables 'Plan1_Call_Detail' and 'Plan2_Call_Detail' are very big ones around 500,000 records are inserted daily and takes lot of time.These tables have an index on DAY column.
This report shows mobile_numbers which have activated with $20 but not used i.e no calls within 60 to 30 days i.e. person activated on 1st of month have 60 days and last day of a month have 30 day limit.
The SQl is as follows
select ad.mobile_number,e.equipment_name,ad.activation_date from 
top_ups t,
additons ad,
equipment_models e
where 
ad.activation_date>=to_date('01-Jun-07')
and ad.activation_date<to_date('30-Jun-07')+1
and t.mobile_number=ad.mobile_number 
and ad.addition_equipment=e.equip_model_seq
and t.top_up_amount=20
and ad.retailer_id = '12345'
and ad.report_retailer_id not in ('-777','-888','-999','-444','-333','-222','-666')
and not exists (select 1 from Plan1_Call_Detail cd 
            where day>=to_date('01-Jun-07')
            and day<to_date('30-Jun-07')+31
            and cd.account_id=ad.account_id
            and cd.call_type in ('SMS','VOICE')
	    union all
	    select 1 from Plan2_Call_Detail cd 
            where day>=to_date('01-Jun-07')
            and day<to_date('30-Jun-07')+31
            and cd.account_id=ad.account_id
            and cd.call_type in ('SMS','VOICE')
            ) 
group by ad.mobile_number,e.equipment_name,ad.activation_date 
having 
trunc(ad.activation_date)=trunc(min(t.Topup_pymt_created));


pls do help me as i am new to sql.
thanks and regards,
naw
Re: Tuning of query (Not DBA Level Tuning) [message #268560 is a reply to message #268558] Wed, 19 September 2007 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you start on tuning queries, the think you should NOT do is replace variables by constants.
For examples the dates and the ids you replaced by constants, I bet you these are variables in the actual query. Replacing them by constants and then tuning the query will lead to COMPLETELY different outcomes.

And a to_date takes two arguments: the date in string-notation with four digits for the year (didn't you learn from the Y2K problem??) and the date-format.

Would it not be more logical for a phone-application to add the account_id to the index??

[Updated on: Wed, 19 September 2007 01:45]

Report message to a moderator

Re: Tuning of query (Not DBA Level Tuning) [message #268621 is a reply to message #268560] Wed, 19 September 2007 03:21 Go to previous messageGo to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
thanks for ur reply frank.

yes ur right the are variables but i removed them just for understanding purpose.

regarding to_date i dint get u. whether the year part should be of 4 digit r of 2 digit. please be clear.

yes there is index on account_id also . sry i forgot to mention it.

regards,
naw
Re: Tuning of query (Not DBA Level Tuning) [message #268681 is a reply to message #268621] Wed, 19 September 2007 06:19 Go to previous messageGo to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
Here is the explain plan for it:
TABLE ACCESS(FULL) TOP_UPS Cost:1222206 Cardinality:109876997 Bytes:2637047928
INDEX(RANGE SCAN) PLAN1_CALL_DETAIL_PK Cost:3757832 Cardinality: 3948622 Bytes: 276403540

INDEX(RANGE SCAN) PLAN2_CALL_DETAIL_PK Cost:1736684 Cardinality: 1142674 Bytes: 79987180


And also there is index on Top_ups table i.e. on columns topup_pymt_created,account_id,ref_row_id combinely.but they are not getting used, even if i include them i.e. changed code is
T.ACCNT_ID IS NOT NULL
AND T.PYMT_CREATED>=TO_DATE('01-JUN-07')
AND T.REF_ROW_ID IS NOT NULL
select ad.mobile_number,e.equipment_name,ad.activation_date from 
top_ups t,
additons ad,
equipment_models e
where 
T.ACCouNT_ID IS NOT NULL
AND T.topup_PYMT_CREATED>=TO_DATE('01-JUN-07')
AND T.REF_ROW_ID IS NOT NULL
and ad.activation_date>=to_date('01-Jun-07')
and ad.activation_date<to_date('30-Jun-07')+1
and t.mobile_number=ad.mobile_number 
and ad.addition_equipment=e.equip_model_seq
and t.top_up_amount=20
and ad.retailer_id = '12345'
and ad.report_retailer_id not in ('-777','-888','-999','-444','-333','-222','-666')
and not exists (select 1 from Plan1_Call_Detail cd 
            where day>=to_date('01-Jun-07')
            and day<to_date('30-Jun-07')+31
            and cd.account_id=ad.account_id
            and cd.call_type in ('SMS','VOICE')
	    union all
	    select 1 from Plan2_Call_Detail cd 
            where day>=to_date('01-Jun-07')
            and day<to_date('30-Jun-07')+31
            and cd.account_id=ad.account_id
            and cd.call_type in ('SMS','VOICE')
            ) 
group by ad.mobile_number,e.equipment_name,ad.activation_date 
having 
trunc(ad.activation_date)=trunc(min(t.Topup_pymt_created));


pls help me out
thanks and regards,
naw
Re: Tuning of query (Not DBA Level Tuning) [message #268799 is a reply to message #268681] Wed, 19 September 2007 14:31 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post indexes and explain (tkprof - better)

Michael
Previous Topic: Heavy query with GL
Next Topic: top query find through sqlplus ?
Goto Forum:
  


Current Time: Tue Dec 06 08:43:05 CST 2016

Total time taken to generate the page: 0.15632 seconds