The GATHER_SCHEMA_STATS procedure is used to collect statistics for all objects in a 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 |
Explanation |
|---|---|
| ownname | The schema to which the table belongs. |
| 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:
FOR ALL COLUMNS SIZE AUTO. |
| degree | Degree of parallelism. Default value is NULL. NULL Indicates the use of a 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 during statistics collection. 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 and ignore the lock status. Default is FALSE. If set to TRUE Statistics collection will continue even if statistics locks are acquired. |
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
Gathers statistics for all tables in the hr schema.
obclient> CALL DBMS_STATS.GATHER_SCHEMA_STATS ('hr', degree=>64,no_invalidate=>TRUE);
Query OK, 0 rows affected
References
For more information about collecting statistics for all objects in a specified schema, see Statistics.