Gather Schema Statistics

DBMS_STATS.GATHER_SCHEMA_STATS

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.

Example:


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:

The meaning of various parameters are as below (from oracle's official documentation library)

Parameter	Description
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.
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.

[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:


To re-enable the job:


To find out whether the automatic job is enabled or not use the following query:


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.