What are the default parameter values ?
select dbms_stats.get_param('cascade') from dual;
select dbms_stats.get_param('degree') from dual;
select dbms_stats.get_param('estimate_percent') from dual;
select dbms_stats.get_param('method_opt') from dual;
select dbms_stats.get_param('no_invalidate') from dual;
select dbms_stats.get_param('granularity') from dual;
DEFAULT PARAMETER
DBMS_STATS.AUTO_CASCADE
NULL
DBMS_STATS.AUTO_SAMPLE_SIZE
FOR ALL COLUMNS SIZE AUTO
DBMS_STATS.AUTO_INVALIDATE
AUTO
ownname : Schema to analyze (NULL means current schema).
estimate_percent : Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100).
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 : Method options of the following format (the phrase ‘SIZE 1’ is required to ensure gathering statistics in parallel and for use with the phrase hidden):
FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer]
This value is passed to all of the individual tables.
degree : Degree of parallelism (NULL means use table default value).
granularity : Granularity of statistics to collect (only pertinent if the table is partitioned).
DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather subpartition-level statistics.
PARTITION: Gather partition-level statistics.
GLOBAL: Gather global statistics.
ALL: Gather all (subpartition, partition, and global) statistics.
cascade : Gather statistics on the indexes as well.
Index statistics gathering is not parallelized. Using this option is equivalent to running the gather_index_stats procedure on each of the indexes in the schema in addition to gathering table and column statistics.
stattab : User stat table identifier describing where to save the current statistics.
statid : Identifier (optional) to associate with these statistics within stattab.
options : Further specification of which objects to gather statistics for:
GATHER: Gather statistics on all objects in the schema.
GATHER STALE: Gather statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.
GATHER EMPTY: Gather statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.
LIST STALE: Return list of stale objects as determined by looking at the *_tab_modifications views.
LIST EMPTY: Return list of objects which currently have no statistics.
objlist : List of objects found to be stale or empty.
statown : Schema containing stattab (if different than ownname).
DBMS_STATS.GATHER_TABLE_STATS
This procedure gathers table and column (and index) statistics.
It attempts to parallelize as much of the work as possible, but there
are some restrictions as described in the individual parameters.
This operation will not parallelize if the user does not have select
privilege on the table being analyzed.
Input arguments:
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 set_param 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 – method options of the following format
method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause]
column|attribute [size_clause]
[,column|attribute [size_clause] … ]
size_clause := SIZE [integer | auto | skewonly | repeat],
where integer is between 1 and 254
column := column name | extension name | extension
default is FOR ALL COLUMNS SIZE AUTO.
The default value can be changed using set_param procedure.
Optimizer related table statistics are always gathered.
If an extension is provided, the procedure create the extension if it
does not exist already. Please refer to create_extended_stats for
description of extension.
degree – degree of parallelism (NULL means use of table default value
which was specified by DEGREE clause in CREATE/ALTER TABLE statement)
Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
based on the initialization parameters.
default for degree is NULL.
The default value can be changed using set_param procedure.
granularity – the granularity of statistics to collect (only pertinent
if the table is partitioned)
‘AUTO’ – the procedure determines what level of statistics to collect
‘GLOBAL AND PARTITION’ – gather global- and partition-level statistics
‘APPROX_GLOBAL AND PARTITION’ – This option is similar to
‘GLOBAL AND PARTITION’. But the global statistics are aggregated
from partition level statistics. It will aggregate all statistics except 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.The global statistics are gathered
(i.e., going back to GLOBAL AND PARTITION behaviour)
if partname argument is null. The aggregation will use only
partitions with statistics, so to get accurate global statistics,
user has to make sure to have statistics for all partitions.
This option is useful when you collect statistics for a new partition added
into a range partitioned table (for example, a table partitioned by month).
The new data in the partition makes the global statistics stale (especially
the min/max values of the partitioning column). This stale global statistics
may cause suboptimal plans. In this scenario, users can collect statistics
for the newly added partition with ‘APPROX_GLOBAL AND PARTITION’
option so that the global statistics will reflect the newly added range.
This option will take less time than ‘GLOBAL AND PARTITION’ option since the
global statistics are aggregated from underlying partition level statistics.
Note that, if you are using APPROX_GLOBAL AND PARTITION,
you still need to collect global statistics (with granularity = ‘GLOBAL’ option)
when there is substantial amount of change at the table level.
For example you added 10% more data to the table. This is needed to get the
correct number of distinct values/keys statistic at table level.
‘SUBPARTITION’ – gather subpartition-level statistics
‘PARTITION’ – gather partition-level statistics
‘GLOBAL’ – gather global statistics
‘ALL’ – gather all (subpartition, partition, and global) statistics
default for granularity is AUTO.
The default value can be changed using set_param procedure.
cascade – gather statistics on the indexes for this table.
Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine
whether index stats to be collected or not. This is the default.
The default value can be changed using set_param procedure.
Using this option is equivalent to running the gather_index_stats
procedure on each of the table’s indexes.
stattab – The user stat table identifier describing where to save
the current statistics.
statid – The (optional) identifier to associate with these statistics
within stattab.
statown – The schema containing stattab (if different then ownname)
no_invalidate – Do not invalide 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 dependend cursors. This is the default. The default
can be changed using set_param procedure.
When the ‘cascade’ argument is specified, not pertinent with certain
types of indexes described in the gather_index_stats section.
force – gather statistics of table even if it is locked.
context – internal use only.
Exceptions:
ORA-20000: Table does not exist or insufficient privileges
ORA-20001: Bad input value
ORA-20002: Bad user statistics table, may need to upgrade it
ORA-20005: object statistics are locked