DBMS_STATS

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement

The optimizer statistics are stored in the data dictionary.

Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.

Automatic Statistics Gathering:

Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the chances of getting poor execution plans because of missing or stale statistics.

Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:

Missing statistics

Stale statistics

This job is created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.

It calls DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure.

Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the DBA_SCHEDULER_JOBS view:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                                                     ENABLE

------------------------------ ----------------                       ---------------------

SYS                            PURGE_LOG                                                        TRUE

SYS                            GATHER_STATS_JOB                                       TRUE

SYS                            TEST_FULL_JOB_DEFINITION                     TRUE

SYS                            TEST_PROG_SCHED_JOB_DEFINITION     TRUE

SYS                            TEST_PROG_JOB_DEFINITION                     TRUE

SYS                            TEST_SCHED_JOB_DEFINITION                   TRUE

Manual Statistics:

Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. There are typically two types of such objects:

Volatile tables that are being deleted or truncated and rebuilt during the course of the day.

Objects which are the target of large bulk loads which add 10% or more to the object's total size.

Statistics are gathered using the DBMS_STATS package. This PL/SQL package is also used to modify, view, export, import, and delete statistics.

The DBMS_STATS package can gather statistics on table and indexes, and well as individual columns and partitions of tables

Procedures in DBMS_STATS Package:

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, for all system schemas, including SYS and SYSTEM and other optional schemas 

GATHER_DATABASE_STATS === Statistics for all objects in a database

When to Gather Statistic:

1.For an application in which tables are being incrementally modified

2.For tables which are being substantially modified in batch operations, such as with bulk loads

3.For partitioned tables, there are often cases in which only a single partition is modified.In those cases, statistics can be gathered only on those partitions rather than gathering statistics for the entire table

System Statistics

 Describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer. When choosing an execution plan, the optimizer estimates the I/O and CPU resources required for each query

Gather, View, Modify or Delete optimizer statistics for database objects:

Subprocedures:

GATHER_DATABASE_STATS

    (estimate_percent,block_sample,method_opt,degree, granularity,cascade,stattab, 

     statid, options,statown,gather_sys,no_invalidate,gather_temp,gather_fixed,stattype);

GATHER_INDEX_STATS 

    (ownname,indname,partname,estimate_percent,stattab,statid                  

     statown,degree,granularity,no_invalidate,stattype);

GATHER_SCHEMA_STATS

    (ownname,estimate_percent,block_sample,method_opt,degree,granularity,cascade,

     stattab,statid,options,statown,no_invalidate,gather_temp,gather_fixed);

GENERATE_STATS

    (ownname,objname,organized);

GATHER_SYSTEM_STATS 

    (gathering_mode,interval,stattab,statid,statown);

GATHER_TABLE_STATS

    (ownname,tabname,partname,estimate_percent,block_sample,method_opt,

     degree,granularity,cascade,stattab,statid,statown,no_invalidate,stattype);

partname         VARCHAR2 DEFAULT NULL,    estimate_percent NUMBER   DEFAULT NULL,     block_sample     BOOLEAN  DEFAULT FALSE,    method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',    degree           NUMBER   DEFAULT NULL,    granularity      VARCHAR2 DEFAULT 'DEFAULT',     cascade          BOOLEAN  DEFAULT FALSE,    stattab          VARCHAR2 DEFAULT NULL,     statid           VARCHAR2 DEFAULT NULL,    statown          VARCHAR2 DEFAULT NULL,    no_invalidate    BOOLEAN  DEFAULT FALSE

PREPARE_COLUMN_VALUES

    (srec,values);

SET_COLUMN_STATS 

SET_INDEX_STATS 

SET_SYSTEM_STATS 

SET_TABLE_STATS 

CONVERT_RAW_VALUE 

GET_COLUMN_STATS 

GET_INDEX_STATS 

GET_SYSTEM_STATS 

GET_TABLE_STATS 

DELETE_COLUMN_STATS

    (ownname,tabname,colname,partname,stattab,statid,

     cascade_parts,statown,no_invalidate,force);

DELETE_DATABASE_STATS

    (stattab,statid,statown,no_invalidate,stattype,force);

DELETE_INDEX_STATS

    (ownname,indname,partname,stattab,statid,cascade_parts,statown            

     no_invalidate,stattype,force);

DELETE_SCHEMA_STATS

    (ownname,stattab,statid,statown,no_invalidate

     stattype,force);

DELETE_SYSTEM_STATS

    (stattab,statid,statown); 

DELETE_TABLE_STATS 

    (ownname,tabname,partname,stattab,statid,cascade_parts,cascade_columns,    

     cascade_indexes,statown,no_invalidate,stattype,force);

CREATE_STAT_TABLE 

DROP_STAT_TABLE 

EXPORT_COLUMN_STATS 

EXPORT_INDEX_STATS 

EXPORT_SYSTEM_STATS 

EXPORT_TABLE_STATS 

EXPORT_SCHEMA_STATS 

EXPORT_DATABASE_STATS 

IMPORT_COLUMN_STATS 

IMPORT_INDEX_STATS 

IMPORT_SYSTEM_STATS 

IMPORT_TABLE_STATS 

IMPORT_SCHEMA_STATS 

IMPORT_DATABASE_STATS 

FLUSH_SCHEMA_MONITORING_INFO 

FLUSH_DATABASE_MONITORING_INFO 

ALTER_SCHEMA_TABLE_MONITORING 

ALTER_DATABASE_TABLE_MONITORING

Example:

EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);

EXEC DBMS_STATS.delete_schema_stats('SCOTT');