Understanding DBMS_STATS.SET_*_PREFS procedures

原文：http://optimizermagic.blogspot.com/2009/08/understanding-dbmsstatssetprefs.html

In previous Database releases you had to use the  DBMS_STATS.SET_PARM  procedure to change the default value for the parameters used by the  DBMS_STATS.GATHER_*_STATS  procedures. The scope of any changes that were made was all subsequent operations. In Oracle Database 11g, the DBMS_STATS.SET_PARM  procedure has been deprecated and it has been replaced with a set of procedures that allow you to set a preference for each parameter at a table, schema, database, and Global level. These new procedures are called  DBMS_STATS.SET_*_PREFS  and offer a much finer granularity of control.

在以往的数据库版本中我们必须使用DBMS_STATS.SET_PARM来修改DBMS_STATS.GATHER_*_STATS存储过程中存数的默认值，其修改会影响到后续的所有操作。在11G中，DBMS_STATS.SET_PARM过程不再被建议使用而是被DBMS_STATS.SET_*_PREFS过程替代，该过程可以在表级别，模式级别，数据库级别和全局级别来设置统计信息收集的参数偏好设置，新的过程为我们提供了更好的控制粒度。

However there has been some confusion around which procedure you should use when and what the hierarchy is among these procedures. In this post we hope to clear up the confusion. Lets start by looking at the list of parameters you can change using the  DBMS_STAT.SET_*_PREFS procedures.

对于使用哪一个过程以及各个过程的组织结构是怎样的，人们往往在认识上比较模糊。本文的目的就是帮助人们解决困惑。首先让我们看一下通过DBMS_STATS.SET_*_PREFS可以修改那些参数：

• AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)
• CASCADE
• DEGREE
• ESTIMATE_PERCENT
• METHOD_OPT
• NO_INVALIDATE
• GRANULARITY
• PUBLISH
• INCREMENTAL
• STALE_PERCENT

As mentioned above there are four  DBMS_STATS.SET_*_PREFS  procedures.

上述参数都可以通过下面的过程了修改

1. SET_TABLE_PREFS
   
2. SET_SCHEMA_PREFS
   
3. SET_DATABASE_PREFS
   
4. SET_GLOBAL_PREFS
   

The  DBMS_STATS.SET_TABLE_PREFS  procedure allows you to change the default values of the parameters used by the  DBMS_STATS.GATHER_*_STATS  procedures for the specified table only.

DBMS_STATS.SET_TABLE_PREFS过程运行我们修改对特定表收集统计信息时的参数默认值

The  DBMS_STATS.SET_SCHEMA_PREFS  procedure allows you to change the default values of the parameters used by the  DBMS_STATS.GATHER_*_STATS  procedures for all of the existing objects in the specified schema. This procedure actually calls  DBMS_STATS.SET_TABLE_PREFS  for each of the tables in the specified schema. Since it uses  DBMS_STATS.SET_TABLE_PREFS  calling this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF  values for all parameters.

DBMS_STATS.SET_SCHEM_PREFS过程允许我们修正在收集某模式下所有对象的统计信息时参数的默认值。该过程最终调用的是DBMS_STATS.SET_TABLE_PREFS来修改某模式下的每张表，因此对于后续新建立的对象，该过程是不起作用的。后续新建立对象回收GLOBAL_PREFS的影响。

The  DBMS_STATS.SET_DATABASE_PREFS  procedure allows you to change the default values of the parameters used by the  DBMS_STATS.GATHER_*_STATS  procedures for all of the user defined schemas in the database. This procedure actually calls  DBMS_STATS.SET_TABLE_PREFS  for each of the tables in each of the user defined schemas. Since it uses  DBMS_STATS.SET_TABLE_PREFS this procedure will not affect any new objects created after it has been run. New objects will pick up the  GLOBAL_PREF  values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the  ADD_SYS  parameter to  TRUE .

DBMS_STATS.SET_DATABASE_PREFS过程运行我们对整个数据库内的已经存在模式进行修改其统计信息时的默认参数值，该过程最终也是调用DBMS_STATS.SET_TABLE_PREFS来设置每个表的偏好设置，因此对于后续新建立的对象该过程也是无法影响的。对于新建立的对象，可以使用GLOBAL_PREF来设置。我们可以通过设置ADD_SYS参数来设置DBMS_STATS.SET_DATABASE_PREFS是否影响ORACLE拥有的模式，如sys,system等

The  DBMS_STATS.SET_GLOBAL_PREFS  procedure allows you to change the default values of the parameters used by the  DBMS_STATS.GATHER_*_STATS  procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS  command. Changes made by this procedure  will  affect any new objects created after it has been run as new objects will pick up the  GLOBAL_PREF  values for all parameters.

DBMS_STATS.SET_GLOBAL_PREFS允许我们修改在数据库中收集任何对象的统计信息时使用的默认参数值，前提是该对象不存在其他更细级别的偏好设置。如果没有在DBMS_STATS.GATHER_*_STATS命令或者其他细粒度级别设置偏好这是，该过程设置的默认值将发挥作用。该过程会影响后续建立的任何新对象。

With  GLOBAL_PREFS  it is also possible to set a default value for one additional parameter, called AUTOSTAT_TARGET . This additional parameter controls what objects the automatic statistic gathering job (that runs in the nightly maintenance window) will look after. The possible values for this parameter are  ALL , ORACLE , and  AUTO .  ALL  means the automatic statistics gathering job will gather statistics on all objects in the database.  ORACLE  means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc) Finally  AUTO (the default) means Oracle will decide what objects to gather statistics on. Currently  AUTO  and  ALL behave the same.

在全局设置中，我们可以设置AUTOSTAT_TARGET参数，该参数控制自动统计信息收集任务将收集的数据库对象。该参数的取值为ALL  ORACLE 和AUTO. ALL 表示自动任务将收集所有的数据库对象，ORACLE 表示仅仅收集ORACLE 拥有模式的统计信息。auto（默认值）表示将有oracle在决定收集那些对象的统计信息。目前为止，AUTO 和 ALL 具有相同的效果

In summary,  DBMS_STATS  obeys the following hierarchy for parameter values, parameters values set in the  DBMS_STAT.GATHER*_STATS  command over rules everything. If the parameter has not been set in the command we check for a table level preference. If there is no table preference set we use the global preference.

总而言之，DBMS_STATS根据如下层次来设置参数值：DBMS_STATS.GATHER_*_STATS具有最高的优先级，如果没有在该命令中设置参数，oracle将会检查表级别的偏好设置，如果没有表级别的偏好设置，oracle将采样全局设置。