Gathering Statistics in Oracle – Detailed Guide with Examples

1. What are Oracle Statistics?

Oracle gathers statistics to help the optimizer choose the best execution plans for queries.

These statistics provide insights into:

Table size and row distribution
Index efficiency and clustering factor
Column uniqueness and histograms
System performance (CPU, I/O, memory usage)

2. Types of Statistics in Oracle (with Parameters & Examples)

Oracle provides different types of statistics that can be gathered for optimized query execution.

Type of Statistics	Description
Table Statistics	Number of rows, blocks, average row length
Column Statistics	Number of distinct values, histograms, null values
Index Statistics	B-tree levels, leaf blocks, clustering factor
System Statistics	CPU speed, I/O performance (used for CBO)
Fixed Object Statistics	Statistics for dynamic performance views (V$ views)
Dictionary Statistics	Statistics for internal data dictionary objects

A. Table Statistics

Collects row count, blocks, and average row length.

Example 1: Gather table statistics (default parameters)


EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

Default ESTIMATE_PERCENT is DBA-defined, usually 100%.
Default METHOD_OPT is AUTO, meaning histograms may be gathered.

Example 2: Gather table stats with specific parameters


EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR', 
    tabname => 'EMPLOYEES', 
    estimate_percent => 50, 
    cascade => TRUE, 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

📌 Parameters explained:

estimate_percent => 50 → Uses 50% sample size instead of full scan.
cascade => TRUE → Gathers both table and index statistics.
method_opt => 'FOR ALL COLUMNS SIZE AUTO' → Automatically generates histograms for skewed columns.

B. Schema Statistics

Collects statistics for all tables, indexes, and columns in a specific schema.

Example: Gather schema-level statistics


EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

Gathers statistics for all tables, indexes, and partitions in the HR schema.
Uses AUTO_SAMPLE_SIZE for accuracy.

Example: Gather schema statistics with more control


EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'HR', 
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
    cascade => TRUE, 
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254'
);

📌 Parameters explained:

AUTO_SAMPLE_SIZE → Oracle automatically determines the sample size for optimal accuracy.
FOR ALL INDEXED COLUMNS SIZE 254 → Collects histograms for indexed columns only.

C. Index Statistics

Helps optimizer determine the efficiency of index-based access.

Example: Gather index statistics


EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMPLOYEES_IDX');

Collects B-tree levels, leaf blocks, and clustering factor.

Example: Gather index statistics with full scan


EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'HR', 
    indname => 'EMPLOYEES_IDX', 
    estimate_percent => 100
);

📌 estimate_percent => 100 → Uses a full scan of the index.

D. System Statistics

Used for CPU and I/O performance tuning in the cost-based optimizer.

Example: Gather system statistics


EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

Oracle collects CPU speed and I/O performance metrics.

Example: Collect system stats for specific workload


EXEC DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'NOWORKLOAD');

📌 NOWORKLOAD → Collects only basic CPU speed statistics, used for quick analysis.


EXEC DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'INTERVAL', 
interval => 60);

📌 INTERVAL => 60 → Collects statistics for 60 minutes while workload is running.

E. Fixed Object Statistics

Used for optimizing V$ views and dynamic performance tables.

Example: Gather fixed object statistics


EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Useful after database startup to improve performance of queries using V$ views.

F. Dictionary Statistics

Collects statistics on internal Oracle dictionary tables.

Example: Gather dictionary statistics


EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Improves performance of queries that use dictionary views like DBA_TABLES, DBA_INDEXES.

G. Gather Database-Wide Statistics

Gathers statistics for entire database.Should be run during low-traffic hours.

Example: Gather Database statistics

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

3. Managing Stale Statistics

Oracle detects stale statistics when 10% or more rows in a table are modified.

Check Stale Tables


SELECT table_name FROM dba_tab_statistics WHERE stale_stats = 'YES';

Gather Stale Statistics Only


EXEC DBMS_STATS.GATHER_DATABASE_STATS(options => 'GATHER STALE');

4. Managing Histograms

Histograms help optimize queries on skewed data distributions.

Create histograms for a specific column


EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR', 
    tabname => 'EMPLOYEES', 
    method_opt => 'FOR COLUMNS SALARY SIZE 254'
);

📌 SIZE 254 → Creates up to 254 histogram buckets.

Delete histograms if not needed


EXEC DBMS_STATS.DELETE_COLUMN_STATS('HR', 'EMPLOYEES', 'SALARY');

5. Restoring Old Statistics

If new statistics cause performance issues, restore old statistics.

Backup Statistics Before Gathering New Ones


EXEC DBMS_STATS.CREATE_STAT_TABLE('SYSTEM', 'STATS_BACKUP');
EXEC DBMS_STATS.EXPORT_TABLE_STATS('HR', 'EMPLOYEES', 'SYSTEM', 
'STATS_BACKUP');

Restore Old Statistics


EXEC DBMS_STATS.IMPORT_TABLE_STATS('HR', 'EMPLOYEES', 'SYSTEM', 
'STATS_BACKUP');

6. Auto Statistics Gathering in Oracle

Oracle gathers statistics automatically using DBMS_AUTO_TASK_ADMIN.

Check if Auto Stats Collection is Enabled


SELECT client_name, status FROM dba_autotask_client;

Enable Automatic Stats Gathering


EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection');

When to Gather Statistics?

1. After Large Data Changes

If more than 10-15% of data has been modified, inserted, or deleted.
Example: After a bulk insert, update, or delete operation.

Command:


EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

2. After Creating or Dropping Indexes

If new indexes are created or old indexes are dropped.
Index statistics help the optimizer in efficient query execution.

Command:


EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMPLOYEES_IDX');

3. Regularly for Performance Maintenance

Run automatically in Oracle’s default maintenance window (10g+).

Oracle automatically gathers statistics overnight on stale objects.

4. Before Upgrading the Database

Gather statistics before and after an upgrade for query plan stability.

Command:


EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

5. After Database Restart (Fixed Objects Statistics)

System performance statistics are reset on restart.
Gather fixed object statistics for dynamic performance views.

Command:


EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Best Practices for Gathering Statistics

✔ Use incremental statistics for large partitioned tables to reduce workload.
✔ Avoid gathering statistics during peak hours to prevent performance issues.
✔ Keep a backup of old statistics before gathering new ones.
✔ Check for stale statistics regularly to ensure optimizer accuracy.

Gathering statistics is essential for SQL performance tuning in Oracle. The DBMS_STATS package provides full control over when and how statistics are gathered. Regular maintenance and automatic collection help optimize execution plans for queries.

🚀 Optimized statistics = Faster Queries = Better Performance!

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Gathering Statistics in Oracle – Detailed Guide with Examples