The GATHER_TABLE_STATS procedure is used to collect statistics for tables and columns.
Syntax
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT AUTO_SAMPLE_SIZE,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
cascade BOOLEAN DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
hist_est_percent NUMBER DEFAULT AUTO_SAMPLE_SIZE,
hist_block_sample BOOLEAN DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| ownname | The schema where the table to be analyzed is located. |
| tabname | The name of the table. |
| partname | The name of the partition. |
| estimate_percent | The percentage of rows to be estimated. Valid range: [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 be one of the following options, or a combination of them:
|
| 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 object size cannot guarantee parallel execution, DBMS_STATS may use serial execution when DEGREE=>NULL or DEGREE=>n is specified. |
| granularity | The granularity of the statistics to be collected (applicable only when the table is partitioned).
|
| cascade | In addition to collecting statistics for tables and columns, 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 lock status. The default is FALSE. If set to TRUE, statistics are collected even if they are locked. |
| hist_est_percent | The sampling ratio for histograms. |
| hist_block_sample | The sampling method for histograms. |
Exceptions
| Error Code | Description |
|---|---|
| HY000 |
|
Considerations
You must be the owner of the table to call this procedure. For objects owned by SYS, you must be the owner of the table or have the SYSDBA privilege to call this procedure.
Examples
Collect statistics for the tbl1 table of user testUser01.
obclient> CALL DBMS_STATS.GATHER_TABLE_STATS ('testUser01', 'tbl1', method_opt=>'FOR ALL COLUMNS SIZE
5', granularity=>'ALL', degree=>4, no_invalidate=>FALSE);
Query OK, 0 rows affected