Without an examination of the “before” environment and load versus the incumbent hardware and the current gap or headroom between actual performance and service level requirements I think it would be less than responsible to spit back a reply.

BUT: One thing I can urge you to do is dump out the existing plans of your essential and most frequently used queries (regardless of current query cost as planned with 9iR2). While there is a general improvement in performance going to 10g, it is almost certain that some of your current queries will get replanned under 10g such that they consume an inordinate amount of resources. Being able to look back to see a plan that worked well is a huge shortcut to reestablishing a good plan in 10g. If you can leave a testbed behind on your current release, then of course you can wait to generate the old acceptable plan for any queries that become noticeable after the upgrade. Also, this may eliminate exercises in “plan tweaking” if the old plan was the same or demonstrably worse so you can quickly turn your efforts to looking for a bottleneck or dysfunction in the environment of the new database.

Regards,

mwf