How to Gather Optimizer Statistics on 9i (Doc ID 388474.1)

In this Document
  Goal
  Solution
  References

---

APPLIES TO:

Oracle Server - Enterprise Edition - Version: 9.0.1.4 to 9.2.0.8 - Release: 9.0.1 to 9.2
Information in this document applies to any platform.

GOAL

This Document outline the recommended method to gather a standard set of optimizer statistics for use by the Cost Based Optimizer under Oracle 9i.  For other versions see:

Note:1226841.1 How To: Gather Statistics for the Cost Based Optimizer

SOLUTION

IMPORTANT: PLEASE NOTE:

These recommendations apply to the majority of databases.
The recommendations aim for statistics accuracy  so full samples are suggested.

For very large systems, the gathering of statistics can be a very time consuming and resource intensive activity.
In this environment sample sizes need to be carefully controlled to ensure that gathering completes within acceptable timescales and resource constraints.
For guidance on this topic See: 

Note:237901.1 Gathering Schema or Database Statistics Automatically - Examples

In these environments, it is also recommended to utilise change based statistics gathering to avoid re-gathering information unecessarily.
Please see: 

Note:44961.1 Statistics Gathering: Frequency and Strategy Guidelines

Gathering statistics an individual table

 
 

  
  exec dbms_stats.gather_table_stats(  -
       ownname => NULL, -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
 
 


N.B. replace '  Table_name  ' with the name of the table to gather statistics for.

Gathering statistics for all objects in a schema

 
 

  
  exec dbms_stats.gather_schema_stats( -
       ownname => NULL, -
       cascade => TRUE, -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
 
 

Gathering statistics for all objects in the database:

 
 

  
  exec dbms_stats.gather_database_stats( -
       cascade => TRUE, -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
 
 

REFERENCES

NOTE:114671.1  - Gathering Statistics for the Cost Based Optimizer (Pre 10g)
NOTE:1226841.1  - How To: Gather Statistics for the Cost Based Optimizer
NOTE:237901.1  - Gathering Schema or Database Statistics Automatically in 8i and 9i - Examples
NOTE:605439.1  - * How to Gather Optimizer Statistics on 10g
NOTE:749227.1  - * How to Gather Optimizer Statistics on 11g