Re: DB performance after upgrade from 9i to 11gR2
- From: "David Fitzjarrell" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for DMARC)
- To: "baitha@xxxxxxxxxxxxxxxxx" <baitha@xxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 May 2014 10:22:38 -0700 (PDT)
Close isn't a match. :) Data differences can affect the standard histograms
which, in turn, affect execution plans. Check the histograms between the two
databases and you may find more differences than you expected. Also 10053
traces for the same query in both databases may shed light on what the
optimizer is doing in both installations and provide clues as to how to proceed
in getting better plans.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Thursday, May 8, 2014 11:05 AM, Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
wrote:
Yes David, they are exactly same in DB version, OS version, storage and stats.
Data is a close match.
Thanks
BA
From:David Fitzjarrell [mailto:oratune@xxxxxxxxx]
Sent: Thursday, May 08, 2014 8:24 AM
To: Bheemsen Aitha; oracle-l@xxxxxxxxxxxxx
Subject: Re: DB performance after upgrade from 9i to 11gR2
The way I read this is both QA and Prod are now at 11gR2, is this correct? I
don't want to take this any further until that bit of information is clearly
stated.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Thursday, May 8, 2014 12:41 AM, Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
wrote:
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
Other related posts:
