GATHER_TABLE_STATS procedure - Collects table statistics

The GATHER_TABLE_STATS procedure collects table 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.
tabname: An input argument of type VARCHAR(128) that specifies the name of the table.; The argument is case-sensitive.
partname: An input argument of type VARCHAR(128) that specifies the name of table partition.
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.
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.
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

BEGIN
CALL DBMS_STATS.GATHER_TABLE_STATS(CURRENT SCHEMA,
'TEST_TABLE_STATS',
ESTIMATE_PERCENT=>10,
BLOCK_SAMPLE=>TRUE,
METHOD_OPT=>'FOR ALL COLUMNS',
DEGREE=>0 ,
GRANULARITY=> 'ALL',
CASCADE=>FALSE,
NO_INVALIDATE=>FALSE,
FORCE=>FALSE);
END;

DB20000I  The SQL command completed successfully.

SELECT TABNAME, CARD, NPAGES, AVGROWSIZE FROM SYSSTAT.TABLES WHERE TABNAME = 'TEST_TABLE_STATS' AND TABSCHEMA = CURRENT SCHEMA

TABNAME                                   CARD                 NPAGES    AVGROWSIZE
----------------------------------------- -------------------- --------- ----------
TEST_TABLE_STATS                          6                    2         17

1 record(s) selected.</div><div>

Example 2

BEGIN
CALL DBMS_STATS.GATHER_TABLE_STATS(CURRENT SCHEMA,
'TEST_TABLE_STATS',
ESTIMATE_PERCENT=>50,
BLOCK_SAMPLE=>TRUE,
METHOD_OPT=>'FOR ALL COLUMNS',
DEGREE=>0 ,
GRANULARITY=> 'ALL',
CASCADE=>FALSE,
STATOWN=>CURRENT SCHEMA,
STATTAB=>'STATSTAB',
STATID=>'TABLE1_STAT',
NO_INVALIDATE=>FALSE,
FORCE=>FALSE);
END;

DB20000I  The SQL command completed successfully.

select STATID,C1,N2,N3,N4 from STATSTAB WHERE STATID='TABLE1_STAT'

STATID                         C1                             N2                       N3                       N4                     
------------------------------ ------------------------------ ------------------------ ------------------------ ------------------------
TABLE1_STAT                    TEST_TABLE_STATS                                      6                       17                        2

1 record(s) selected.