Query tuning question

Printable View

06-24-2008, 01:59 PM
tuseau

Query tuning question

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.
06-24-2008, 02:22 PM
LKBrwn_DBA

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.
:p
06-24-2008, 02:35 PM
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.
06-24-2008, 04:04 PM
davey23uk

Quote:

Originally Posted by 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.

post the query and the two execution plans
06-24-2008, 04:56 PM
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?
06-24-2008, 05:04 PM
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.
06-24-2008, 05:37 PM
davey23uk

without posting the execution plans its impossible
06-25-2008, 03:05 AM
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
06-25-2008, 10:01 AM
PAVB

Quote:

Originally Posted by tuseau

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.
06-25-2008, 10:46 AM
tuseau

Quote:

Originally Posted by 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

Yes, they are definitely for the same query.
06-25-2008, 10:55 AM
tuseau

Quote:

Originally Posted by PAVB

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.

I'm convinced this is something in the 10g optimizer, myself.

What's the left philangy?
06-25-2008, 11:27 AM
Tuma

suggestion replace the 10g stats with stats exported from the 9i...
06-25-2008, 11:46 AM
PAVB

Quote:

Originally Posted by Tuma

suggestion replace the 10g stats with stats exported from the 9i...

...poster already has the left philangy compromissed, do you want for him/her to screw-up the right one too?
06-25-2008, 11:53 AM
tuseau

Quote:

Originally Posted by Tuma

suggestion replace the 10g stats with stats exported from the 9i...

Since the database contains no data, do you think the stats are the issue?
06-25-2008, 01:44 PM
Tuma

Quote:

Originally Posted by PAVB

...poster already has the left philangy compromissed, do you want for him/her to screw-up the right one too?

They can export the 10 stats and bring them back if it get worse..
06-25-2008, 05:39 PM
tuseau

After some testing, I am 95% sure this is a bug in the 10g optimizer.

The same query takes 0.3 seconds in 9i.
In 10g, it takes 160 seconds.
Both databases have no data in them.
The time is simply the optimizer creating the execution plan, since there are no rows returned for either query.

I tried setting the following in the 10g session:
alter session set "_optimizer_cost_based_transformation" =off;
alter session set "_gby_hash_aggregation_enabled" = FALSE;
But it made no difference.

Does anyone know of other differences in the 10g optimizer that might be causing this? I have read that there are many differences, but going through them might take a while.

I'm thinking it might be time to call oracle support...