The GATHER_SCHEMA_STATS procedure is used to collect statistics for all objects of the specified user.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only 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 username is set to NULL, the current login username is used by default. |
| estimate_percent | The percentage of rows to be estimated. The valid range is [0.000001,100]. |
| block_sample | Specifies whether to use 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 the disk, the sample values may be correlated. |
| method_opt | When setting preferences at the global, schema, database, or dictionary level, only the FOR ALL syntax is allowed. Otherwise, method_opt can only be one of the following options or a combination of them:
FOR ALL COLUMNS SIZE AUTO. |
| degree | The degree of parallelism. The default value is NULL. NULL indicates that the default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement is used. When DEGREE=>NULL or DEGREE=>n is used, and the size of the object is not guaranteed to allow 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 table and column statistics, it also collects index statistics, equivalent to running the GATHER_INDEX_STATS procedure on each index in the schema. |
| no_invalidate | Specifies whether to invalidate the plan cache when collecting statistics. If set to TRUE, the plan cache is not invalidated when collecting statistics. |
| stattype | The current location where the statistics are stored. |
| force | Specifies whether to forcibly collect statistics and ignore the lock status. The default is FALSE. If set to TRUE, statistics are collected even if they are locked. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The user does not exist or does not have sufficient privileges. |
| OBE-20001 | The input value is invalid. |
Considerations
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the owner or have the SYSDBA privilege to call this procedure.
Note that for parameters with values TRUE and FALSE, you must execute the procedure within a BEGIN ... END block.
Examples
Collect statistics for all tables under the hr schema.
obclient> BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS ('hr', degree=>64,no_invalidate=>TRUE);
END;
Query OK, 0 rows affected
References
For more information about collecting statistics for all objects of a specified user, see Manually collect statistics.