The GATHER_TABLE_STATS procedure collects statistics on 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);
Parameters
| Parameter | Description |
|---|---|
| ownname | The schema where the table to be analyzed resides. |
| tabname | The name of the table. |
| partname | The name of the partition. |
| estimate_percent | The percentage of rows to be estimated. Value range: [0.000001,100]. |
| block_sample | Specifies whether to use random block sampling (TRUE) or random row sampling (FALSE) for the database. Default value: FALSE. Random block sampling is more efficient, but sample values may be correlated if data is not randomly distributed on the disk. |
| method_opt | You can use only the FOR ALL syntax when setting global preferences or preferences at the schema, database, or dictionary level. Other than that, you can choose either or both of the following options for method_opt:
|
| degree | The degree of parallelism (DOP). Default value: NULL. NULL indicates that the default table DOP specified in the DEGREE clause of the CREATE TABLE or ALTER TABLE statement is used. When DEGREE=>NULL or DEGREE=>n is specified, DBMS_STATS may adopt serial execution if parallel execution cannot be guaranteed due to the object size. |
| granularity | The granularity of statistics to be collected (applicable only when the table is partitioned).
|
| cascade | Specifies to collect index statistics besides table and column statistics, which is equivalent to running the GATHER_INDEX_STATS procedure on each index in the schema. |
| no_invalidate | Specifies whether to refresh the plan cache during statistics collection. If you set the value to TRUE, the plan cache will not be refreshed during statistics collection. |
| stattype | The location where statistics are stored. |
| force | Specifies whether to perform forced collection and ignore the lock status. Default value: FALSE. If you set the value to TRUE, statistics will be collected even if they are locked. |
Exceptions
| Error code | Description |
|---|---|
| HY000 |
|
Considerations
To call this procedure, you must be the owner of the table. If the object is in the sys tenant, you must be the owner of the table or have the SYSDBA privilege.
Examples
Collect the statistics on the tbl1 table of the testUser01 user.
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