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 |
Explanation |
|---|---|
| ownname | The schema of the table to be analyzed. |
| tabname | Table name. |
| partname | Partition name. |
| estimate_percent | Estimated percentage of rows. The valid range is [0.000001,100]. |
| block_sample | Determine whether the database uses 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 disk, the sample values may be correlated. |
| method_opt | When setting preferences at the global, schema, database, or dictionary level, only the following operations are allowed: FOR ALL syntax. In addition, method_opt Only one of the following options, or a combination of both, is allowed:
|
| degree | Degree of parallelism. Default value is NULL. NULL indicates that the function is used by CREATE TABLE or ALTER TABLE In the statement, DEGREE The default value of the table specified by the clause. When DEGREE=>NULL or DEGREE=>n is used, if the object size does not guarantee parallel execution, DBMS_STATS may use serial execution. |
| granularity | The granularity of the statistics to be collected (only applicable when the table is partitioned).
|
| cascade | In addition to collecting statistics on tables and columns, statistics on indexes are also collected. This is equivalent to running the GATHER_INDEX_STATS The process. |
| no_invalidate | Whether to flush the plan cache when statistics are collected. Valid values: TRUE Indicates that the plan cache is not flushed during statistics collection. |
| stattype | The location where the current statistics are stored. |
| force | Whether to forcibly collect the data, ignoring the lock status. Default value: FALSE. If set to TRUE, statistics collection will continue even if the statistics are locked. |
| hist_est_percent | Specifies the sampling ratio for the histogram. |
| hist_block_sample | Specifies the sampling method for the histogram. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 |
|
Usage instructions
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the table owner 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