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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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. -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l