Re: DB performance after upgrade from 9i to 11gR2
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: baitha@xxxxxxxxxxxxxxxxx
- Date: Thu, 8 May 2014 21:21:33 +0200
hi
the first thing you should do is get rid of histograms then check
in 11g it's simple to restore statistics so when things goes wrong you can
always go back quickly
or
use the pending statistics feature, gather stats and dont publish them then
fire a couple of sqlplus sessions with optimizer_use_pending_statistics set
to TRUE and test your queries, if they are good then publish, if not good
jsut delete them with dbms_stats
thanks
On Thu, May 8, 2014 at 7:29 PM, Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>wrote:
> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Powell, Mark
> *Sent:* Thursday, May 08, 2014 7:39 AM
>
> *To:* oracle-l@xxxxxxxxxxxxx
> *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@xxxxxxxxxxxxxxxxx<baitha@xxxxxxxxxxxxxxxxx>]
>
> *Sent:* Thursday, May 08, 2014 10:02 AM
> *To:* Powell, Mark; oracle-l@xxxxxxxxxxxxx
> *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@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Powell, Mark
> *Sent:* Thursday, May 08, 2014 6:10 AM
> *To:* oracle-l@xxxxxxxxxxxxx
> *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@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Bheemsen Aitha
> *Sent:* Thursday, May 08, 2014 2:40 AM
> *To:* oracle-l@xxxxxxxxxxxxx
> *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*
>
>
>
Other related posts:
