Gathering statistics of a table in Oracle

With the advent of cost based optimizer in Oracle, every oracle professional will understand the importance of database object statistics. For CBO to choose the best plan proper statistics is the most important requirement. Oracle provides us with a package of DBMS_STATS for proper statistics gathering.

GATHER_TABLE_STATS procudure of the package has many parameters that can be used to control the type of statistics being gathered. Although Oracle recommends to use the AUTO parameter values for many of these parameters for gathering best quality statistics.

Syntax for GATHER_TABLE_STATS:

DBMS_STATS.GATHER_TABLE_STATS (
   ownname                               VARCHAR2,
   tabname                                VARCHAR2,
   partname                               VARCHAR2 DEFAULT NULL,
   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 GET_PARAM('GRANULARITY'),
   cascade                                 BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab                                   VARCHAR2 DEFAULT NULL,
   statid                                     VARCHAR2 DEFAULT NULL,
   statown                                 VARCHAR2 DEFAULT NULL,
   no_invalidate                          BOOLEAN  DEFAULT  to_no_invalidate_type (get_param('NO_INVALIDATE')),
   force                                      BOOLEAN DEFAULT FALSE);

However seldom we use all the parameters. The important parameters used are:

ownname                     -- Name of the owner of the table, i,e. the schema name in most of the case in which the  table resides.
tabname                       -- Name of the table for  which stats needs to be gathered.
partname                      -- Name of the partition, if the table is a partitioned table.
estimate_percentage     -- The estimate percentage or the sample size to be used for gathering statistics. Recommended to use AUTO_SAMPLE_SIZE.
degree                         -- The degree of parallelism with which statistics will be gathered. AUTO is the recommended option  provided by Oracle.

Apart from these parameters there are few other parameters that can be used for controlling the quality of statistics.

granularity        -- For partitioned table, collecting stats for each partition locally and collecting global stats for  table as a whole is recommended, because depending on the sql optimizer will select the local or the global stats. If the parameters value is set to AUTO, then oracle gathers statistics for both      locally as well as globally. However for AUTO granularity, oracle gathers only for incremental    
partitions that has been changed more than 10% if the INCREMENTAL value is set to TRUE else if it is set to FALSE then stats for whole partition and global partition is set.
method_opt        -- The method_opt parameter can be used to create histogram statistics for table containing skewed data. Oracle recommends using FOR ALL COLUMNS SIZE AUTO. This leads oracle to decide which column needs histogram and for which column only normal statistics will suffice.


Monitoring tabe statistics: 
It takes oracle few minutes to propagate the statistics data to data dictionary views. To view the statistics immediately use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO.