TIP 51# : Performance issue after upgrade to 10g.
I faced with a performance issue for a client right after upgrade from 9i to 10g.
We all know that optimizer in 10g is different animal which is supposed to work better and more inteligent.This is almost true but not always.
Some queries after upgrade from 9i to 10g may have performance issue which means they may run slower in 10g.
For figuring out whether or not performance issue is because of new enhanced features in 10g please turn off the following options and run query in 10g.
alter session set "_optimizer_cost_based_transformation" =off; (Disable subquery unnesting and view merging -- New 10g optimizer feature).
alter session set "_gby_hash_aggregation_enabled" = FALSE; (Disable Hash group by aggregation -- New 10g optimizer feature).
If disabling above options resolve the situation, the performance issue is definitely because of new features in 10g optimizer. Otherwise, optimizer would not be culprit.