Re: Performance problems after moving to new hardware
- From: Sandra Becker <sbecker6925@xxxxxxxxx>
- To: Stefan Koehler <contact@xxxxxxxx>
- Date: Tue, 24 Mar 2015 15:57:54 -0600
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@xxxxxxxx> 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: @OracleSK
>
> > Sandra Becker <sbecker6925@xxxxxxxxx> 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
Other related posts:
