Using dbms_stats to Gather Oracle Stats for Schema, Table and Indexes

In Oracle databases, maintaining up-to-date statistics is crucial for optimal query performance. The dbms_stats package is a powerful tool that helps database administrators gather and manage these statistics for schemas, tables, and indexes.

In this guide, we will walk you through the essential steps to effectively use dbms_stats to collect accurate Oracle database statistics, ensuring that your database performs at its best.

Whether you're optimizing a large enterprise database or fine-tuning a smaller environment, this tutorial will provide you with the knowledge you need to manage Oracle stats with confidence.

Gather Schema Stats with GATHER_SCHEMA_STATS

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

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

Using the GATHER_SCHEMA_STATS command

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

Syntax of GATHER_SCHEMA_STATS

Gather Table Stats with GATHER_TABLE_STATS

To gather statistics of individual tables use the following command.

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

Using the GATHER_TABLE_STATS command

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

Syntax of GATHER_TABLE_STATS

Gather Index Stats with GATHER_INDEX_STATS

To gather Indexes statistics use the following command.

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

Using the GATHER_INDEX_STATS command

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

Gather Stats for Database with GATHER_DATABASE_STATS

To gather whole database statistics use the following command.

BEGIN
    DBMS_STATS.GATHER_DATABASE_STATS;
END;
/

Using the GATHER_DATABASE_STATS command

Full Syntax of GATHER_DATABASE_STATS

DBMS_STATS.GATHER_DATABASE_STATS (
   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,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

DBMS_STATS.GATHER_DATABASE_STATS (
   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',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

Syntax of GATHER_DATABASE_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! 🚀