Home » SQL & PL/SQL » SQL & PL/SQL » Tuning SQL query
Tuning SQL query [message #202000] Tue, 07 November 2006 15:20 Go to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Hello experts,

I've a query which is running long. Could you please have a look at this and give me solution which should run faster (Using exists or whichever way).

Thanks in advance on this.

Query:

select ndb.plan_ctry, ndb.division, ndb.material, ndb.cust_need_dt,
stg.total_ord_qty stg_total_ord_qty,
stg.open_ord_qty stg_open_ord_qty,
stg.cncl_ord_qty stg_cncl_ord_qty,
stg.ship_ord_qty stg_ship_ord_qty,
ndb.total_ord_qty ndb_total_ord_qty,
ndb.open_ord_qty ndb_open_ord_qty,
ndb.cncl_ord_qty ndb_cncl_ord_qty,
ndb.ship_ord_qty ndb_ship_ord_qty
from dp_fc_recon_stg stg, dp_fweq_need_dt_book ndb, dp_system_date dt, dp_fweq_product prd
where dt.dp_region = 'USA'
and dt.dp_plan_ctry = stg.plan_ctry
and dt.dp_division = stg.division
and prd.plan_ctry = stg.plan_ctry
and prd.division = stg.division
and prd.material = stg.material
and prd.sap_prod_stat_cd in ('ACT','DEV')
and stg.plan_ctry = ndb.plan_ctry
and stg.division = ndb.division
and stg.material = ndb.material
and stg.forecast_dt = ndb.cust_need_dt
and ndb.sply_chain_grp = 'S1'
and ndb.afs_category = '01'
and ndb.sap_prod_st_cd = 'ACT'
and ndb.sales_doc_typ = 'Z001'
and ndb.src_id = 'SAP'
and ndb.cust_need_dt >= trunc(add_months('01-OCT-2006', 4 * -1), 'mm')
and (
stg.total_ord_qty <> ndb.total_ord_qty
or stg.open_ord_qty <> ndb.open_ord_qty
or stg.cncl_ord_qty <> ndb.cncl_ord_qty
or stg.ship_ord_qty <> ndb.ship_ord_qty )
order by ndb.plan_ctry, ndb.division, ndb.material, ndb.cust_need_dt



Kind Regards,
Vish
Re: Tuning SQL query [message #202049 is a reply to message #202000] Wed, 08 November 2006 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
add
and 1 = 2

That will speed things up!

What do you expect from us?
We have NO IDEA
- how many rows each table contains
- which indexes you have
- how your data is spread (eg how many dp_region = 'USA'?)
- what you consider "running long"
etc.

by the way, add_months takes a date as first parameter; '01-OCT-2006' is NOT a date, it is a string.
Re: Tuning SQL query [message #202107 is a reply to message #202000] Wed, 08 November 2006 04:01 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
use '01-OCT-06' instead of '01-OCT-2006' and say whether that speed things up a bit

[Updated on: Wed, 08 November 2006 04:09]

Report message to a moderator

Re: Tuning SQL query [message #202112 is a reply to message #202000] Wed, 08 November 2006 04:11 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Can you run explain plan on the query and post the results for review? The explain plan may give some full table scan and you can see some index missing or some joins are not proper.

Jayg
Fun Programming with Oracle & Visual C++
Re: Tuning SQL query [message #202164 is a reply to message #202107] Wed, 08 November 2006 07:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
amul wrote on Wed, 08 November 2006 05:01
use '01-OCT-06' instead of '01-OCT-2006' and say whether that speed things up a bit


Huh? How the heck is that going to speed things up? Go from one string to another string?
Re: Tuning SQL query [message #202166 is a reply to message #202164] Wed, 08 November 2006 07:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Less characters, so comparison is faster.
Re: Tuning SQL query [message #202168 is a reply to message #202166] Wed, 08 November 2006 07:57 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Oh yeah, I should have thought of that Wink
Re: Tuning SQL query [message #202219 is a reply to message #202112] Wed, 08 November 2006 15:55 Go to previous messageGo to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Hello,

Below is the explain plan on this query. Also I added Amul's comments. Magic happenned. It reduced cost whole lot. Thanks Amul.

""use '01-OCT-06' instead of '01-OCT-2006' and say whether that speed things up a bit""

Explain Plan I added as attachment.

Thanks
--Vish
Re: Tuning SQL query [message #202220 is a reply to message #202107] Wed, 08 November 2006 15:56 Go to previous messageGo to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Amul,

Tons of thanks. It reduced lot of cost when I ran the query.

Thanks again.

--Vish
Re: Tuning SQL query [message #202252 is a reply to message #202220] Wed, 08 November 2006 20:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you serious? Or are you just pulling our leg?

Post the SQL and plan with '2006'. I for one would love to know how this magical trick works.

Ross Leishman
Re: Tuning SQL query [message #202292 is a reply to message #202252] Thu, 09 November 2006 00:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The only thing I can imagine happening is that using 2006 an index is used, causing a bad path, whereas 06 gets translated to 0006, causing a FTS.

Why don't you follow my advice and treat '01-OCT-blabla' as it should be: as a date.
'01-OCT-06' is a string, it is NOT a date. Wrap it in an explicit to_date.
Re: Tuning SQL query [message #202308 is a reply to message #202252] Thu, 09 November 2006 01:30 Go to previous messageGo to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Hello,

Attached here is the explain plan with year "2006"

Thank
Vish
Re: Tuning SQL query [message #202309 is a reply to message #202292] Thu, 09 November 2006 01:37 Go to previous messageGo to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Frank,

Could you please clarify me, what you mean by """"'01-OCT-06' is a string, it is NOT a date. Wrap it in an explicit to_date."""".

If so, how to write that way. Add_months only except string as a date. How can I write your way please....

Also can I use ""EXISTS" to make the query faster.................

As always
Thank you
Re: Tuning SQL query [message #202315 is a reply to message #202309] Thu, 09 November 2006 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add_months accepts only a date as first parameter.
Rewrite it like this:

 and ndb.cust_need_dt >= trunc(add_months(to_date('01-OCT-2006', 'dd-MON-yyyy'), 4 * -1), 'mm')

Re: Tuning SQL query [message #202326 is a reply to message #202315] Thu, 09 November 2006 02:37 Go to previous messageGo to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Frank,

No luck I tried with this(to_date). No change in the cost. Attached here with explain plan.

I guess would try with ""EXISTS"" oracle statement. Don't know how we can write this. Please help.

Vish
  • Attachment: Exp_plan.txt
    (Size: 3.28KB, Downloaded 147 times)
Re: Tuning SQL query [message #202328 is a reply to message #202326] Thu, 09 November 2006 02:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This has NOTHING to do with cost or an alternate plan. It's just a matter of good coding.
Re: Tuning SQL query [message #202366 is a reply to message #202328] Thu, 09 November 2006 06:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Frank,
Exists do make a difference if you use it wisely. If you see in his case he is not interested in any of the values of the following tables (dp_system_date dt, dp_fweq_product prd
). He can use the exists clause to probe into it provided it is using the appropriate indexes otherwise it will prove expensive. For further information check in asktom website discussion regarding in vs exists.

Also have a think of using inline query to select values from dp_fweq_need_dt_book ndb.

and ndb.sply_chain_grp = 'S1'
and ndb.afs_category = '01'
and ndb.sap_prod_st_cd = 'ACT'
and ndb.sales_doc_typ = 'Z001'
and ndb.src_id = 'SAP'
and ndb.cust_need_dt >= trunc(add_months('01-OCT-2006', 4 * -1), 'mm').

Assume you have 1 mill rows in table dp_fweq_need_dt_book. After applying the above condition you get around 100K rows. I think it may be efficient by having a subselect and join it with your main query. Also try using bind variable rather than defining constants.

Something like this

var a number;
exec :a := 1;
select * from test where sno = :a;

By doing this you might save some time in parsing. Also you could do the trunc(add_months....) processing similarly.

I will be more interested in seeing your tkprof rather than your explain plan. Because your explain plan is may not be accurate always. Refer asktom website why that be the case. He always recommend to get your actual statistics from tkprof because oracle may decide to choose altogether a different plan when it executes your code.

I have not tried anything. So good luck in your r&d.

cheers
Re: Tuning SQL query [message #202377 is a reply to message #202366] Thu, 09 November 2006 07:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sigh.
Did you even bother to read my posts (other than just the last one)?

Then
- what makes you think my reply was a reaction to the 'exists' question
- WHY DO YOU CODE trunc(add_months('01-OCT-2006', 4 * -1), 'mm') ???

Further, if you are trying to tune one single instance of one single query, using binds instead of fixed values will not help.

@OP: When was the last time your tables & indexes were analyzed?
Re: Tuning SQL query [message #202493 is a reply to message #202377] Fri, 10 November 2006 00:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just back to the original - and actually interesting - point. The difference between the plans is interesting.

The change in the predicate from 06 to 2006 has altered the CBO's estimate of how many rows will be returned. Unintuitively, it thinks the 2006 example will return fewer rows - but if it was making the mistake of thinking 06 = 0006 or 1906, then it should be the other way around.

The general structure of the plan is almost identical, but it has swapped the order of two of the hash joins in the 06 example, using the lower estimate of rows returned by the predicate to select that table as the "hashed" table in the join.

I don't know whether @amul is an absolute genius or - as we say in Australia - a "tin-arse" (very lucky person). But to think that providing an alternative date format (that Oracle will still have to cast) will result in a more accurate estimate of rows returned is - I think - a bit of a stretch.

The correct way to tune this query is to:
  • Run the Explain Plan and note that it is estimating 1,008,558 rows returned from NSCDPREP.DP_FC_RECON_STG, which is way more than it should.
  • Analyze statistics - see if it helps
  • Provide a TO_DATE function so that poor old Oracle doesn't have to cast the character date. See if that helps.
  • Make sure you have a histogram for the cust_need_dt column. Add one if necessary. See if that helps.
  • Provide a CARDINALITY hint because the optimizer clearly has no idea how to estimate.
    SELECT /*+CARDINALITY(ndb,100000)*/ ...

Once the CBO has an accurate idea of the number of rows it will be processing, it should be able to make a good decision on the plan.

Ross Leishman
Re: Tuning SQL query [message #202498 is a reply to message #202493] Fri, 10 November 2006 01:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
Unintuitively, it thinks the 2006 example will return fewer rows

I think you mean the other way around..
Re: Tuning SQL query [message #202553 is a reply to message #202000] Fri, 10 November 2006 06:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Apologies frank. I have read the entire article. It's just a manufacturing defect (i meant my brain). I didn't try to pin point your mistake. I thought you were talking about exists. After reading it twice i do realise you are talking about the to_date and not exists.

comment about your second point :
WHY DO YOU CODE trunc(add_months('01-OCT-2006', 4 * -1), 'mm'). I just copied it and pasted it. I didn't code it. If i would have done i wouldn't even bother to use add_months or trunc because the date is hardcoded. so it is literally waste of time.

Further, if you are trying to tune one single instance of one single query, using binds instead of fixed values will not help.


I am not just talking about bind variable. I am trying to say is remove that join and create an inline view and do a subselect. Having said that i have not looked into the explain plan so i could be completely wrong.

Anyways no offence

cheers
Re: Tuning SQL query [message #202675 is a reply to message #202553] Fri, 10 November 2006 20:31 Go to previous messageGo to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Rajaram,

The reason is hard coded the year to run the query. This query I need to use in my SQL package and date field is going into ADD_MONTHS is variable field, not 01-OCT-2006. I do not understand why we are worried about ADD_MONTHS.

Can anyone show me how we can write this one using ""EXISTS"

Much appreciate it.

Vish
Re: Tuning SQL query [message #202773 is a reply to message #202498] Sun, 12 November 2006 04:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Frank wrote on Fri, 10 November 2006 18:18
Quote:
Unintuitively, it thinks the 2006 example will return fewer rows

I think you mean the other way around..

Glad someone's taking notice. Lord knows I'm not...
Thanks for the correction Frank.

[Updated on: Sun, 12 November 2006 04:46]

Report message to a moderator

Re: Tuning SQL query [message #202782 is a reply to message #202675] Sun, 12 November 2006 10:58 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi Vimal

Why are you using 'trunc(add_months('01-OCT-2006', 4 * -1), 'mm')' it is giving the same result as 'add_months('01-OCT-2006', 4 * -1)'

Ashu

[Updated on: Sun, 12 November 2006 10:58]

Report message to a moderator

Re: Tuning SQL query [message #203860 is a reply to message #202782] Thu, 16 November 2006 11:48 Go to previous message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Hello,

1) Run this

select trunc(add_months('25-OCT-2006', 4 * -1), 'mm') from dual

2) Run this

select add_months('25-OCT-2006', 4 * -1) from dual

Please tell me did I get the same answer!!!!

I want first day of the month.

Thanks
Vish
Previous Topic: How to create a grand total from multiple SELECTs
Next Topic: mutating
Goto Forum:
  


Current Time: Sun Dec 04 14:23:21 CST 2016

Total time taken to generate the page: 0.09508 seconds