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-rowsIf 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?