BNFD - Your DBA NoteBook On The Net!!!

Wednesday, April 1, 2009

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


2 comments:

Anandkumar S.M said...

Hi Dipti

I appreciate your blogs related to oracle but, what I feel is, the contents are much basic in nature !!! it makes sense for newbies but not for intermediate or advanced DBA's.

But I appreciate your good work. Keep on blogging, May help newbies !!!!

Anandkumar S.M
Oracle DBA
Bangalore-India

Lalatendu Patro said...

Hi Dipti...

Its great to know that you became a DBA. May be when I need some DB related help I can take your consultaion. Good luck