如何修改oracle自动统计信息收集所使用的参数

这里主要介绍如何来修改Oracle 10g与11g统计信息收集所使用的缺省参数以及Oracle对这些参数提供的一个参考值。当使用一个维护窗口来自动收集统计信息时这些参数是非常有用的，这些缺省参数定义了如何来收集统计信息。在Oracle10g中使用dbms_stats.set_param与dbms_stats.get_param来完成缺省参数的修改，但在Oracle 11g中dbms_stats.set_param与dbms_stats.get_param已经被丢弃，进而使用dbms_stats.set_global_prefs与dbms_stats.get_prefs来进行缺省参数的修改。

在Oracle 10g中收集统计信息的缺省参数可以执行dbms_stats.set_param过程来进行修改。如果想要修改多个参数，那么对于每个参数都需要执行一次dbms_stats.set_param过程，语法如下:
DBMS_STATS.SET_PARAM ( pname IN VARCHAR2, pval IN VARCHAR2);
pname是参数名，pval是参数值。

可以使用dbms_stats.set_param进行修改的缺省参数
cascade:控制在相同时间索引是否被分析，缺省值为:true，可选值:true|false，由dbms_stats.set_param所设置的cascade的缺省值不能用于统计信息的导出或导入过程，只能用于统计信息的收集过程

SQL> select dbms_stats.get_param('cascade') from dual;  DBMS_STATS.GET_PARAM('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE   SQL> exec dbms_stats.set_param('cascade','true');     PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_param('cascade') from dual;  DBMS_STATS.GET_PARAM('CASCADE') -------------------------------------------------------------------------------- TRUE  

degree:并行度，缺省值是NULL，如果设置为auto_degree，那么将会自动判断并行度。该参数可选的参数值为null/integer(所有对象所使用的并行度为integer

SQL> select dbms_stats.get_param('degree') from dual;  DBMS_STATS.GET_PARAM('DEGREE') -------------------------------------------------------------------------------- NULL  SQL> exec dbms_stats.set_param('degree','4');  PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_param('degree') from dual;  DBMS_STATS.GET_PARAM('DEGREE') -------------------------------------------------------------------------------- 4  

estimate_percent:行数采样的百分比，缺省值为dbms_stats.auto_sample_size,可选的参数值:一个有效范围[0.000001,100]/null使用100%来进行计算/dbms_stats.auto_sample_size。使用dbms_stats.auto_sample_size时根据数据库的版本，采样百分比会有所不同，10g中的采样百分比要比11g所使用的小。

SQL> select dbms_stats.get_param('estimate_percent') from dual;  DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE  SQL> exec dbms_stats.set_param('estimate_percent','null');  PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_param('estimate_percent') from dual;  DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- NULL 

method_opt:用于收集列统计信息，缺省值为:for all columns size auto,可选参数值:
for all [indexed| hidden] columns [size_clause]
for columns [size_clause] column|attribute [size_clause][,column|attribute

[size_clause]...]

size_clause:=SIZE{integer |repeat |auto |skewonly}
integer:指定histogram的桶数，范围[1,254]
repeat:只对已经存在直方图的列收集直方图
auto:Oracle根据列中数据的分布与列的工作负载来决定是否收集直方图
skewonly:Oracle将根据列中数据的分布来决定是否收集直方图

SQL> select dbms_stats.get_param('method_opt') from dual;          DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO  SQL> exec dbms_stats.set_param('method_opt','for all columns size 1');  PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_param('method_opt') from dual;  DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE 1 

no_invalidate:判断是否将依赖的游标设置为无效，缺省参数值是dbms_stats.auto_invalidate,可选参数:
dbms_stats.auto_invalidate Oracle决定是否将依赖于统计信息的游标设置为无效
true 依赖于统计信息的游标将不会失效
false 依赖于统计信息的游标将会失效

SQL> select dbms_stats.get_param('no_invalidate') from dual;  DBMS_STATS.GET_PARAM('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE  SQL> exec dbms_stats.set_param('no_invalidate','false');  PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_param('no_invalidate') from dual;  DBMS_STATS.GET_PARAM('NO_INVALIDATE') -------------------------------------------------------------------------------- FALSE 

granularity: 统计信息收集的粒度，缺省参数'auto'，可选参数:
'auto' 基于分区类型来决定粒度
'all' 收集所有统计信息(子分区，分区与全局）
'global' 收集全局统计信息
'global and partition' 收集全局与分区级别的统计信息。即使是一个复合分区对象，那么子分区
级统计信息不会被收集。
'partition' 收集分区级别统计信息
'subpartition' 收集子分区级别统计信息

'default'已经过时了。这个选项只能收集全局与分区级别的统计信息。

SQL> exec dbms_stats.set_param('granularity','all');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('granularity') from dual;

DBMS_STATS.GET_PARAM('GRANULARITY')
--------------------------------------------------------------------------------
ALL

autostats_target:这个参数只能用于自动统计信息收集，这个参数控制着那些对象会被收集统计信息
缺省值是'auto'，可选参数值:
'auto' oracle将会决定那些对象将被收集统计信息
'all' 对系统中的所有对象收集统计信息
'oracle' 对所有oracle所拥有的对象收集统计信息，这个选项将会限制自动统计信息收集作业所选择的方案列表，将对Oracle组件系统方案列表收集统计信息，例如SYS,SYMAN,WMSYS与EXFSYS方案

SQL> select dbms_stats.get_param('autostats_target') from dual;                          DBMS_STATS.GET_PARAM('AUTOSTATS_TARGET') -------------------------------------------------------------------------------- AUTO  SQL> exec dbms_stats.set_param('autostats_target','all');  PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_param('autostats_target') from dual;  DBMS_STATS.GET_PARAM('AUTOSTATS_TARGET') -------------------------------------------------------------------------------- ALL  

dbms_stats.set_param的使用
为了执行这个过程，用户必须有sysdba或analyze any dictionary与analyze any系统权限。

varchar2
类型的参数与值都需要用引号括起来，就算他们代表数字也是一样。注意NULL与'NULL'是不同的:当设置为NULL，没有引号，设置为Oracle的建议值，当设置为'NULL'时，参数等于NULL

下面介绍如何来修改Oracle 11g统计信息收集操作的缺省参数并对这些参数提供一个参考值，及如何对其进行修改。这些缺省参数将会用来于在维护窗口中自动统计信息收集来收集统计信息。

在Oracle 11g中收集统计信息主要有以下四个过程来修改缺省参数:
set_global_prefs
set_schema_prefs
set_database_prefs
set_table_prefs

根据你需要修改的级别来选择特定的过程来修改这些缺省参数。例如，如果想要对单个表修改缺省参数据，那么应该使用set_table_prefs过程，set_database_prefs用来维护数据库级别的设置。对于每一个要修改的参数都需要执行一次修改过程。

set_global_prefs
能够改变dbms_stats.gather_*_stats过程的缺省参数，在没有表级特定设置的情况下来对数据库中任何对象收集统计信息。对于全局设置所有参数都使用缺省值，除非表级特定设置或者在dbms_stats.gather_*_stats命令中显式设置参数。通过这个过程进行的改变将会影响改变之后所创建的任何对象。新对象将对所有参数使用global_pref值。
语法:
dbms_stats.set_global_prefs(pname in varchar2,pvalue in varchar2);
下面的语句将对所有对象在全局级设置no_invalidate为false:

SQL> select dbms_stats.get_prefs('no_invalidate') from dual;         DBMS_STATS.GET_PREFS('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE  SQL> exec dbms_stats.set_global_prefs(pname=>'no_invalidate',pvalue=>'false');  PL/SQL procedure successfully completed.  SQL> select dbms_stats.get_prefs('no_invalidate') from dual;  DBMS_STATS.GET_PREFS('NO_INVALIDATE') -------------------------------------------------------------------------------- FALSE  

set_table_prefs
可以用来修改dbms_stats.gather_*_stats过程收集统计信息的缺省参数，但仅限于表级别。
语法:
dbms_stats.set_table_prefs(ownname in varchar2,tabname varchar2,pname in varchar2,pvalue

in varchar2);

例如，下面的语句将对scott用户的emp表进行设置，因此索引统计信息不会在收集表统计信息时而被收集：

SQL> select table_name,num_rows,blocks,last_analyzed from dba_tables where   owner='SCOTT';  TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZE ------------------------------ ---------- ---------- ------------ DEPT                                    4          5 02-DEC-15 EMP                                 30014         80 03-MAY-16 SALGRADE                                5          5 02-DEC-15 BONUS                                   0          0 02-DEC-15 T2                                      0          0 03-MAY-16  SQL> select   table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from   dba_indexes where owner='SCOTT' and table_name='EMP';  TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS   DISTINCT_KEYS   NUM_ROWS LAST_ANALYZE ------------------------------ ------------------------------ ---------- -----------   ------------- ---------- ------------ EMP                            PK_EMP                                  1          57           30014      30014 03-MAY-16 EMP                            EMP_EMPNO_DEPTNO                        1          72           30014      30014 03-MAY-16   SQL> exec dbms_stats.gather_table_stats  (ownname=>'scott',tabname=>'emp',estimate_percent=>100,method_opt=>'for all columns size   repeat');  PL/SQL procedure successfully completed.  SQL> select table_name,num_rows,blocks,last_analyzed from dba_tables where   owner='SCOTT';  TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZE ------------------------------ ---------- ---------- ------------ DEPT                                    4          5 02-DEC-15 EMP                                 30014         80 19-MAY-16 SALGRADE                                5          5 02-DEC-15 BONUS                                   0          0 02-DEC-15 T2                                      0          0 03-MAY-16  SQL> select   table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from   dba_indexes where owner='SCOTT' and table_name='EMP';  TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS   DISTINCT_KEYS   NUM_ROWS LAST_ANALYZE ------------------------------ ------------------------------ ---------- -----------   ------------- ---------- ------------ EMP                            PK_EMP                                  1          57           30014      30014 03-MAY-16 EMP                            EMP_EMPNO_DEPTNO                        1          72           30014      30014 03-MAY-16 

但如果在dbms_stats.gather_table_stats过程指定cascade为true还是会在收集表统计信息的同时收集索引统计信息

SQL> exec dbms_stats.gather_table_stats  (ownname=>'scott',tabname=>'emp',estimate_percent=>100,method_opt=>'for all columns size   repeat',cascade=>true);          PL/SQL procedure successfully completed.  SQL>  select table_name,num_rows,blocks,last_analyzed from dba_tables where   owner='SCOTT';  TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZE ------------------------------ ---------- ---------- ------------ DEPT                                    4          5 02-DEC-15 EMP                                 30014         80 19-MAY-16 SALGRADE                                5          5 02-DEC-15 BONUS                                   0          0 02-DEC-15 T2                                      0          0 03-MAY-16  SQL> select   table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from   dba_indexes where owner='SCOTT' and table_name='EMP';  TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS   DISTINCT_KEYS   NUM_ROWS LAST_ANALYZE ------------------------------ ------------------------------ ---------- -----------   ------------- ---------- ------------ EMP                            PK_EMP                                  1          57           30014      30014 19-MAY-16 EMP                            EMP_EMPNO_DEPTNO                        1          72           30014      30014 19-MAY-16  

set_schema_prefs
用来修改dbms_stats.gather_*_stats过程的缺省参数值，只对指定方案中的所有已经存在的对象生效。这个过程将会对指定方案中的每个表调用一次set_table_prefs过程。因为set_schema_prefs过程会对方案中的每个表调用一次set_schema_prefs过程，所以不会对该过程执行以后所创建的任何对象产生影响，新创建的对象将对所有参数使用global_pref值。
语法:
dbms_stats.set_schema_prefs(ownname in varchar2,pname in varchar2,pvalue in varchar2);
例如，以下命令设置当表中的记录数发生改变的百分比超过一定阈值就认为表的统计信息老旧了，并且应该对scott方案中的所有对象重新收集统计信息:

exec dbms_stats.set_schema_prefs(ownname=>'scott',pname=>'stale_percent',pvalue=>'5'); 

set_database_prefs
用来修改dbms_stats.gather_*_stats过程的缺省参数值，对数据库中所有用户定义的方案生效。通过设置add_sys参数为true可以包含sys与system方案。这个过程会对数据库中的每个表调用一次set_table_prefs过程。所以在过程执行后所创建的新对象，所有参数都将使用global_pref值。
语法:
dbms_stats.set_databasae_prefs(pname in varchar2,pvalue in varchar2,add_sys in boolean

default false);

例如，下面的命令对所有方案中表中数据分布存在倾斜的列收集直方图：
exec dbms_stats.set_database_prefs(pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE

SKEWONLY',add_sys=>TRUE)

相对于10g,11g对于统计信息收集新增了以下参数:
publish:这个参数决定在统计信息收集操作完成后是否发布新收集的统计信息。从11gr1开始，用户可以收集统计信息但不立即发布使用统计信息。在发布使用新收集的统计信息之前DBA可以测试新收集的统计信息。缺省值:true,可选值为true|false

incremental:这个参数决定在每次对分区收集统计信息时是否收集分区的全局统计信息。缺省值为:false,可选值true|false

stale_percent:这个参数判断表中记录发生改变的百份比达到指定阈值后，表的统计信息将会被识为过时应该重新收集统计信息，缺省值:10%，可选值:正数

将参数还原成缺省值
为了将参数设置成为缺省值，可以将参数值设置为null:
exec dbms_stats.set_global_prefs('no_invalidate',null);

查看参数值的方法
为了查看参数的当前值可以使用dbms_stats.get_prefs过程:
dbms_stats.get_prefs，这个函数将返回指定参数的缺省值,其语法如下:
dbms_stats.get_prefs(pname in varchar2,ownname in varchar2 default null,tabname in

varchar2 default null)

当指定owner(ownname)与table_name(tabname)时将返回对于该表的特定设置，其它情况下返回的是全局设置。

例如:

SQL>  select dbms_stats.get_prefs('CASCADE','SCOTT','EMP') from dual;   DBMS_STATS.GET_PREFS('CASCADE','SCOTT','EMP')  --------------------------------------------------------------------------------  FALSE     SQL> select dbms_stats.get_prefs('STALE_PERCENT') from dual;   DBMS_STATS.GET_PREFS('STALE_PERCENT')  --------------------------------------------------------------------------------  10 

在收集统计信息之前可以根据需要与测试结果来对这些参数进行设置，这样在使用Oracle统计信息收集job来收集统计信息时就可以满足你的需要。