GATHER_SCHEMA_STATS procedure - Collects schema statistics

The GATHER_SCHEMA_STATS procedure collects schema statistics that are stored in the system catalog or in specified statistic tables.

Syntax

Parameters

ownname: An input argument of type VARCHAR(128) that specifies the schema of the table.; The argument is case-sensitive.
estimate_percent: An input argument of type INTEGER that specifies the percentage of rows that are sampled when statistics are gathered.
block_sample: An input argument of type BOOLEAN that specifies whether random page sampling is used.
method_opt: An input argument of type VARCHAR(32672) that specifies for which columns statistics are collected.
degree: An input argument of type INTEGER that specifies the degree of parallelism.
granularity: An input argument of type VARCHAR(128) that specifies the granularity of the statistics that are collected.
cascade: An input argument of type BOOLEAN that specifies whether to gather statistics also for the indexes.
stattab: An input argument of type VARCHAR(128) that specifies the identifier of the table where the current user statistics are to be saved.; The default value is NULL.; The argument is case-sensitive.
statid: An input argument of type VARCHAR(128) that specifies the identifier with which the statistics within stattab are associated.
options: An input argument of type VARCHAR(128) that specifies the objects for which statistics are gathered.
objlist: An output argument of type ObjectTab that returns the list of objects that are stale or empty.
statown: An input argument of type VARCHAR(128) that specifies the schema that contains the stattab input argument.; The default value is NULL.; The argument is case-sensitive.
no_invalidate: An input argument of type BOOLEAN that, if it is set to TRUE, specifies whether the dependent cursor is to be invalidated.
gather_temp: An input argument of type BOOLEAN that specifies whether statistics about global temporary tables are gathered.
gather_fixed: An input argument of type BOOLEAN that specifies whether statistics about dynamic performance views are gathered.
stattype: An input argument of type VARCHAR(128) that specifies the type of statistical data that is stored in stattab.
force: An input argument of type BOOLEAN that specifies whether statistics are gathered about the object even if it is locked.

Authorization

EXECUTE privilege on the DBMS_STATS module.

Example 1

The following procedure collects the schema statistics in the STATSTAB user-defined statistics table.

BEGIN
CALL DBMS_STATS.GATHER_SCHEMA_STATS(CURRENT SCHEMA,
CASCADE=>FALSE,
STATOWN=>CURRENT SCHEMA,
STATTAB=>'STATSTAB',
STATID=>'SCHEMA_STAT',
STATTYPE=> 'S');
END;

DB20000I  The SQL command completed successfully.

Example 2

BEGIN
CALL DBMS_STATS.GATHER_SCHEMA_STATS(CURRENT SCHEMA,
ESTIMATE_PERCENT=>10,
BLOCK_SAMPLE=>TRUE,
METHOD_OPT=>'FOR ALL COLUMNS',
DEGREE=>4 ,
GRANULARITY=> 'ALL',
CASCADE=>FALSE,
OPTIONS=> 'GATHER',
NO_INVALIDATE=> FALSE,
FORCE=> FALSE,
GATHER_TEMP=> FALSE,
GATHER_FIXED=> FALSE
);
END;

DB20000I  The SQL command completed successfully.