Home » RDBMS Server » Performance Tuning » Adding ORDER BY increases parse time dramatically
Adding ORDER BY increases parse time dramatically [message #215997] Wed, 24 January 2007 10:13 Go to next message
milovdl
Messages: 4
Registered: January 2007
Junior Member
I have a SQL query involving 10 tables, 2 of which are accessed via separate database links. Running that query with input that results in 0 rows takes less than a second. However, when I add an ORDER BY clause, returning 0 rows now takes over 7 seconds.

Using sql_trace, I can see that all the time is spent on parsing the SQL. Re-running the same SQL (and avoiding the parse) confirms that, since it returns almost immediately the second time.

When I run a 10053 trace on the ORDER-BY-less query, the resulting trace file is 260 KB. For the query with an ORDER BY, the resulting trace file is 211 MB. This latter trace file appears to evaluate 9882 join orders, whereas the first one only evaluates 54.

First of all, is this normal expected behavior, or is this an indication of a configuration error or an Oracle bug?

Secondly, what is the best way to "tame" the parse time on a SQL statement?

edit: Oracle 8.1.7.4.0 Enterprise edition.

[Updated on: Wed, 24 January 2007 13:14]

Report message to a moderator

Re: Adding ORDER BY increases parse time dramatically [message #216001 is a reply to message #215997] Wed, 24 January 2007 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>First of all, is this normal expected behavior, or is this an indication of a configuration error or an Oracle bug?
Yes.

>Secondly, what is the best way to "tame" the parse time on a SQL statement?
Either avoid DBLINKs or process data on the "local" node.
I suspect the inclusion of ORDER BY results in the data from the remote hosts to be brought back for sorting which increases elapsed time.
Re: Adding ORDER BY increases parse time dramatically [message #216003 is a reply to message #216001] Wed, 24 January 2007 10:28 Go to previous messageGo to next message
milovdl
Messages: 4
Registered: January 2007
Junior Member
anacedent wrote on Wed, 24 January 2007 11:23
I suspect the inclusion of ORDER BY results in the data from the remote hosts to be brought back for sorting which increases elapsed time.

But there isn't any data to be brought back from the remote hosts. The query returns 0 results, and sql_trace/tkprof shows all of the time is spent parsing, not executing or fetching.
Re: Adding ORDER BY increases parse time dramatically [message #216054 is a reply to message #216003] Wed, 24 January 2007 20:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, that sounds suspect. CBO is known to only evaluate a limited number of join paths. I don't remember where I read it, but I understand it to start ignoring some permutations from 5-tables joins upwards. So, 5*4*3*2*1 = 120 possibilities.

If yours is generating 9000+ possible paths, then it seems CBO has gone a bit haywire. It may be a bug - the only way you'll ever find out is to submit a TAR with Oracle. I cannot find anything in the Performance Tuning Manual that backs up my 5-table "fact" above, so perhaps you should include in the TAR some other examples that demonstrate how the CBO limits join permutations.

In the meantime, an ORDERED or LEADING hint will probably do the trick.

Ross Leishman
Re: Adding ORDER BY increases parse time dramatically [message #216189 is a reply to message #216054] Thu, 25 January 2007 08:30 Go to previous messageGo to next message
milovdl
Messages: 4
Registered: January 2007
Junior Member
Thank you for your input. I'll look into submitting a TAR.

Although a LEADING hint doesn't seem to do anything, an ORDERED hint takes the parse time back to under a second. Now I just have to ensure that doesn't have any adverse side-effects on the generated plan.

Thanks again!
Re: Adding ORDER BY increases parse time dramatically [message #216193 is a reply to message #215997] Thu, 25 January 2007 08:59 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hmm...when I read this, I first thought of a parameter that lets you control how long the optimizer searches for a suitable execution plan (thus how many plans it tries).

But when I just tried to find it with show parameter in my 10.2 db, I can't seem to find it, so it may be deprecated. But I'm pretty sure it used to exist, and may in your 8i db.

something like optimizer_max_permutations ? Maybe I'm imagining things though or just blind and can't see it.

Ahh, here it is in 9iR2 at least, with implication that it is in 8i as well. It must have gone obsolete in 10gR1.

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1139.htm#1021491

In 11g I hear that there will be some sort of ability to have the optimizer try for a certain amount of time now (now being first/hard parse) and then to pick back up and try a bunch more alternatives at some later point in time (as in a defined maintenance window), with it presumably updating the data dictionary to reflect a better plan if one is found, so that the next time it is parsed (soft), it will use the new and better plan. But the preceeding is just my speculation.

[Updated on: Thu, 25 January 2007 09:05]

Report message to a moderator

Re: Adding ORDER BY increases parse time dramatically [message #216246 is a reply to message #215997] Thu, 25 January 2007 14:00 Go to previous messageGo to next message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
- Does all the local tables have statistics ??
- what is your optimizer set to (since you are using 8.1.7.4.0)

BTW, yes ORDER BY does take time as it is trying to sort the output data.

Just my thoughts...
--
Sanjay
Re: Adding ORDER BY increases parse time dramatically [message #216248 is a reply to message #216246] Thu, 25 January 2007 14:10 Go to previous messageGo to next message
milovdl
Messages: 4
Registered: January 2007
Junior Member
Sanjay Bajracharya wrote on Thu, 25 January 2007 15:00
- Does all the local tables have statistics ??

Yes, the local tables, as well as the remote tables, have up-to-date statistics.

Sanjay Bajracharya wrote on Thu, 25 January 2007 15:00
- what is your optimizer set to (since you are using 8.1.7.4.0)

I assume you mean these (all default values):
optimizer_features_enable = 8.1.7
optimizer_mode = CHOOSE
optimizer_max_permutations = 80000
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
optimizer_percent_parallel = 0

Sanjay Bajracharya wrote on Thu, 25 January 2007 15:00
ORDER BY does take time as it is trying to sort the output data.

As I said earlier, the extra time is taken parsing the SQL. I know (and can verify in the trace files) that my query returns 0 rows, so there isn't any data to sort.
Re: Adding ORDER BY increases parse time dramatically [message #216252 is a reply to message #216248] Thu, 25 January 2007 14:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I didn't notice you were on 8i. Good luck getting that TAR through... Razz

Ross Leishman

[Updated on: Thu, 25 January 2007 14:24]

Report message to a moderator

Previous Topic: Industry standard best practice for Primary keys..should they be meaningful or meaningless?
Next Topic: Testing performance tweaks
Goto Forum:
  


Current Time: Fri Dec 09 17:15:43 CST 2016

Total time taken to generate the page: 0.25093 seconds