还有一个SET_TABLE_PREFS 可以单独设置表的一些属性

BEGIN

 DBMS_STATS.SET_TABLE_PREFS ( ownname =>'HFF', tabname =>'T1', pname=>'METHOD_OPT', pvalue =>'FOR ALL COLUMNS SIZE AUTO');
END;
/

This procedure is used to set the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE for the add_sys parameter.

This procedure was introduced in Oracle V11.1.

Syntax

DBMS_STATS.SET_DATABASE_PREFS (
    pname            IN   VARCHAR2,
    pvalue           IN   VARCHAR2,
    add_sys          IN   BOOLEAN DEFAULT FALSE);

ParameterDescription
pname

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

  • CASCADE - The value determines whether or not index statistics are collected as part of gathering table statistics.

  • DEGREE - The value determines degree of parallelism used for gathering statistics.

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

  • METHOD_OPT - The value 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|attribute [size_clause] [,column|attribute [size_clause]...]

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

    column is defined as column := column_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 - The value 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 - This value determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to 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 - This value 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:

    • the INCREMENTAL value for the partitioned table is set to TRUE;
       
    • the PUBLISH value for the partitioned table is set to TRUE;
       
    • the 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 timely operation for large tables.

  • STALE_PERCENT - This value 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%.

pvaluePreference value. If NULL is specified, it will set the Oracle default value.
add_sysValue TRUE will include the Oracle-owned tables

Exceptions

  • ORA-20000: Insufficient privileges.
     
  • ORA-20001: Invalid or illegal input values.

Usage Notes

To run this procedure, you need to have the SYSDBA role or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

Both arguments are of type VARCHAR2 and values are enclosed in quotes, even when they represent numbers.

Examples

DBMS_STATS.SET_DATABASE_PREFS('CASCADE', 'DBMS_STATS.AUTO_CASCADE');
DBMS_STATS.SET_DATABASE_PREFS('ESTIMATE_PERCENT','9');
DBMS_STATS.SET_DATABASE_PREFS('DEGREE','99');