The GATHER_SCHEMA_STATS procedure collects the statistics on all objects of the specified user.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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 name of the user. If the username is set to NULL, the current logon username is used by default. |
| 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 when statistics are collected. If you set the value to TRUE, the plan cache is not refreshed when statistics are collected. |
| 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 |
|---|---|
| ORA-20000 | The user does not exist, or you do not have the required privileges. |
| ORA-20001 | The input value is incorrect. |
Considerations
To call this procedure, you must be the owner of the table. For objects in the sys tenant, you must be the owner of the table or have the SYSDBA privilege to call this procedure.
Procedures with parameters whose values are TRUE or FALSE must be executed in the BEGIN ... END block.
Examples
Collect the statistics on all tables of the hr user.
obclient> BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS ('hr', degree=>64,no_invalidate=>TRUE);
END;
Query OK, 0 rows affected