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 GATHER_INDEX_STATS, Index statistics GATHER_TABLE_STATS, Table, column, and index statistics GATHER_SCHEMA_STATS,Statistics for all objects in a schema GATHER_DICTIONARY_STATS,Statistics for all dictionary objects 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: Code (Text): begin dbms_stats.gather_schema_stats (ownname => 'APPS', estimate_percent => dbms_stats.auto_sample_size E "sample_size", options => 'GATHER EMPTY' ); end; 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): DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), 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 ( get_param('NO_INVALIDATE'))); The meaning of various parameters are as below (from oracle's official documentation library) [table]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. |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): SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB'; 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: Responsibility = System Administrator Request > Run > Gather Schema Statistics 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. Submit the gather schema statistics program.