| Adding ORDER BY increases parse time dramatically [message #215997] |
Wed, 24 January 2007 10:13  |
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 #216003 is a reply to message #216001] |
Wed, 24 January 2007 10:28  |
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 |
rleishman
Messages: 3728
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 |
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 |
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 #216248 is a reply to message #216246] |
Thu, 25 January 2007 14:10 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
