GATHER_SCHEMA_STATS

The GATHER_SCHEMA_STATS procedure is used to collect statistics for all objects in the specified schema.

Syntax

DBMS_STATS.GATHER_SCHEMA_STATS (
  ownname            VARCHAR2,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  block_sample       BOOLEAN DEFAULT FALSE,
  method_opt         VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  cascade            BOOLEAN DEFAULT NULL,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  stattype           VARCHAR2 DEFAULT 'DATA',
  force              BOOLEAN DEFAULT FALSE);

Parameters

Parameter	Description
ownname	The schema where the table is located.
estimate_percent	The percentage of rows to estimate. The valid range is [0.000001,100].
block_sample	Specifies whether to use random block sampling (TRUE) or random row sampling (FALSE). The default value is FALSE. Random block sampling is more efficient, but if the data is not randomly distributed on the disk, the sample values may be correlated.
method_opt	When setting preferences at the global, schema, database, or dictionary level, only the FOR ALL syntax is allowed. Otherwise, method_opt can only be one of the following options or a combination of them: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [column_clause] where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}. column_clause is defined as column_clause := column_name | extension name | extension. integer: the number of histogram buckets. The valid range is [1,2048]. REPEAT: collect histograms only for columns that already have histograms. AUTO: the seekdb optimizer decides whether to collect histograms for columns based on their usage. The default number of histogram buckets is 254. SKEWONLY: collect histograms only for columns with skewed data distribution. The default number of histogram buckets is 254. column_name: the name of the column. extension: a column group or expression in the format (column_name, colume_name [, ...]). The default value is FOR ALL COLUMNS SIZE AUTO.
degree	The degree of parallelism. The default value is NULL. NULL indicates that the default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement is used. If the size of the object is not guaranteed to support parallel execution, DBMS_STATS may use serial execution when DEGREE=>NULL or DEGREE=>n is specified.
granularity	The granularity of the statistics to collect (applicable only when the table is partitioned). ALL: collect statistics for all (subpartitions, partitions, and global) levels. APPROX_GLOBAL AND PARTITION: similar to GLOBAL AND PARTITION, but in this case, global statistics are aggregated from partition-level statistics. This option aggregates all statistics except the count of distinct values and the count of distinct keys. However, it only aggregates partitions that have statistics. If partname is NULL or if aggregation is not possible (e.g., statistics are missing for one of the partitions), global statistics are collected. AUTO: determine the granularity based on the partition type. This is the default value. DEFAULT: collect statistics at the global and partition levels. This option is deprecated. We recommend using GLOBAL AND PARTITION instead. GLOBAL: collect global statistics. GLOBAL AND PARTITION: collect statistics at the global and partition levels. Subpartition-level statistics are not collected for composite partitioned objects. PARTITION: collect statistics at the partition level. SUBPARTITION: collect statistics at the subpartition level.
cascade	In addition to collecting table and column statistics, also collect statistics for indexes, equivalent to running the GATHER_INDEX_STATS procedure on each index in the schema.
no_invalidate	Specifies whether to invalidate the plan cache when collecting statistics. If set to TRUE, the plan cache is not invalidated when collecting statistics.
stattype	The location where the current statistics are stored.
force	Specifies whether to forcibly collect statistics and ignore the lock status. The default value is FALSE. If set to TRUE, statistics are collected even if they are locked.

Exceptions

Error Code	Description
HY000	The schema does not exist or the user does not have sufficient privileges. The input value is invalid.

Usage

You must be the owner of the table to call this procedure.

Examples

Collect statistics for all tables in the hr schema.

CALL DBMS_STATS.GATHER_SCHEMA_STATS ('hr', degree=>64,no_invalidate=>TRUE);
Query OK, 0 rows affected