About Oracle Statistics

1. Introduction
2. About *_TAB_MODIFICATIONS
3. Identify STALE STATS
4. Gather STATS
       4.1 DATABASE Level
       4.2 SCHEMA Level
       4.3 TABLE Level
       4.4 INDEX Level
5. SYSTEM STATISTICS
6. How lock/unlock statistics on table

Introduction

What are statistics?

Ans: Input to the Cost-Based Optimizer, Provide information on

User Objects
	Table, Partition, Subpartition
	Columns
	Index, Index Partition, Index Subpartition
System
Dictionary
Memory structure (X$)

Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed
(total number of inserts, deletes, updates) in the table. 
Oracle monitors the DML activity for all tables and records it in the SGA. 
The monitoring information is periodically flushed to disk, and is exposed in the *_TAB_MODIFICATIONS view.

Why do we care about statistics?

Poor statistics usually lead to poor plans
Collecting good quality stats is not straightforward
Collecting good quality stats may be time consuming
Improving statistics quality improves the chance to find an optimal plan (usually)
The higher the sample the higher the accuracy
The higher the sample the longer it takes to collect
The longer it takes the less frequent we can collect fresh stats!

If your data changes frequently, then

If you have plenty of resources:
Gather statistics often and with a very large sample size

If your resources are limited: 
Use AUTO_SAMPLE_SIZE (11g)
Use a smaller sample size (try to avoid this)

If your data doesn’t change frequently: 
Gather statistics less often and with a very large sample size


Recommended syntax

/*
Assuming we want Oracle to determine where to put histograms (instead of specifying the list manually):

In 10g avoid AUTO_SAMPLE_SIZE

exec dbms_stats.gather_table_stats('owner', 'table_name', estimate_percent => NNN,granularity => “it depends”);

In 11g use AUTO_SAMPLE_SIZE but keep an eye open. 
exec dbms_stats.gather_table_stats('owner', 'table_name');
*/

About *_TAB_MODIFICATIONS

When querying *_TAB_MODIFICATIONS view you should ensure that you run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO before doing so in order to obtain accurate results.

Before

--  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -- 

SQL> select table_name, inserts, updates, deletes from dba_tab_modifications where table_name='BIG_TABLE';

no rows selected

SQL>


After 


SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from dba_tab_modifications where table_name='BIG_TABLE';

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
BIG_TABLE                             100          0          0

SQL>

Identify STALE STATS:

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_STATISTICS where STALE_STATS='YES';

OR

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES';

Gather STATS

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 4: Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO : You can specify one column between “” instead of all columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS : Collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : Collect histograms for columns have skewed value should test skewness first
FOR ALL INDEXED COLUMNS : Collect histograms for columns have indexes only.

DATABASE Level
Gathering statistics for all objects in database, cascade will include indexes

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE AUTO');
OR
-- For faster execution
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);
OR
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,CASCADE => TRUE,degree => 4);

SCHEMA level

Gathering statistics for all objects in a schema, cascade will include indexes. If not used Oracle will determine whether to collect it or not.