Re: Oracle Monitoring Tool

From: David Roberts <big.dave.roberts_at_googlemail.com>
Date: Sat, 11 Jun 2011 12:43:32 +0100
Message-ID: <BANLkTi=3FCFAw3-P65FUWpGAB6YWEfNCUA_at_mail.gmail.com>

Obviously, there is internally, in 11g, adaptive cursor sharing.

This (AFAIK) only tackles performance issues with SQL containing bind variables and doesn't raise an alert, although I suppose it would be trivial to monitor and alert on the basis of plans that had been stable and had suddenly started to generate multiple plans, assuming that the query contained bind variables.

Beyond that, I can see plans changing (predominantly) due to:

1. Upgrades
2. Statistics changing (From Inaccurate to accurate)
3. Real statistics changing (Table changing in size)

1. Perhaps should be tackled with RAT.
2. This shouldn't be happening without the DBA already being aware of a problem.
3. Possibly this would be useful, but it's still reactive, you would have already had to observe a degradation in performance before this alert could be raised.

So in the third scenario, there would be some short term value in a system warning you that a component of a batch job had changed plan before the Batch job had completed, but I would suggest that this would be a hard sell for a third party tool vendor, so if such a tool exists, I suspect that it would be open source.

D.

On Fri, Jun 10, 2011 at 9:17 PM, Michael Dinh <mdinh_at_xifin.com> wrote:

> What monitoring tools are you using to detect performance degradation and
> to alert in real time for an SQL execution?
>
>
>
> For example, if a SQL normally takes 5 minutes to run and now runs in 60
> minutes, there should be a alert at some time thereafter.
>
>
>
> Does such a tool exists?
>
>
>
> Thanks for sharing.
>
>
>
> Michael Dinh
>
>
>
> 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.
>

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

Received on Sat Jun 11 2011 - 06:43:32 CDT