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