Rama, June 28, 2012 - 6:18 am UTC
As always Thank you Tom.
plan change + skew data + adaptive cusor + sql profile
A reader, July 03, 2012 - 3:01 am UTC
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)
"Create Index Compute Statistics",
Sasikumar, September 25, 2012 - 8:06 am UTC
We didn't specify Compute Statistics while creating index in Oracle 10g. However, while creating the index(COMPUTE STATISTICS is appended automatically to DDL) and collect stats on that particular index. This overrides over weekly stats collection using dbms_stats, which in-turn causing the problem to our nightly jobs.(We disabled automatic Stats gathering-GATHER_STATS_JOB). Is anyway we can tell Oracle not to run Compute Statistics while creating the indexes?
September 27, 2012 - 8:22 am UTC
no, there is not. it is a feature, we compute them on a rebuild/create.
why is having compute statistics causing a problem????
Create Index Compute Statistics
A reader, October 25, 2012 - 3:49 am UTC
Thanks for the quick reply. We able to overcome the problem, by locking the Stats collection for the table, while creating the indexes. Once index is created, we used Dbms_stats to collect the statistics. I am not sure why Compute statistics is causing problem, whereas dbms_stats doesn't create any issues.
I have issue with creating index
George, April 30, 2015 - 3:42 am UTC
Hi Tom, I too have an issue with compute statistics. I don't actually run the command, I create an index on a column and I get an incident in my logs immediately after and the index is not created. The incident log states that analyze table compute statistics has an issue. Oracle support keeps asking why I am running compute statistics when I should be running dbms_stats package but I am not sending the analyze command.
The strange part is that this does not happen in 126.96.36.199 but does when I upgrade to 188.8.131.52