DB version is 184.108.40.206.0
Basically, my question is as stated in the subject line - I have a (somewhat complex) query that only returns 1 row. Without an ORDER BY clause, it is very quick, sub-second. But once I add any kind of order by, the query suddenly takes almsot 30 seconds.
I see that in the query plan, the ORDER BY causes a few more full table scans, but I have no idea why. How would I go about getting to the bottom of this?
No, it's not always going to return only 1 row. The ORDER BY is necessary, unfortunately, for when there are multiple rows.
In other words, I can tweak the where conditions slightly so that it returns a few more rows, but I see the same effect with the performance. Fast without order by, extremely slow with it.
I see that in the query plan, the ORDER BY causes a few more full table scans, but I have no idea why.
Because if I ask you to sort every book in the library by author's last name you aren't going to use the card catalogue; you are just going to go back to the books shelves and start sorting books. Using the card catalogue is analogous to using an index.
See the FAQ for how to post a tuning query. You need to post the query, the execution plan, row counts for the tables involved, row counts for the predicates.
Are the stats on all of the tables and indexes up to date?
Ok, before I post all the explain plans, here's a new wrinkle.
I have another database server with the exact same schema and data as the problematic database.
This other database is actually 220.127.116.11.0. It doesn't have the problem with the slow ORDER BY. It's fast whether it's ordered or not.
Looking at all the explain plans, I see that I get destroyed by lack of a VIEW PUSHED PREDICATE in one particular part of the plan. In other words:
11.1 server, no ORDER BY ==> VIEW PUSHED PREDICATE ... fast
11.1 server, with ORDER BY ==> no predicate push, horrible series of hash joins and FTS resulting in 271M in the "Bytes" column of autotrace output ... sloooow
11.2 server, either way ==> VIEW PUSHED PREDICATE ... fast
So would it be reasonable to conclude that this really is just some optimizer bug that's related to the oracle version/patchlevel? Would it be advisable to try a predicate push hint?
Or could it be related to the sga/pga/etc parameters of the respective servers? They are supposed to be tuned similarly but by no means identically. The server hardware is certainly different.
So would it be reasonable to conclude that this really is just some optimizer bug that's related to the oracle version/patchlevel?
It wouldn't be reasonable for any of us to conclude anything since, so far, you have chosen NOT to post anything for us to look at that could indicate what the problem might be. Until you do you are pretty much on your own.
Would it be advisable to try a predicate push hint?
It's your database. You don't need permission from any of us to try things.