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');
