Oracle DBMS_STATS: Delete Database Statistics

calendar Jan 16, 2025 / · 3 min read · Oracle Database Performance Tuning SQL Database Administration Statistics Management DBMS_STATS  ·
Share on: linkedin copy

Oracle Database: Deleting Statistics with DBMS_STATS Package

Deleting optimizer statistics leaves a table with none, and the optimizer then falls back to dynamic statistics: it samples a few table blocks at parse time. The DBMS_STATS package has one delete procedure for each level, from the whole database down to a single index. The commands below show each one and what it removes.

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

Oracle's reference says these DELETE_* procedures "delete both user-defined statistics and the standard statistics for the given schema object." Here is each procedure and its purpose.

Database-Wide Statistics Deletion

Sample SQL Command

1exec dbms_stats.delete_database_stats;

This procedure removes statistics for every table in the database. Until statistics are gathered again, the optimizer uses dynamic statistics. It is rarely the right choice on a production system.

Schema-Level Statistics Deletion

1exec dbms_stats.delete_schema_stats('SCOTT');

This command removes statistics for all objects in the specified schema (here, 'SCOTT') and leaves other schemas alone.

Table-Level Statistics Deletion

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

This procedure removes statistics for one table ('EMPLOYEES') in the given schema ('SCOTT'). Oracle's SQL Tuning Guide uses this call for volatile tables: with the statistics gone, the database gathers what it needs during optimization with dynamic statistics.

Index-Level Statistics Deletion

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

This command removes statistics for one index ('EMPLOYEES_PK') in the given schema ('SCOTT') and does not touch other objects.

Key Points and Insights

  1. Selective Deletion: These procedures let you target 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, because the optimizer may choose different plans.
  3. Automatic Stats Collection: Automatic optimizer statistics collection runs in the maintenance windows by default. It gathers statistics for objects that have none, so it can refill what you deleted, but only when the window runs.
  4. Manual Regathering: For immediate effect, gather statistics yourself with the matching DBMS_STATS.GATHER procedures.
  5. Privileges and Locks: Your privileges decide which objects you can change (see the DBMS_STATS reference). Statistics that are locked are protected from changes unless the call uses the force parameter.
  6. History: Oracle saves old versions of statistics whenever the dictionary statistics change, so you can restore them.

When to Use DELETE_STATS

  • To clear bad or unrepresentative statistics on one object
  • When troubleshooting query performance issues
  • To make a volatile table use dynamic statistics instead of stored ones
  • When preparing for system testing or benchmarking

After a large data load or change, gather instead of deleting. GATHER_TABLE_STATS replaces the existing statistics, so you do not need to delete them first. The GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS procedures, with the GATHER STALE or GATHER AUTO option, also pick up tables whose statistics are stale or missing.

Used together with the DBMS_STATS.GATHER procedures, the delete procedures let a DBA clear bad statistics so the optimizer can work from freshly gathered ones.

References

Posts in this series