RE: Monitoring CBO degrading Switches in xplans?
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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 18 2009 - 17:33:12 CDT