Re: Performance problems after moving to new hardware

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 4 Mar 2015 19:51:47 +0100 (CET)
Message-ID: <1122404818.205240.1425495107644.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hi Sandra,
assuming that the PX execution is really identical to before migration (and with that i do not only mean the execution plan shape, i mean all important stuff like projections, predicates & outline section, parallel data skew, parallel execution slaves, etc.), it looks like the performance break down is caused by these (dramatic) bad I/O response times with the new storage sub system (and here once again assuming, that the AWR top 5 section is mostly related to your critical query).

At first i would crosscheck these I/O performance values with the wait event histogram as average values can be pretty misleading sometimes. If it looks bad there as well, i would verify that the service time (= storage wait) and host wait time are related closely.

If yes, then it looks like a storage issue, if not i would go with DTrace (luckily you are on Solaris) for further troubleshooting.

Is your database running on ZFS? You mentioned that the OS version is identical, but from time to time ZFS settings are thrown away by such migrations.

It is a lot of theoretical stuff and guessing, but i guess that's all we can do without any detailed information.  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Sandra Becker <sbecker6925_at_gmail.com> hat am 4. März 2015 um 18:43 geschrieben:
>
> Restored the schema stats to a point prior to the time we started gathering new stats. Rerunning the query, but I don't see any improvement.
>
> Ran the query on the standby and grabbed the execution plan. Looks the same as the execution plan on the primary.
>
> Still trying to get the number of physical disks so I can do the I/O calibration.
>
> AWR from old server/storage
>
> [Inline image 1]
>
> AWR from new server/storage
>
> [Inline image 1]
>
> Are there other sections or portions you need to see?
> Sandy

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

Received on Wed Mar 04 2015 - 19:51:47 CET