1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Gather Schema Statistics

Discussion in 'Server Administration and Options' started by tyro, Apr 3, 2009.

  1. tyro

    tyro Forum Genius

    Likes Received:
    Trophy Points:

    In Oracle Database when there is something called Cost Based Optimizer (CBO) which other than doing many things also does query optimization. That is it determines the best or optimum method to run a query. Now when large changes to the data or database take place, the system performance may get slow. DBMS_STATS is the package which collects statistics for Cost Based Optimizer. Collecting Statistics lets the CBO know of the best methods for query optimization. The statistics include the Table statistics, Column statistics, Index statistics and System statistics. The DBMS_STATS package includes the following procedures
    1. GATHER_INDEX_STATS, Index statistics
    2. GATHER_TABLE_STATS, Table, column, and index statistics
    3. GATHER_SCHEMA_STATS,Statistics for all objects in a schema
    4. GATHER_DICTIONARY_STATS,Statistics for all dictionary objects
    5. GATHER_DATABASE_STATS,Statistics for all objects in a database

    Of all of the above usually the most used is GATHER_SCHEMA_STATS to gather statistics of all objects within a particular Schema. The Gather Schema Statistics process should be run on a regular basis (weekly at a minimum) and anytime large changes to the data or database take place.


    Code (Text):
        (ownname => 'APPS',
        estimate_percent => dbms_stats.auto_sample_size E "sample_size",
        options => 'GATHER EMPTY'
    The above call starts the gather_schema_stats for a schema called APPS with estimate_percent at default sample size and options gather empty which gathers statistics on objects which currently have no statistics.

    [BREAK=Detailed Syntax And Parameters]
    The Syntax of the dbms_stats.gather_schema_stats is as follows:
    Code (Text):

       ownname          VARCHAR2,
       estimate_percent NUMBER   DEFAULT to_estimate_percent_type
       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 'AUTO',
       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 (
    The meaning of various parameters are as below (from oracle's official documentation library)

    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]. 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_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|The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.
    degree|Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement.
    granularity|Granularity of statistics to collect (only pertinent if the table is partitioned). Default is AUTO
    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 statistics 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: Gathers statistics on all objects in the schema.
    • GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
    • GATHER STALE: Gathers 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: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.
    • LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.
    • LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views.
    • LIST EMPTY: Returns list of objects which currently have no statistics.
    |statown|Schema containing stattab (if different than ownname).
    no_invalidate|If set to the default, does not invalidate the shared cursors dependent on the objects for which statistics are being gathered. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the cascade option because GATHER_INDEX_STATS does not support the cascade option. This option has no effect when an index is picked up as a target because GATHER_INDEX_STATS does not support this option.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_PARAM Procedure.[/table]
    [BREAK=Automatic gathering and Oracle Applications]
    Since Oracle Database 10g, Oracle has automated the process of collection of database statistics through a scheduled job that generates the database statistics for. (We will still need to collect system statistics since system statistics are not collected by the automatic statistics gathering mechanism.). Therefore, Oracle will automatically estimate the sample size, detect skewed columns that would benefit from histograms, and refresh the schema statistics when they become stale.

    You can disable automated statistics collection job using the code below:

    Code (Text):
    EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’);
    To re-enable the job:

    Code (Text):
    EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’);
    To find out whether the automatic job is enabled or not use the following query:

    Code (Text):
    Now let us take a look at the Syntax and an example

    Lastly mention should be made about Oracle Applications Gather Schema Statistics Concurrent Request

    If you use Oracle Applications, it is recommended to use the concurrent programs for gathering statistics. The procedure to run the concurrent request (thanks Sadik) is as below:

    1. Responsibility = System Administrator
    2. Request > Run > Gather Schema Statistics
    3. Enter the parameters. This can be run for specific schemas by specifying the schema name or entering 'ALL' to gather statistics for every schema in the database. Other Parameters are more or less same as in the Procedure.
    4. Submit the gather schema statistics program.
  2. Sadik

    Sadik Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Nice article tyro