Query tuning question

[tuseau]

Hi,

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.

Any thoughts?

Thanks.

[LKBrwn_DBA]

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.

[tuseau]

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.

[davey23uk]

Hi,

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.

Any thoughts?

Thanks.

post the query and the two execution plans

[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?

[tuseau]

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.

[davey23uk]

without posting the execution plans its impossible

[gtcol]

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.

gtcol

[PAVB]

I'm not sure how much use this is without knowledge of the db schema

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.

[tuseau]

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.

gtcol

Yes, they are definitely for the same query.