Home » RDBMS Server » Performance Tuning » SQL Tuning Urgent
icon14.gif  SQL Tuning Urgent [message #265286] Thu, 06 September 2007 01:21 Go to next message
ronjubd
Messages: 2
Registered: August 2007
Location: Bangladesh
Junior Member

Dears,

I have a query like bellow:
SELECT a.contrno, a.NAME contract_name, b.NAME subscriber_name, b.subno,a.id_type, a.id_no,
s.codetext_lang status,b.PREPOST_PAID,e.EQUIPID Package_Plan, c.IMSI_NUMBER, b.creation_date,b.CHANGED,
b.reservation_code, d.description, a.billgroup,
a.createdby,d.ADDRESS , d.ADDRESS2 , d.ADDRESS3, a.CATEGORY CCATEGORY,ccm.PARENT_CC_CATEGORY, ccm.CC_LIMIT,
------------------Newly added --------------------------------------------
(select sum(nvl(R.ar_am_loc,0)) past_due
from IVM_INVOICE_DETAIL R
where r.contrno=a.contrno
group by r.contrno) past_due,
(select unbilled from ARM_DUE_UNBILL_AMT where contrno=a.contrno) unbilled
-------------------Newly added --------------------------------------------
FROM crm_departement a,
crm_user_info b,
crm_commdevice c,
crm_retailer_info d,
som_tabs_codes s,
som_tabs_codes ss,
CRM_USER_EQUIPMENTS e,
crm_cust_addrs cc,
crm_address d
,(select c.USER_CODE USER_CODE, c.PARENT_CC_CATEGORY PARENT_CC_CATEGORY, cc.CC_LIMIT CC_LIMIT from CCM_USER_INFO c,
CCM_USER_INFO cc
where c.parent_entity!='NON'
and c.PARENT_ENTITY=cc.USER_CODE
and cc.USER_TYPE='CONT') ccm
WHERE a.contrno = b.contrno
AND b.cardno = c.cardno
AND b.reservation_code = d.retailer
AND s.codename = 'SUSTAT'
AND b.status = s.code
and ss.CODENAME = 'SPC_MAINSERV'
and b.SUBNO=e.SUBNO(+)
and e.EQUIPID = ss.CODE
and a.cust_no= cc.cust_no
and cc.address_id=d.address_id
AND trunc(B.CREATION_DATE) between to_date('01-09-07','DD-MM-YY') and to_date('06-09-07','DD-MM-YY')
and d.ADDRESS_TYPE='BILL'
and b.SUBNO=substr(ccm.user_code,6,10)
--and b.contrno=ccm.user_code
--and ccm.parent_entity!='NON'
--and ccm.user_type ='CONT'
and b.PREPOST_PAID='POST'
ORDER BY b.creation_date,a.CONTRNO
/

It is too slow and i believe it can be tuned more. Please advice how can i tune this one?
The Explain plan is here...
./fa/3007/0/
Thanks in advance
Re: SQL Tuning Urgent [message #265320 is a reply to message #265286] Thu, 06 September 2007 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Putting "Urgent" in the title is one sure way to shut me up.

Regards
Michel
Re: SQL Tuning Urgent [message #265359 is a reply to message #265320] Thu, 06 September 2007 03:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What is the fascination with scalar sub-queries in the SELECT clause? Everybody seems to be using them without any comprehension of the damage they have on performance.

Don't use sub-queries in the SELECT clause. EVER! That's not quite true - there are rare cases where they're helpful - but you need the experience to spot those cases.

When you JOIN tables in the FROM clause, Oracle has the option to either NEST or UNNEST the lookup on that table/view/inline view. When you place sub-queries in the SELECT clause, Oracle has no choice - it must NEST. Nesting means that the sub-query is run once for every row returned. Unnesting means it is run once - period.

Ross Leishman
Re: SQL Tuning Urgent [message #265388 is a reply to message #265359] Thu, 06 September 2007 04:18 Go to previous messageGo to next message
ronjubd
Messages: 2
Registered: August 2007
Location: Bangladesh
Junior Member

Dear Michel,
Thank you so much for your suggessaion. I will remember this in my next post.

Dear Ross,
Thanks you very much for you suggession. Would you please give me some more tips on it ? Razz

Re: SQL Tuning Urgent [message #265415 is a reply to message #265388] Thu, 06 September 2007 05:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Which bit is not clear?

Pretend that scalar sub-queries in the SELECT clause are not permitted. How would you write your SQL now?

Ross Leishman
Re: SQL Tuning Urgent [message #342753 is a reply to message #265286] Sun, 24 August 2008 21:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Might be easier for some of us to help you if your code posting was formatted...

Kevin
Re: SQL Tuning Urgent [message #342755 is a reply to message #342753] Sun, 24 August 2008 21:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's a bit cruel. OP puts URGENT on the post and you wait a year to respond. Nasty! Wink
Re: SQL Tuning Urgent [message #342756 is a reply to message #265286] Sun, 24 August 2008 21:45 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OOPS, sorry. Was just looking at post to reply to and was not checking dates. hehehe

Kevin
Previous Topic: need suggestions to optimize the query
Next Topic: explain plan and autotrace
Goto Forum:
  


Current Time: Sat Dec 03 13:39:06 CST 2016

Total time taken to generate the page: 0.09095 seconds