Statistic Collection

Simon,

Oracle control the proess of Statisitic automaitcly, and the data would be refrieshed periodically and triggered with DLM and data change ratio, etc, because the data impact with SQL excution plan much.

Thanks,

Eddy

Can I specify Method_Opt with specific histograms if I don't want automatic behaviour? 

from oracle 10g, stats is auto-collected by scheduled job. however, you can lock particular stats related to tables. by default, this function is disabled. you can use dbms_stats package to enable this feature. after that, when you collect stats over these table, stats are not affected.

I will give the answer possibly YES to your question.

Method_Opt enables gathering of histograms.
Someway if you explicitly specify Method_Opt ("for .. columns ... size ..."), Oracle uses the specification.

If you want specific histograms, always be careful to ensure that you include the specification.
If you are comfortable with the automatic histograms, you can rely on the auto behaviour.

Simon,

If I could assume you are using 11G, you might look @ 11g extended optimizer statistics, which provides more option on statistic collection, effective job running and better execution speed.

Here is a article for your reference. http://www.dba-oracle.com/art_orafaq_cbo_stats.htm

Thanks,

Eddy

Thanks Eddy for your information!

The method_opt parameter for dbms_stats is very useful for refreshing statistics when the table and index data change. The method_opt parameter is also very useful for determining which columns require histograms.

In some cases, the distribution of values within an index will effect the CBOs decision to use an index versus perform a full-table scan. This happens when a where clause has a disproportional amount of values, making a full-table scan cheaper than index access.

Oracle histograms statistics can be created when you have a highly skewed index, where some values have a disproportional number of rows. In the real world, this is quite rare, and one of the most common mistakes with the CBO is the unnecessary introduction of histograms in the CBO statistics. As a general rule, histograms are used when a column's values warrant a change to the execution plan.