Performance issue after 10g upgrade. Please help!

[bhatia333]

Hi,
We have several databases (E-bus and non-E-bus) which are being upgraded to 10g. One common thing we are seeing everywhere is performance issues after upgrade (even with larger new servers for 10g instances) and changes in explain plan. While this may be common but it becomes a problem when alter table commands also start taking longer as part of an upgrade. The difference is significant. Opening a tar with Oracle is of no use as they say to upgrade to a next level. Now we have upgraded all instances to Oracle recommended 10.2.0.3. One thing is that the performance issue goes away as soon as we set optimizer_features_enable to 9.2.0. Not sure if we can keep recommending this solution every time.
Please let me know if anyone has faced these issues and what they have done about it. The issue spans across various platforms (Linux, Solaris) and Applications (E-Bus, Peoplesoft, Kronos, Homegrown apps)

Thanks,
Sandeep

[davey23uk]

too broad a question, you will have to post specific problems

[stmontgo]

what apps are you upgrading?

[BV1963]

Any upgrades I have done I had same problems. From 7.3 to 8, from 8 to 8I,from 8 to 9, from 9 to 10 -- optimizer is changing, default behavior is changing and that is why it has to be tested . It is quite frustrating, I know. It should not be this way as long as your have provided accurate statistics levels. Perhaps upgrade from 10 to 11 will be better????????

[JChiappa]

Can I suggest http://www.oracle.com/technology/pro...10gr2_0208.pdf , optimizermagic.blogspot.com/2008/02/upgrading-from-oracle-database-9i-to.html and the metalink notes "Upgrading from 9i to 10g - Potential Query Tuning Related Issues", Doc ID: 295819.1 and "Tips for avoiding upgrade related query problems", Doc ID: 167086.1 ???? In my personal experience, an awful lot of upgrade issues can (will) be busted with this material....

Regards,

Chiappa