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

From: Laimutis.Nedzinskas@xxxxxx
To: baitha@xxxxxxxxxxxxxxxxx
Date: Thu, 8 May 2014 16:09:36 +0300

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@xxxxxxxxxxxxxxxxx>
To:     "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>,
Date:   2014.05.08 09:41
Subject:        DB performance after upgrade from 9i to 11gR2
Sent by:        oracle-l-bounce@xxxxxxxxxxxxx



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

Follow-Ups:
- RE: DB performance after upgrade from 9i to 11gR2
  - From: Bheemsen Aitha

References:
- DB performance after upgrade from 9i to 11gR2
  - From: Bheemsen Aitha

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

Other related posts: