Per your reply below:
see "Why Does My Plan Change?" in this article: http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html
Does it mean we only suffer the bad explain before the second statistics-gathering if we do not gather statistics manually?
Meanwhile, what's the relationship between it and adaptive cursor sharing in 11g?
Also i know sql profile which is something like gather statistics for sql statement. Is it ok to solve this kind of issue.
It would be highly appreciate to hear some detail explanation for them(adptive cursor, sql profile, and plan change in 10g due to statistic gathering)
Thanks very much.
July 03, 2012 - 8:40 am UTC
Does it mean we only suffer the bad explain before the second
statistics-gathering if we do not gather statistics manually?
It means more generally that if the optimizer does not have information that allows it to accurately estimate cardinalities (row counts for various plan steps), the odds that you get the right plan are much lower than if the optimizer does have that information.
We got a 'bad' plan before the second gathering because the estimated cardinalities were way off. When we gathered again, after running some predicates, the optimizer got the right estimates and hence - the right plan.Meanwhile, what's the relationship between it and adaptive cursor sharing in
nothing and everything. Think about it - the optimizers job is to basically estimate cardinalities (that is the MOST important thing it does). If it gets those wrong, we likely get a bad plan.
Now, with adaptive cursor sharing, the optimizer will optimize initially by peeking at the bind variable to a query like "select * from t where indexed_column = ?". it will make a decision to use or not use the index for that query based on its estimated cardinality.
IF the optimizer has statistics that could cause it to come up with different plans for that query depending on the bind (it has histograms for example), it will watch that query as it executes to try to see if it needs more than one plan for optimal performance.
IF the optimizer doesn't have those statistics, it will generate one plan for everyone and stick with it.
All of the things you meantion - sql profiles, statistics, extended statistics, dynamic sampling - are ways to give the optimizer MORE information so it can hopefully generate a good plan (bad plan => bad cardinality estimate).
Adaptive cursor sharing is just an outcome of the fact that you can have a different plan for the same query depending on what bind variables were used to optimize it. It can happen because you have statistics, extended statistics, dynamic sampling, sql profiles and the like (statistics all - all of these are statistics)