统计信息参数设置、是否关闭自动收集、回退统计信息、统计信息迁移

==================================================================================================

大约2个月前，一位业内人士问我为什么9i CBO迁移到10g上会出现许多执行计划改变导致的性能，他当然是为了能考考我；实际上我接触过的环境大多在8i/9i下没有使用CBO优化模式，从8i/9i的RBO模式跨越到10g上较为成熟的CBO优化模式，这当中出现执行计划讹误可以说是情理之中的；而9i CBO到10上的CBO问题也不少，我首先想到的是统计信息收集上存在区别，但具体是什么区别却又说不上。那位业内人士听了我的回答，笑，笑而不语。

Oracle十分博大，博大到可以称为Oracle的世界，很多东西长期不用就会遭人淡忘；我们来复习下9i和10g上统计信息收集的一些改动。

?

在9i中收集统计信息时其默认的MOTHOD_OPT模式为’FOR ALL COLUMNS SIZE 1′，使用这种模式时Oracle只收集所有列上最基础的统计信息，包括了最小/大值，distinct值等信息;但是不会收集列上的直方图。对那些数据均匀分布和没有出现在SQL语句中where子句中作为条件的列来说，这样的统计信息完全足够了。然而如果列上的数据分布并不均匀就可能导致CBO的执行计划成本计算不准确，这时我们需要手动对这些列上的直方图进行统计。

10g上对dbms_stats包中默认的METHOD_OPT模式做了修正，这显然是引起9i CBO迁移到10g CBO后易发地执行计划变化的一个重要因素，也是那位业内人士所要问的题眼。

?

新的默认METHOD_OPT值为”FOR ALL COLUMNS SIZE AUTO”，这意味着Oracle将通过内部算法自动决定那些列上需要收集统计信息，而那些列上不需要。是否收集直方图取决于列上数据的分布情况和与对应表相关的工作负载,这种工作负载可以解释为数据库中存在某些需要参考这些列的详细信息来计算执行成本的SQL语句。

这种方式听上去十分理想，似乎Oracle可以默默无闻地为我们抓取所有急需的统计信息。

然而问题是在许多环境中Oracle没有做出是否需要收集列上直方图的正确决定。实践证明Oracle有可能收集许许多多不必要的直方图，同时又放弃了许多需要收集的直方图。

?

在轻量级的应用环境中这种直方图收集不当的问题造成的影响大多数时间不为人们所察觉，相反在performance critical或已经形成性能瓶颈的环境中则可能是一场不大不小的麻烦。

?

此外Oracle还改变了列上密度(density)信息的计算方式。该值常被Oracle用来确定谓词选择性，当突然出现额外不必要的直方图时可能造成的广泛显著地性能影响(当然好的影响也可能出现，只是概率上……)。

显然这些莫名出现的不速之客也会给共享池造成影响，library cache与row cache相关的闩可能短期内车水马龙，如果您的应用数据表上有成百上千的列那么情况可能更糟(所以说开发要遵循范式，没有规矩的最后结果往往是应用不可用，项目失败。别告诉我你的应用苟且地活着，那同样意味着项目失败)！

?

======================================================================================================

?

利用dbms_stats来收集统计信息的时候，其procedure有很多参数是有默认值的。9i的默认值是直接写死在procedure的定义中，而10g的默认值则是一组可以由用户设置的参数值，为此，10g的dbms_stats引入了两个新的procedure：get_param和set_param。

?

9i：

procedure gather_table_stats
    (ownname varchar2,
     tabname varchar2,
     partname varchar2 default null,
     estimate_percent number default null,
     block_sample boolean default FALSE,
     method_opt varchar2 default 'FOR ALL COLUMNS SIZE 1',
     degree number default null,
     granularity varchar2 default 'DEFAULT',
     cascade boolean default FALSE,
     stattab varchar2 default null,
     statid varchar2 default null,
     statown varchar2 default null,
     no_invalidate boolean default FALSE);

10g：

procedure gather_table_stats
    (ownname varchar2,
     tabname varchar2,
     partname varchar2 default null,
     estimate_percent number default to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
     block_sample boolean default FALSE,
     method_opt varchar2 default get_param('METHOD_OPT'),
     degree number default to_degree_type(get_param('DEGREE')),
     granularity varchar2 default get_param('GRANULARITY'),
     cascade boolean default to_cascade_type(get_param('CASCADE')),
     stattab varchar2 default null, statid varchar2 default null,
     statown varchar2 default null,
     no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
     stattype varchar2 default 'DATA',
     force boolean default FALSE);

The DBMS_STATS.SET_PARAM can be used to set the following parameters:
CASCADE
Controls whether indexes are analyzed at the same time
Default: TRUE
Possible Values:
*
TRUE
*
FALSE
Note: The default value for CASCADE set by SET_PARAM is not used by export/import procedures. It is used only by gather procedures.
Example: exec DBMS_STATS.SET_PARAM(‘CASCADE’,'FALSE’);
?
DEGREE
Degree of parallelism.
Default: NULL
Possible Values:
*
NULL – Use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement.
*
integer – the integer will be used as degree for all objects
Example: exec DBMS_STA