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

From: Bheemsen Aitha <baitha_at_itradenetwork.com>
Date: Thu, 8 May 2014 17:29:25 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423EEF35867C_at_plt-exch-01.Itradenetwork.com>

We were not using histograms before but in 11g now. Yes, I am still suspecting that could be the issue. No issues with storage and physical layout of the disks.

Thanks
BA

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark Sent: Thursday, May 08, 2014 7:39 AM
To: oracle-l_at_freelists.org
Subject: RE: DB performance after upgrade from 9i to 11gR2

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<mailto: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> [mailto: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<mailto: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> [mailto: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<mailto: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

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

Received on Thu May 08 2014 - 19:29:25 CEST