ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_stats.gather_table_stats

dbms_stats.gather_table_stats

原创 Linux操作系统 作者:victor1010 时间:2009-04-20 12:56:26 0 删除 编辑

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.

Exceptions

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

ORA-20001: Bad input value.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8102208/viewspace-590999/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-29

  • 博文量
    296
  • 访问量
    570909