Query tuning question

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?

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

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?

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?

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..

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...