Re: Monitoring software

   I'm not so sure about your statement that if it doesn't relate to bad user experience, then it doesn't matter. The problem is that no bad user experience now doesn't mean no bad user experience in a few months; having no file system fuller than 80% doesn't mean "I have no storage problem". You need to anticipate. Any apparently satisfactory situation can degrade very fast, for multiple reasons, and fighting my back to the wall isn't necessarily the situation I find most comfortable.

I no longer count the cases when I have seen periods of intense panic after an upgrade when people when email were flying with comments along the line of "our daily batch that used to run in 8 hours now takes 14 HOURS - WHAT ARE THE DBAs DOING!!!". Most of the time, when you dig into the code you discover that it's all full of loops and that correctly written everything could have been done in 2 hours, before and after the upgrade. Well, if you want an opinion, I find that 8 hours is pretty long daily batch. The problem is that many people have a rather high threshold of tolerance. But when you cross it, all hell breaks loose. That should be avoidable.
The optimizer, statistics and their kin have become so monstrous beasts that I feel it's often easier to take your chainsaw and refactor the code on sound basis than to spend days trying to find the good lever to pull to keep pre-upgrade performance (without adversely affecting something else).

What I have found so far most efficient to convince people of the origin of performance issues is to take a query or a process, rewrite it, and prove it can be done much faster. I once received to review a slow query that was the UNION of three select statements, and saw rather quickly that the result could be returned in a single pass over the data. When I said to the developer I thought it could be made three times faster, he was less than pleased (he wasn't a beginner). But I did it. We became friends ...

My 0.02 euros

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

Job Miller wrote:
> if worst sql doesn't correlate to "bad user experience" in some front
> end, or in some batch process that requires completion in a particular
> window, it doesn't much matter. does it?
>
> monitor SLA for user experience, and only dig for bad SQL when
> something someone cares about starts showing up on the violation list.
>
> That's probably a hard job for the DBA to sit back and not care about
> bad SQL, but consider it liberating.
>
> Also if you start leveraging the new auto-tune stuff, oracle will be
> prioritizing the highest impact sql of the day, week, month, and
> automatically generating and evaluating profiles for those statements
> anyway during maintenance task windows.
>
> that will free you up even more supposedly.
>
> Job
>
>
>
> --- On *Fri, 7/2/10, Michael Dinh /<mdinh_at_XIFIN.Com>/* wrote:
>
>
> From: Michael Dinh <mdinh_at_XIFIN.Com>
> Subject: RE: Monitoring software
> To: "'sbecker6925_at_gmail.com'" <sbecker6925_at_gmail.com>,
> "david.robillard_at_gmail.com" <david.robillard_at_gmail.com>
> Cc: "Martin Bach" <development_at_the-playground.de>,
> "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
> Date: Friday, July 2, 2010, 12:44 PM
>
> If it makes you feel any better, you are not in the boat alone.
>
>
>
> Everyone once in a while, we go through the ritual of identifying
> the top 10 worse SQL.
>
>
>
> Then we repeat the process of identifying the top 10 worse SQL.
>
>
>
> Notice I did not say anything about fixing, only identifying.
>
>
>
> Michael Dinh : XIFIN : 858.436.2929
>
>
>
> NOTICE OF CONFIDENTIALITY - This material is intended for the use
> of the individual or entity to which it is addressed, and may
> contain information that is privileged, confidential and exempt
> from disclosure under applicable laws. BE FURTHER ADVISED THAT
> THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY
> ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE
> AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
> CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE
> EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE
> RECEIPT OF THIS MESSAGE. If the reader of this email (and
> attachments) is not the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. Please notify the sender of
> the error and delete the e-mail you received. Thank you.
>
> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Sandra Becker
> *Sent:* Friday, July 02, 2010 9:18 AM
> *To:* david.robillard_at_gmail.com
> *Cc:* Martin Bach; oracle-l_at_freelists.org
> *Subject:* Re: Monitoring software
>
>
>
>
> So what do you people do when you've provided either the
> information and/or tool for developers to see the performance of
> their code and they refuse to look at it and insist it is up to
> the DBAs to tune it and make it efficient? I also provide
> performance information to my VP on a weekly basis and all other
> high level execs on a quarterly basis but no one except my VP
> seems to take it seriously. That's the boat I'm in right now and
> it seems to have a really big hole that is leaking more day by day.
>
>
> --
> Sandy
> Transzap, Inc.
>
>

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