Monitoring CBO degrading Switches in xplans?

From: Fmhabash <fmhabash_at_gmail.com>
Date: Thu, 18 Jun 2009 17:51:35 -0400
Message-ID: <4a3ab6d9.c5c2f10a.2e61.0936_at_mx.google.com>



This is an issue that has made sleepless over and over again. Critical application sql changes its xplans causing elapse time to go unacceptably worst. We eventually wanted to monitor this kind of event proactively. To this effect, we query top n sql from awr order by elapsed time. We then compare most recent ET value to the previous n samples. We then calculate cost of current plan and compare it to existing awr ones cost. With all this info, we alert if ET and plan cost exceed thresholds.

How do you cope with this issue and what have you done to monitor it (logic wise).  



Thank You.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Jun 18 2009 - 16:51:35 CDT

Original text of this message