Re: Performance problems after moving to new hardware
Date: Tue, 24 Mar 2015 15:57:54 -0600
Message-ID: <CAJzM94D19K7jTDAhwhMjPVoPZmjpD_L+OMbSJ=-pF8Azaw-D_Q_at_mail.gmail.com>
Finally back to complete this saga. The SEs have some tools they used, along with some things oracle support suggested. I already had timings and execution plans of the most problematic queries from running them on the new hardware. Ran the exact same queries on the old hardware. Funny thing, the execution plans were identical. Because the PSTART and PSTOP columns showed :BF0000 (bloom filter), the developer assumed it wasn't doing partition pruning. Ran the queries 4 times, same runtimes, give or take a few minutes, but I was the only user in the database when testing on the old hardware. Had the application and ad hoc users in when we tested on the new hardware. The graphs and stats the SEs had showed that the CPU and I/O were about the same, better on the new hardware which has 4 other databases running. Old hardware it was the only database. It was abundantly clear to the SEs and DBAs involved in the testing that there was no way the query to return 11.6M rows was finishing in 5 minutes despite the assertions of the developers.
Had a ticket open about the partition pruning to cover all my bases. Support came back with about 3 dozen recommendations for tuning the queries. As I read through them, I see that most of them are the same recommendations I made before being told to fix the database and the hardware. Upshot - we are moving our redo logs off the data mount points to new disk; the developers have been informed of our findings and told we are moving back to the new hardware this week; we will pass on the oracle recommendations and work with them to get their horrible, horrible queries tuned.
Thanks everyone for your help and suggestions.
Sandy
GHX
On Tue, Mar 17, 2015 at 12:45 PM, Stefan Koehler <contact_at_soocs.de> wrote:
> Hi Sandra,
> i remember your issue and my latest reply. This is a bad (Oracle) support
> situation and even worst that you had to revert to the old hardware.
>
> However did you verify the mentioned service time (= storage wait) and
> host wait time? And if yes - did you DTrace the issue as suggested? What
> were
> the results? I think there is some way to find the issue with DTrace (even
> without Oracle support) based on your description and AWR data.
>
> 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 17. März 2015 um 17:34
> geschrieben:
> >
> > After asking for the same reports 3 times, oracle support came back and
> asked if we were sure the SQL in the AWR and ADDM were from the
> > application. Took a lot of doing, and a call to the escalation manager,
> but we finally were able to get a Solaris engineer involved to help us
> > troubleshoot the hardware. Very annoyed that they agreed to have someone
> on a conference call yesterday and that person decided not to call in
> > because he didn't think it was a hardware issue. No explanations, just
> he didn't think it was hardware. We switched the primary back to the old
> > hardware since the database was basically unusable for the application
> users.
> >
> > Sandy
>
-- Sandy GHX -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 24 2015 - 22:57:54 CET