Examples:
exec dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
cascade => true, -
degree => 15 -
)
gather Reanalyzes the whole schema
gather empty - Only analyzes tables that have no existing statistics
gather stale - Only reanalyzes tables with more than 10% modifications
(inserts, updates, deletes).
gather auto - Reanalyzes objects which currently have no statistics and
objects with stale statistics (Using gather auto is like combining gather
stale and gather empty.)
Using the repeat option will only reanalyze indexes with existing histograms,
and will not search for other histograms opportunities. This is the way that
you will reanalyze you statistics on a regular basis.
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true,
degree => bms_stats.auto_degree
);
end;
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameter Description
ownname
Schema of table to analyze
tabname
Name of table
partname
Name of partition
estimate_percent
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics.
This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure,
SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
block_sample
Whether or not to use random block sampling instead of random row sampling. Random block sampling
is more efficient, but if the data is not randomly distributed on disk, then the sample values may
be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt
Accepts either of the following options, or both in combination:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | (extension)
- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload
of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
- column_name : name of a column
- extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the
SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
degree
Degree of parallelism. The default for degree is NULL. The default value can be changed using the
SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and
SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in
the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the
default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of
parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default
value based on number of CPUs and initialization parameters) according to size of the object.
granularity
Granularity of statistics to collect (only pertinent if the table is partitioned).
'ALL' - gathers all (subpartition, partition, and global) statistics
'APPROX_GLOBAL AND PARTITION' - similar to 'GLOBAL AND PARTITION' but in this case the global statistics
are aggregated from partition level statistics. This option will aggregate all statistics except the
number of distinct values for columns and number of distinct keys of indexes. The existing histograms
of the columns at the table level are also aggregated.Global statistics are gathered if partname is
NULL or if the aggregation cannot be performed (for example, if statistics for one of the partitions is missing).
'AUTO'- determines the granularity based on the partitioning type. This is the default value.
'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported,
it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this
functionality. Note that the default value is now 'AUTO'.
'GLOBAL' - gathers global statistics
'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are
gathered even if it is a composite partitioned object.
'PARTITION '- gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics.
cascade
Gathers statistics on the indexes for this table. Using this option is equivalent to running the GATHER_INDEX_STATS
Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether
index statistics are to be collected or not. This is the default. The default value can be changed using the
SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
stattab
User statistics table identifier describing where to save the current statistics
statid
Identifier (optional) to associate with these statistics within stattab
statown
Schema containing stattab (if different than ownname)
no_invalidate
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately
if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors.
This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS
Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
force
Gather statistics of table even if it is locked
GATHER_SCHEMA_STATS Procedures
This procedure gathers statistics for all objects in a schema.
Syntax
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE'),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);