Montag, 18. Juli 2011

estimated plan stability

Sometimes I am searching for any method to solve a problem. And after some investigations, mailing lists, direct contact of much smarter people, I come to the conclusion:
It's just not possible!
(Or at least not within reasonable effort).

One of these problems, or more precise questions is:
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-rows gather_plan_statistics or similar is needed.

  • Best so far in 10053 trace

    If you ever have analysed a 10053 trace, you might know the line starting with Best 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?


Gary Myers hat gesagt…

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.

Martin Berger hat gesagt…

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!

Anonym hat gesagt…

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...