Thanks for the question, Rama.

Asked: June 19, 2012 - 1:39 pm UTC

Last updated: September 27, 2012 - 8:22 am UTC

Version: 11.2.0.2

Viewed 50K+ times! This question is

You Asked

Hi Tom,
We always put the "COMPUTE STATISTICS" clause in our CREATE INDEX statement so that the index gets used soon after it is created until we came across an excerpt from Oracle Docs that "Compute Statistics" uses the old Analyze command to compute statistics to gather stats and starting 11GR2 Oracle automatically computes the stats soon after creating Index.

We then started to not include the clause in our CREATE INDEX statements. Wehn we looked at the DBA_INDEXES view it was showing up that the stats were indeed gathered (NUM_ROWS/ TOTAL_BLOCKS/ DISTINCT_KEYS etc). The issue we then started to encounter is the Index is not being used by the optimizer is some scenarios (like queries involving complex joins and views on top of the table)unless we manually gather the stats using the DBMS_STATS API. Can you please shed more light on whether this is an expected behavior or if we are doing something wrong?

Thanks

and Tom said...

you do not need to do that anymore, since 10g - we always compute statistics on all index creates/rebuilds when the index is not empty. consider:

ops$tkyte%ORA11GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(user_id);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select clustering_factor, num_rows from user_indexes where index_name = 'T_IDX';

CLUSTERING_FACTOR   NUM_ROWS
----------------- ----------
                1         49

so, you can get rid of the compute if you like, it is already happening

I'd need more information - for example - did the tables change between the time you created the indexes and the time you ran dbms_stats?

what parameters did you use with dbms_stats? If you just created the tables, loaded them, gathered stats - and then ran queries, you might find that running stats again gathers an entirely different set of stats.

see "Why Does My Plan Change?" in this article:
http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html

If you give us more information along those lines, we might be able to make a comment. It would be especially useful to have a plan from BEFORE the dbms_stats and the plan from AFTER - for the same query. We'll be looking in particular at the changed cardinality estimates (rows)

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Hi Tom,

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
11g?

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

Hi Tom,

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?

Thanks

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

Hi Tom,

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.

Regards,
Sasi

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 11.2.0.1 but does when I upgrade to 11.2.0.4

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer