tag:blogger.com,1999:blog-5309604992043123290.post6103468985891128347..comments2024-03-24T11:22:17.354+01:00Comments on berxblog: estimated plan stabilityMartin Bergerhttp://www.blogger.com/profile/16504572924713610305noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5309604992043123290.post-3164241770373295062011-07-23T17:15:47.989+02:002011-07-23T17:15:47.989+02:00I think it would become a hard question to answer ...I think it would become a hard question to answer accurately. Plans will <em>generally speaking</em> only be recalculated if the plan drops out of the cache, bind variables vary (under 11g) or the generation of child SQL cursors (I think...). The plan is pushed out of the cache if stats are re-gathered on any of the segments the SQL statement acts on and Gary covers looking at DBA_TAB_MODIFICATIONS for detecting if stats are likely to be regathered automatically. <br />The plan is likely to change if the estimated cost between two plans is close, so I suppose the CBO could take into account if it sees several plans with similar costs.<br />Of course, plans can change when "nothing" has changed. Time always changes and that can cause plans to change when for example "now" gets further and further from the known values for a date column in the where clause...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5309604992043123290.post-80810030808275052102011-07-19T07:04:35.119+02:002011-07-19T07:04:35.119+02:00Thank you Gary,
you got the point.
Your fear of ...Thank you Gary, <br />you got the point. <br />Your fear of <i>false positives</i> is the reason, why I called it <i>estimates</i>. <br />And the false <i>estimates</i> the CBO does where one of the reasons for my wishes - so we are in good company!Martin Bergerhttps://www.blogger.com/profile/16504572924713610305noreply@blogger.comtag:blogger.com,1999:blog-5309604992043123290.post-4444230135575985682011-07-19T01:29:14.414+02:002011-07-19T01:29:14.414+02:00DBA_TAB_MODIFICATIONS - when is a table close to t...DBA_TAB_MODIFICATIONS - when is a table close to the point when its stats will be deemed stale and the table re-analyzed.<br /><br />Another one - when is a bind variable peek close to a boundary in a range based histogram.<br /><br />Both could throw off a lot of false positives though.sydoraclehttps://www.blogger.com/profile/10404756950638119562noreply@blogger.com