Gather Oracle Stats for Schema, Table and Indexes

In the world of Oracle Database Management, gathering statistics plays a crucial role in optimizing query performance. Accurate statistics help the query optimizer make informed decisions about execution plans.

In this introduction let's explore how to gather statistics for Schemas , Tables , and Indexes using Oracle simple commands.

Table of Contents

• Gather Schema Stats
• Gather Table Stats
• Gather Index Stats

Gather Schema Stats with GATHER_SCHEMA_STATS

To collect statistics for an entire schema, use the following command.

-- GATHER STATS FROM SCHEMA USING DEFAULT OPTIONS
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME'); 

Considerations

• For partitioned tables, use CASCADE=>TRUE to gather statistics for all partitions

Full Syntax of GATHER_SCHEMA_STATS

DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname VARCHAR2, 
   estimate_percent NUMBER DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample BOOLEAN DEFAULT FALSE, 
   method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab VARCHAR2 DEFAULT NULL, 
   statid VARCHAR2 DEFAULT NULL, 
   options VARCHAR2 DEFAULT 'GATHER', 
   objlist OUT ObjectTab,
   statown VARCHAR2 DEFAULT NULL, 
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force BOOLEAN DEFAULT FALSE,
  obj_filter_list ObjectTab DEFAULT NULL);

Gather Table Stats with GATHER_TABLE_STATS

To gather statistics of individual tables use the following command.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME'
);

Considerations

• Small tables: Use a larger sample size
• Large tables: Consider parallelism and incremental statistics

Full Syntax of GATHER_TABLE_STATS

DBMS_STATS.GATHER_TABLE_STATS (
   ownname VARCHAR2, 
   tabname VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER DEFAULT to_estimate_percent_type 
                                                  (get_param('ESTIMATE_PERCENT')),
   block_sample BOOLEAN DEFAULT FALSE,
   method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
   granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab VARCHAR2 DEFAULT NULL, 
   statid VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
                                                      get_param('NO_INVALIDATE')),
   stattype VARCHAR2 DEFAULT 'DATA',
   force BOOLEAN DEFAULT FALSE,
   context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
   options VARCHAR2 DEFAULT get_param('OPTIONS'));

Gather Index Stats with GATHER_INDEX_STATS

To gather Indexes statistics use the following command.

EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SCHEMA_NAME',
    indname => 'INDEX_NAME'
);

Considerations

• Unique indexes: Gather statistics for uniqueness checks
• Bitmap indexes: Handle them differently due to their nature

Full Syntax of GATHER_INDEX_STATS

DBMS_STATS.GATHER_INDEX_STATS (
   ownname VARCHAR2, 
   indname VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER DEFAULT to_estimate_percent_type 
                                                (GET_PARAM('ESTIMATE_PERCENT')),
   stattab VARCHAR2 DEFAULT NULL, 
   statid VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL,
   degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
   granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type 
                                               (GET_PARAM('NO_INVALIDATE')),
   force BOOLEAN DEFAULT FALSE);

Syntax of GATHER_INDEX_STATS

Conclusion

Gathering statistics is not just a routine task—it’s a critical part of maintaining a healthy database. By following best practices and understanding the nuances, you’ll ensure optimal query performance.

For further details check the official documentation.

Remember to apply these techniques in your database environment, and happy querying! 🚀