Ask Maclean

Gather DBMS_STATS Default parameter

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

Posted

in

by

mac

Tags:

Comments

2 responses to “Gather DBMS_STATS Default parameter”

  1. maclean Avatar
    maclean

    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).

    Reply
  2. maclean Avatar
    maclean

    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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *