oracle 收集对象统计信息 配置 dbms_stats包

1.1          配置 dbms_stats包

dbms_stats包提供了两组子程序，用来配置在之前章节中描述的某些参数的默认值。第一组仅应在10.2版本中使用。实际上，第一组子程序在11.1版本中已废弃。因此，从11.1版本开始，应该使用由第二组子程序提供的子程序。

1.1.1      传统方式

在10.2版本中，你可以更改cascade、estimate_percent、degree、method_opt、no_invalidate和granularity参数的全局默认值。这些默认值能进行修改是因为它们不是硬编码写在存储过程中的，而是在运行时从数据字典中抽取出来的。

dbms_stats包的set_param存储过程可以用来设置默认值。要执行这个过程，需要analyze any dictionary 和 analyze any系统权限。dbms_stats包的get_param函数可以用来获取默认值。下面的例子展示了如何使用它们。注意，pname是参数的名称，而pval是参数的值：

execute dbms_output.put_line(dbms_stats.get_param(pname=>'CASCADE'));

execute dbms_stats.set_param(pname=>'CASCADE',pval=>'TRUE');

execute dbms_output.put_line(dbms_stats.get_param(pname=>'CASCADE'));

另一个可以使用这种方法设置的参数是autostats_target。这个参数的唯一用途是，gather_stats_job任务可以使用该参数决定应该处理哪些对象的统计信息收集。表8-7列出了可选的值。其默认值是auto。

表8-7  autostats_target参数接受的值

要想无需多次执行get_param函数就获取所有参数的默认值，可以使用以下查询：

select sname AS parameter, nvl(spare4, sval1) AS default value

  FROM sys.optstat_hist_controls

 WHERE sname IN ('CASCADE',

                 'ESTIMATE_PERCENT',

                 'DEGREE',

                 'METHOD_OPT',

                 'NO_INVALIDATE',

                 'GRANULARITY',

                 'AUTOSTATS_TARGET');

①遗憾的是，Oracle并不会通过一张数据字典视图显示这个信息，也就是说这个查询是基于内部表的。访问这张表需要select any dictionary系统权限。

要还原原来设置的默认值，可以使用dbms_stats包提供的reset_param_defaults存储过程。

1.1.2      现代方式

自11.1版本开始，为参数设置默认值的概念，被称作首选项，与10.2版本相比，该功能有了极大的增强。实际上，你不仅可以设置全局默认值，也可以在表级别设置默认值。这些增强的一个结果就是上一节中描述的get_param函数、set_param过程和reset_param_defaults 过程都被淘汰了。

可以为参数autostats_target、cascade、concurrent、estimate_percent、degree、method_opt、no_invalidate、granularity、publish、incremental、stale_percent、table_cached_blocks(从11.2.0.4版本开始)以及从12.1版本开始出现的参数global_temp_table_stats、incremental_staleness和incremental_level等设置默认值。要修改它们，可以使用dbms_stats包提供的以下存储过程。

Ø  set_global_prefs 设置全局首选项。它取代了set_param存储过程。

Ø  set_database_prefs 设置数据库级首选项。全局首选项和数据库级别首选项的区别是后者不用作数据字典对象。换句话说，数据库级别首选项只作用于用户定义的对象。

Ø  set_schema_prefs 为某个特定的模式设置首选项。

Ø  set_table_prefs 为某个特定表设置首选项。

注意 参数autostats_target和concurrent只能通过set_global_prefs存储过程来修改。

警告 存储过程set_database_prefs和set_schema_prefs不会直接将首选项信息存储到数据字典中，而是会将它们转变成为调用存储过程时即刻在数据库中或模式中可用的所有对象的表级别首选项。

换句话说，真正存在的只有全局首选项或者表级别首选项。存储过程set_database_prefs和set_schema_prefs只是对存储过程set_table_prefs的简单包装。这意味着对于调用完这两个存储过程后创建的新表，将会使用全局首选项。

下面的PL/SQL代码块展示了如何为cascade参数设置不同的值。注意，pname指参数名称，pvalue指参数值，ownname指所有者，而tabname指表名。再强调一次，要非常小心，因为在这样的PL/SQL代码块中调用的顺序十分关键。实际上，每一次调用都会覆盖上一次调用完成的一些定义：

BEGIN

  dbms_stats.set_database_prefs(pname  => 'CASCADE',

                                pvalue => 'DBMS_STATS.AUTO_CASCADE');

  dbms_stats.set_schema_prefs(ownname => 'SCOTT',

                              pname   => 'CASCADE',

                              pvalue  => 'FALSE');

  dbms_stats.set_table_prefs(ownname => 'SCOTT',

                             tabname => 'EMP',

                             pname   => 'CASCADE',

                             pvalue  => 'TRUE');

END;

/

为获取当前的设置，可以使用get_prefs函数来取代get_param函数。下面的查询用来展示在上面的PL/SQL块中所执行设置的效果。注意，pname是参数名称，ownname是所有者名称，而tabname是表名。正如你所看到的，依赖于所指定的参数，该函数会返回指定级别的值。这次对于首选项的搜索按照图8-9所示的方式进行：

select dbms_stats.get_prefs(pname => 'cascade') AS global,

       dbms_stats.get_prefs(pname   => 'cascade',

                            ownname => 'SCOTT',

                            tabname => 'DEPT') AS dept,

       dbms_stats.get_prefs(pname   => 'cascade',

                            ownname => 'SCOTT',

                            tabname => 'EMP') AS emp

  FROM dual;

如果希望不用多次执行get_param函数就可以获取多个全局首选项，正如上一节中描述的那样，可以查询内部数据字典表optstat_hist_control$。要获取表的首选项，也可以执行接下来的查询。注意，即便之前的PL/SQL代码块配置是在模式级别，dba_tab_stat_prefs视图仍显示了其设置结果：

select table_name, preference_name, preference_value

  FROM dba_tab_stat_prefs

 WHERE owner = 'SCOTT'

   AND table_name IN ('EMP', 'DEPT')

 ORDER BY table_name, preference_name;

要删除首选项， dbms_stats包提供了以下存储过程。

Ø  reset_global_pref_defaults 将全局首选项重置为默认值。

Ø  delete_database_prefs 在数据库级别删除首选项配置。

Ø  delete_schema_prefs  在模式级别删除首选项配置。

Ø  delete_table_prefs   在表级别删除首选项配置。

下面的调用展示了如何删除当前scott用户下包含的所有表中与cascade参数相关的首选项：

dbms_stats.delete_schema_prefs(ownname =>'SCOTT',pname =>'CASCADE')

要在全局级别和数据库级别执行这些存储过程，需要有analyze any dictionary 和 analyze any系统权限。要在模式级别或表级别执行这些存储过程，需要以所有者身份连接到数据库或者拥有analyze any系统权限。