SET_TABLE_PREFS

1、SET_TABLE_PREFS  语法

SET_TABLE_PREFS Procedure

This procedure isused to set the statistics preferences of the specified table in the specifiedschema.

Syntax

DBMS_STATS.SET_TABLE_PREFS (

    ownname    IN  VARCHAR2,

    tabname    IN  VARCHAR2,

    pname      IN  VARCHAR2,

    pvalue     IN  VARCHAR2);

Parameters

Table 141-93SET_TABLE_PREFS Procedure Parameters

Parameter

Description

ownname

Owner name

tabname

Table name

pname

Preference name. The default value for following preferences can be set:

·         CASCADE

·         DEGREE

·         ESTIMATE_PERCENT

·         METHOD_OPT

·         NO_INVALIDATE

·         GRANULARITY

·         PUBLISH

·         INCREMENTAL

·         STALE_PERCENT

.

CASCADE - Determines whether or not index statistics are collected as part of gathering table statistics.

.

DEGREE - Determines degree of parallelism used for gathering statistics.

.

ESTIMATE_PERCENT - Determines the percentage of rows to estimate. The valid range is [0.000001,100]. Use the constantDBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.

.

METHOD_OPT - Controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:

·         FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

·         FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | extension name | extension

- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
- column_name : Name of a column
- extension : Can be either a column group in the format of (column_name, colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO.

.

NO_INVALIDATE - The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default.

.

GRANULARITY - Determines granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL' - Gathers all (subpartition, partition, and global) statistics

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - Gathers subpartition-level statistics.

.

PUBLISH - Determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.

.

INCREMENTAL - Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

·         INCREMENTAL value for the partitioned table is set to TRUE;

·         PUBLISH value for the partitioned table is set to TRUE;

·         User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.

.

STALE_PERCENT - Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.

pvalue

Preference value. If NULL is specified, it will set the Oracle default value.

1） PUBLISH：收集完统计信息以后是否立即将统计信息发布到数据字典里，还是将它们存放在私有区域里。TRUE表示立即发布，FALSE表示存放到私有区域里。
 2） STALE_PERCENT：确定某个对象的统计信息过时的上限，如果过时就需要重新收集统计信息，缺省为10.计算某个表的统计信息是否过时，oracle会计算自从上一次收集该表的统计信息以来，该表中被修改的数据行数占该表的总行数的百分比。然后用得出的百分比值与该选项配置的值（如果缺省，就是10）进行比较，大于10，则说明该表的统计信息过时了，需要重新收集统计信息；否则就认为该表的统计信息不过时，不用再次收集。 

3） INCREMENTAL：在分区表上收集global的统计信息时（将GRANULARITY设置为GLOBAL），采用增量方式完成。使用该选项是因为对于某些分区表来说，比如按照月份进行范围分区的分区表来说，除了代表当前月的分区里的数据会经常变化以外，其他分区里的数据不会变动。因此在收集该分区表上的global的统计信息时，就没有必要再次扫描那些非当前月的分区了。如果你将INCREMENTAL设置为TRUE时，则在收集统计信息时，就不会扫描那些非当前月的分区里的数据，而只会扫描当前月的分区里的数据。最后将非当前月的分区上已经存在的统计信息加上当前月新算出来的统计信息合并就得出了分区表的global的统计信息。 
  可以从视图：DBA_TAB_STAT_PREFS里看到所有的收集统计信息时的各个选项的值。

2、设置参数

获取global的统计信息收集设置选项

select dbms_stats.get_prefs('method_opt') from dual;

select dbms_stats.get_prefs('concurrent') from dual; 

select dbms_stats.get_prefs('GRANULARITY') from dual;

select dbms_stats.get_prefs('INCREMENTAL') from dual;

设置global的统计信息收集选项

exec DBMS_STATS.SET_PARAM('DEGREE',4);

exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');

获取某个表的信息收集选项

 select dbms_stats.get_prefs('STALE_PERCENT',owner,'table_name') from dual;

 select dbms_stats.get_prefs('method_opt'',null,'table_name') from dual;

设置某个表的统计信息

               exec dbms_stats.set_table_prefs(owner,'table_name','STALE_PERCENT',5);

              exec dbms_stats.set_table_prefs('OWNER','TABLE_NAME','METHOD_OPT','FOR ALL COLUMNS size 1');

                exec dbms_stats.set_table_prefs(owner,'table_name','PUBLISH',FALSE);

  挂起的统计：

1、 查看某表统计信息是否挂起                        select dbms_stats.get_prefs('PUBLISH','OWNER','table_name') from dual;

2、  设置挂起统计信息                                                                           exec dbms_stats.set_table_prefs('OWNER','table_name','PUBLISH',false);

3、使用[DBA|ALL|USER]_TAB_PENDING_STATS和[DBA|ALL|USER]_IND_PENDING_STATS视图可以看到未发布的统计

4、如果将初始化参数OPTIMIZER_PENDING_STATISTICS设置为TRUE（默认为FALSE），优化器就可以使用挂起的统计。在会话层修改这个参数允许你测试在发布挂起的统计之后有何影响：

当系统有很大的分区表时，如果总是全部收集则会比较慢，11g之后可以设置INCREMENTAL只对数据有变动的分区做收集
    

查看分区表INCREMENTAL的值

select dbms_stats.get_prefs('INCREMENTAL',null,'table_name') from dual;

只收集数据变动的分区

exec dbms_stats.set_table_prefs(user,'table_name','INCREMENTAL','TRUE');

所有分区都要收集统计信息

exec dbms_stats.set_table_prefs(user,'table_name','INCREMENTAL','FALSE');