It's just not possible!(Or at least not within reasonable effort).
One of these problems, or more precise questions is:
Unfortunately there is currently no such feature but at least I can give some examples, what I would like to have:
How likely is the current explain plan for a given SQL statement to change?I call this
estimated plan stability
Unfortunately there is currently no such feature but at least I can give some examples, what I would like to have:
- E-rows vs. A-rows
If they differ a lot (in any line of the execution plan) it might be a hint the plan is far away from reality, or in risk to change?
Of course for A-rowsgather_plan_statisticsor similar is needed. - Best so far in 10053 trace
If you ever have analysed a 10053 trace, you might know the line starting withBest so far ....
If the 2nd best is not far from the 1st, I assume small changes in the data might lead to a different execution plan. - Binds outside histogram boundaries
If a bind variable is outside of the min/max values of a histogram, the optimiser tries to guess how many rows it will get from this predicate/filter. Of course this can be horrible wrong, and should be also shown by my 1st suggestion.
These are only 3 possibilities. They should show some areas of information where I'd like Oracle to collect and provide more data than they do at the moment. Probably they would also be valuable for others? Any other suggestions out there?
3 Kommentare:
DBA_TAB_MODIFICATIONS - when is a table close to the point when its stats will be deemed stale and the table re-analyzed.
Another one - when is a bind variable peek close to a boundary in a range based histogram.
Both could throw off a lot of false positives though.
Thank you Gary,
you got the point.
Your fear of false positives is the reason, why I called it estimates.
And the false estimates the CBO does where one of the reasons for my wishes - so we are in good company!
I think it would become a hard question to answer accurately. Plans will generally speaking 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.
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.
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...
Kommentar veröffentlichen