Query tuning question
I'd be grateful for any insight that might help me with this problem.
I have a query in oracle - it's fairly large, and it has quite a lot of joins involved (about 15). It runs fine in 9i but in 10g it's very slow (about 200 seconds). What stumps me is that it is very slow even on a database with NO DATA in it. It appears to only become very slow when I reach a certain number of joins in the query. Does anyone have advice for pinpointing exactly where the problem might be here? I think it's because the optimizer is taking a long time to determine the best execution plan, and have considered using the /*+ ordered */ hint to get around this, but so far to no avail.
Maybe it's a bug
If you upgraded your 10g version to 10.2.0.3 and the OS is Solaris/AIX or HP-UX, then there is an optimizer bug.
Check Metalink for AIX/HP-UX patch or Solaris work-around.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Thanks for the reply...I should have mentioned this is running on Windows Server 2003.
It's Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
I don't think it's an issue with the particular instance, as I've tested it on other instances of 10g.
post the query and the two execution plans
Originally Posted by tuseau
I'm not sure how much use this is without knowledge of the db schema, but here is the query:
/* removed */
Not sure how to post an execution plan here where it would actually make sense and not look like a total mess...can I export an execution plan?
Last edited by tuseau; 06-27-2008 at 05:23 PM.
So in fact there are at least 25 joins here.
I'm wondering if there's anything that can be done that doesn't require massive restructuring of the db or the views...
Is there anything here that could create some kind of "loop" in the optimizer?
It would seem the 10g optimizer is doing something radically different than the 9i optimizer.
without posting the execution plans its impossible
Are the execution plans both for the same query? I can see different numbers on bytes column. Please confirm and post it again with everything properly visible.
I fully support you, don't understand either why these guys want to see what you are doing... BS!... just look at the left philangy, I'm sure is something wrong with it.
Originally Posted by tuseau
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Yes, they are definitely for the same query.
Originally Posted by gtcol
Click Here to Expand Forum to Full Width