The GATHER_SCHEMA_STATS procedure collects 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 to which the table belongs. |
| 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:
FOR ALL COLUMNS SIZE AUTO. |
| 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 all tables in the hr schema.
obclient> CALL DBMS_STATS.GATHER_SCHEMA_STATS ('hr', degree=>64,no_invalidate=>TRUE);
Query OK, 0 rows affected
