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 username. If the parameter is set to NULL, the current login 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. The default value is 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. The default value is FALSE. If you set the value to TRUE, statistics will be collected even if statistics are locked. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The user does not exist, or you do not have the required privileges. |
| OBE-20001 | The input value is incorrect. |
Considerations
To call this procedure, you must be the owner of the table. To call this procedure on a table object owned by the SYS user, you must be the table owner or have the SYSDBA privilege.
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