Re: DB performance after upgrade from 9i to 11gR2

if your test db was really performing good, if parameters that matter are the same on both databases then try importing statistics from test db into your prod system. If (if) your tables have statistics - if sampling is used then well...
It is fast. You can allways revert to previous stats version - well, take a backup if existing stats just in case.
Do not forget system statistics too.
Next idea (if test is performing well): you can import plans from your test db as well - what is already in test db shared pool cache (v$ views) might be better than what you have in your prod database. However if literall SQL is miss-performing then well..

Next option: playing arround with parameters. Well, you can play with parameters like _optimizer_cost_based_transformation=off but analysis of worst performing queries should come first then. It will take time and work.

Please consider the environment before printing this e-mail

From:	Bheemsen Aitha <baitha_at_itradenetwork.com>
To:	"oracle-l_at_freelists.org" <oracle-l_at_freelists.org>,
Date:	2014.05.08 09:41
Subject:	DB performance after upgrade from 9i to 11gR2
Sent by:	oracle-l-bounce_at_freelists.org

Hello DBA gurus,

We recently upgraded our database from 9i to 11gR2 on Solaris 11 64-bit, Veritas File system (single instance). We tested thoroughly in our QA environment before the upgrade. But after the upgrade we are seeing terrible performance in production with many of the queries. Even though the query plan looks good with very low cost, the queries are just sitting there and not finishing. Some of the queries have differences in plan (FTS to index scans on small tables) when compared to QA environment. We gathered stats with default auto sample size and default method_opt options. I opened a ticket with oracle on the performance issue, but I doubt if I can get a solution on this. The QA and prod environments are exactly same, but the queries are running are faster in QA. Did anyone come across similar problem in the past? Any inputs are greatly appreciated.

Thanks
BA

--
http://www.freelists.org/webpage/oracle-l