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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 8 May 2014 16:08:52 +0100
Message-ID: <CABe10sbJRu8dzWxkjPHoSHuAa313u76fKRH1YtOLi23E8DRC-Q_at_mail.gmail.com>



Like the others here I can make some blind guesses :) . I'd definitely second Mark's suggestion of grabbing comparable reports as between 9i and 11, but also as between prod and QA. You say that those two environments are identical but clearly there is at least one significant difference between them :). It might obviously be hard to put the output on somewhere like pastebin but without evidence we are just a bunch of storytellers sitting round the fire. Many of these stories make sense and may be relevant, but its difficult to tell.

I do like Mohamed's approach as well, but get the impression you are overwhelmed by the number of queries that are going wrong. AWR/STATSPACK will help you prioritize the really large problems.

On Thu, May 8, 2014 at 3:39 PM, Powell, Mark <mark.powell2_at_hp.com> wrote:

> Were you using histograms on the 9.2 system? From 10g on Oracle
> automatically collects histograms which it did not do on 9.2. If you did
> not use histograms on 9.2 but have histograms now removing the histograms
> and seeing if this helps would be one option.
>
>
>
> I would suggest taking a prime time STATSPACK or AWR report for a five or
> ten minute window and looking at what it shows before doing anything, but
> histograms would potentially be one of the major differences in statistics
> between 9.2 and 11.2
>
>
>
> If you migrated the data when you upgraded then what about the disk
> layout? That is, is the database spread over enough physical drives to
> handle the IO requirements? What do the IO queue lengths look like?
> Average read and write time? Etc ….
>
>
>
>
>
> *From:* Bheemsen Aitha [mailto:baitha_at_itradenetwork.com]
> *Sent:* Thursday, May 08, 2014 10:02 AM
> *To:* Powell, Mark; oracle-l_at_freelists.org
>
> *Subject:* RE: DB performance after upgrade from 9i to 11gR2
>
>
>
> It’s a 128 CPU monster server. This is the only database on that server.
> The total memory is 128 Gig. We are using 45 Gig for SGA and 20 Gig for
> PGA. We are not using AMM, as there was a bug that caused a background
> process called DISM which causes database to hang. I don’t see any wait
> event related to memory. I see mostly “db sequential read” in wait events.
>
>
>
> Thanks
>
> *BA*
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Powell, Mark
> *Sent:* Thursday, May 08, 2014 6:10 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* RE: DB performance after upgrade from 9i to 11gR2
>
>
>
> BA, what kind of memory management is in use. You said the plans look
> good but the queries just do not complete so this makes me wonder if the
> system is waiting on AMM memory management operations to complete.
>
>
>
> Have you checked the disk performance?
>
>
>
> What about other OS measures: cpu utilization and memory?
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Bheemsen Aitha
> *Sent:* Thursday, May 08, 2014 2:40 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* DB performance after upgrade from 9i to 11gR2
>
>
>
> 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*
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 08 2014 - 17:08:52 CEST

Original text of this message