RE: Monitoring CBO degrading Switches in xplans?

From: Milen Kulev <>
Date: Fri, 19 Jun 2009 00:33:12 +0200
Message-ID: <002c01c9f064$c40be700$4c23b500$_at_net>

What about using SQL Profiles and generating them automatically (the hints for the good/bad execution plans are in dba_hist_sqlplan anyway). You will end up with many SQL Profiles, but at least the execution plans will be stable.
Bear in mind that the efficiency (elapsed time per execution) may greatly vary depending on the values of the bind variables

HTH. Milen

-----Original Message-----

From: [] On Behalf Of Fmhabash
Sent: Donnerstag, 18. Juni 2009 23:52
To: Oracle-L Group
Subject: Monitoring CBO degrading Switches in xplans?

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.

-- Received on Thu Jun 18 2009 - 17:33:12 CDT

Original text of this message