dbms_stats.gather_table_stats

最新推荐文章于 2021-04-05 12:32:44 发布

转载最新推荐文章于 2021-04-05 12:32:44 发布 · 159 阅读

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm

GATHER_TABLE_STATS Procedure

This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters. This operation does not parallelize if the user does not have select privilege on the table being analyzed.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT FALSE);

Parameters

Table 70-35 GATHER_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Schema of table to analyze.
tabname	Name of table.
partname	Name of partition.
estimate_percent	Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Accepts: FOR ALL [INDEXED \| HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column\|attribute [size_clause] [,column\|attribute [size_clause]...], where size_clause is defined as: size_clause := SIZE {integer \| REPEAT \| AUTO \| SKEWONLY} 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.
degree	Degree of parallelism. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. ALL: Gather all (subpartition, partition, and global) statistics.
cascade	Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS procedure on each of the table's indexes.
stattab	User stat table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
no_invalidate	Dependent cursors are not invalidated if this parameter is set to TRUE. When the 'cascade' argument is specified, this parameter is not relevant with certain types of indexes, as described in "GATHER_INDEX_STATS Procedure".

Exceptions

ORA-20000: Table does not exist or insufficient privileges.

ORA-20001: Bad input value.

GATHER_INDEX_STATS Procedure

This procedure gathers index statistics. It attempts to parallelize as much of the work as possible. Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity and no_invalidate arguments are not relevant to these types of indexes.

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT',
   no_invalidate    BOOLEAN  DEFAULT FALSE);

Parameters

Table 70-34 GATHER_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Schema of index to analyze.
indname	Name of index.
partname	Name of partition.
estimate_percent	Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.
stattab	User stat table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
degree	Degree of parallelism (NULL means use of table default value that was specified by the DEGREE clause in the CREATE/ALTER INDEX statement). Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters.
granularity	The granularity of statistics to collect (only pertinent if the index is partitioned): 'DEFAULT' - gathers global and partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics 'PARTITION '- gathers partition-level statistics 'GLOBAL' - gathers global statistics 'ALL' - gathers all (subpartition, partition, and global) statistics
no_invalidate	Dependent cursors are not invalidated if this parameter is set to TRUE.