Oracle Database: Deleting Statistics with DBMS_STATS Package

The Oracle Database DBMS_STATS package provides powerful procedures for managing database statistics. This post explores the delete_stats procedures, which are crucial for maintaining accurate query optimization.

Understanding DBMS_STATS.DELETE_STATS Procedures

The DBMS_STATS package offers several procedures to delete statistics at different levels:

1. Database-wide: delete_database_stats
2. Schema-level: delete_schema_stats
3. Table-level: delete_table_stats
4. Index-level: delete_index_stats

Let's break down each procedure and its purpose:

Database-Wide Statistics Deletion

Sample SQL Command

1exec dbms_stats.delete_database_stats;

This procedure deletes statistics for all objects in the database. It's useful when you want to reset all statistics, perhaps after a major data load or system change.

Schema-Level Statistics Deletion

1exec dbms_stats.delete_schema_stats('SCOTT'); 

This command removes statistics for all objects within the specified schema (in this case, 'SCOTT'). It's helpful when you want to refresh statistics for an entire schema without affecting others.

Table-Level Statistics Deletion

1exec dbms_stats.delete_table_stats('SCOTT', 'EMPLOYEES');

This procedure deletes statistics for a specific table ('EMPLOYEES') in the given schema ('SCOTT'). Use this when you need to refresh statistics for a particular table, perhaps after significant data modifications.

Index-Level Statistics Deletion

1exec dbms_stats.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

This command removes statistics for a specific index ('EMPLOYEES_PK') in the given schema ('SCOTT'). It's useful when you want to refresh statistics for a particular index without affecting other objects.

Key Points and Insights

1. Selective Deletion: These procedures allow for targeted deletion of statistics, from the entire database down to a single index.
2. Performance Impact: Deleting statistics can temporarily affect query performance until new statistics are gathered.
3. Automatic Stats Collection: After deleting statistics, Oracle's automatic stats collection job will typically gather new statistics during the next maintenance window.
4. Manual Regathering: For immediate effect, you may need to manually regather statistics using the corresponding DBMS_STATS.GATHER procedures.
5. Privileges Required: Executing these procedures requires specific privileges, typically granted to DBA roles.

When to Use DELETE_STATS

• After large data loads or modifications
• When troubleshooting query performance issues
• Before gathering fresh statistics
• When preparing for system testing or benchmarking

By understanding and properly utilizing these DBMS_STATS.DELETE_STATS procedures, database administrators can ensure that the Oracle query optimizer has access to accurate and up-to-date statistics, leading to optimal query execution plans and improved database performance.