DBMS_STATS - Gather & Manage Oracle Optimizer Statistics

DBMS_STATS

Overview

DBMS_STATS is Oracle’s primary package for collecting, managing, and maintaining the Cost-Based Optimizer (CBO) statistics that drive execution plan selection. Every time Oracle parses a SQL statement, the optimizer uses table row counts, column value distributions, index clustering factors, and system statistics from DBMS_STATS to estimate the cost of competing execution plans. Stale, missing, or locked statistics are among the most common causes of sudden plan regressions in production databases.

Package: DBMS_STATS Available Since: Oracle 8i Required Privileges: ANALYZE ANY (for gathering statistics on other users’ schemas), or object owner for own schema

When to Use DBMS_STATS

• After large bulk data loads or partition operations
• When execution plans degrade unexpectedly
• When tables grow or shrink significantly (>10% row change)
• Before major application releases to establish a known statistics baseline
• When the nightly auto-stats job is too slow or does not cover all tables

---

Key Procedures

GATHER_TABLE_STATS

Gather statistics for a single table, its columns, and optionally its indexes. This is the most commonly used entry point.

DBMS_STATS.GATHER_TABLE_STATS(
    ownname          IN VARCHAR2,
    tabname          IN VARCHAR2,
    partname         IN VARCHAR2  DEFAULT NULL,
    estimate_percent IN NUMBER    DEFAULT DBMS_STATS.AUTO_SAMPLE_SIZE,
    block_sample     IN BOOLEAN   DEFAULT FALSE,
    method_opt       IN VARCHAR2  DEFAULT 'FOR ALL COLUMNS SIZE AUTO',
    degree           IN NUMBER    DEFAULT NULL,
    granularity      IN VARCHAR2  DEFAULT 'AUTO',
    cascade          IN BOOLEAN   DEFAULT DBMS_STATS.AUTO_CASCADE,
    stattab          IN VARCHAR2  DEFAULT NULL,
    statid           IN VARCHAR2  DEFAULT NULL,
    statown          IN VARCHAR2  DEFAULT NULL,
    no_invalidate    IN BOOLEAN   DEFAULT DBMS_STATS.AUTO_INVALIDATE,
    stattype         IN VARCHAR2  DEFAULT 'DATA',
    force            IN BOOLEAN   DEFAULT FALSE
);

Key parameters:

Parameter	Recommended Value	Notes
estimate_percent	DBMS_STATS.AUTO_SAMPLE_SIZE	Oracle auto-determines the optimal sample size; almost always better than a fixed percentage
method_opt	’FOR ALL COLUMNS SIZE AUTO’	Gathers column histograms where beneficial based on column usage; default since 11g
cascade	TRUE	Also gathers index statistics in the same pass
degree	DBMS_STATS.AUTO_DEGREE	Uses the table’s configured degree; set an integer to override parallelism
granularity	’AUTO’	Oracle chooses between GLOBAL, PARTITION, SUBPARTITION, or ALL based on partition type
force	TRUE	Gather even if statistics are locked; use with caution in production

Basic example — gather statistics on a single table:

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'SALES',
        tabname          => 'ORDERS',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => 4,
        no_invalidate    => FALSE
    );
END;
/

Gather only a single partition:

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'SALES',
        tabname          => 'ORDERS',
        partname         => 'ORDERS_Q1_2026',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        granularity      => 'PARTITION',
        cascade          => TRUE,
        no_invalidate    => FALSE
    );
END;
/

---

GATHER_SCHEMA_STATS

Gather statistics for all tables and indexes in a schema in a single call. Useful for post-deployment refreshes or when an entire schema needs updating:

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname          => 'SALES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => DBMS_STATS.AUTO_DEGREE,
        options          => 'GATHER STALE',    -- Only tables with stale stats
        no_invalidate    => FALSE
    );
END;
/

options parameter values:

Value	Behavior
’GATHER’	Gather statistics for all objects regardless of staleness
’GATHER STALE’	Gather only objects flagged as stale by change monitoring
’GATHER EMPTY’	Gather only objects with no existing statistics
’LIST STALE’	Return a list of stale objects without gathering (diagnostic)
‘LIST EMPTY’	Return a list of objects with no statistics without gathering

---

GATHER_DATABASE_STATS

Gather statistics across the entire database. Typically used as a one-time baseline refresh after a major version upgrade:

BEGIN
    DBMS_STATS.GATHER_DATABASE_STATS(
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => DBMS_STATS.AUTO_DEGREE,
        options          => 'GATHER STALE',
        no_invalidate    => FALSE
    );
END;
/

---

Automatic Statistics Gathering

Oracle 10g and later include an automated statistics gathering job that runs during the default maintenance window (nights and weekends). Check its status with:

-- Check whether the auto-stats job is enabled
SELECT client_name, status
FROM   dba_autotask_client
WHERE  client_name = 'auto optimizer stats collection';

-- View recent run history for the auto-stats task
SELECT client_name,
       window_name,
       jobs_created,
       jobs_started,
       jobs_completed,
       window_start_time,
       window_end_time,
       window_duration
FROM   dba_autotask_client_history
WHERE  client_name = 'auto optimizer stats collection'
ORDER  BY window_start_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Disable auto-stats (rarely recommended; prefer preference overrides)
BEGIN
    DBMS_AUTO_TASK_ADMIN.DISABLE(
        client_name => 'auto optimizer stats collection',
        operation   => NULL,
        window_name => NULL
    );
END;
/

---

Setting Preferences

Preferences let you change default DBMS_STATS behavior at the table or schema level without modifying every gather call.

SET_TABLE_PREFS

Override defaults for a specific table:

-- Prevent auto-stats from gathering on a static lookup table
BEGIN
    DBMS_STATS.SET_TABLE_PREFS(
        ownname    => 'SALES',
        tabname    => 'COUNTRY_CODES',
        pname      => 'STALE_PERCENT',
        pval       => '50'             -- Only flag stale if >50% of rows changed
    );
END;
/

-- Force full table scan sampling on a skewed table
BEGIN
    DBMS_STATS.SET_TABLE_PREFS(
        ownname    => 'SALES',
        tabname    => 'ORDER_LINES',
        pname      => 'ESTIMATE_PERCENT',
        pval       => '100'
    );
END;
/

SET_SCHEMA_PREFS

Apply a preference to every table in a schema:

BEGIN
    DBMS_STATS.SET_SCHEMA_PREFS(
        ownname => 'SALES',
        pname   => 'METHOD_OPT',
        pval    => 'FOR ALL COLUMNS SIZE AUTO'
    );
END;
/

Common Preference Names

Preference	Purpose
ESTIMATE_PERCENT	Sampling percentage; DBMS_STATS.AUTO_SAMPLE_SIZE is usually best
METHOD_OPT	Histogram collection strategy
STALE_PERCENT	Row change percentage that triggers a stale flag; default is 10
DEGREE	Parallelism for statistics gathering
CASCADE	Whether to gather index statistics along with table stats
GRANULARITY	Partition-level gathering strategy
NO_INVALIDATE	Whether to invalidate cursors immediately after stats change

---

Detecting Stale Statistics

-- Tables flagged stale by the monitoring subsystem
SELECT s.owner,
       s.table_name,
       s.last_analyzed,
       s.stale_stats,
       m.inserts,
       m.updates,
       m.deletes,
       ROUND((m.inserts + m.updates + m.deletes)
             / NULLIF(s.num_rows, 0) * 100, 2) AS pct_changed
FROM   dba_tab_statistics  s
       JOIN dba_tab_modifications m
           ON  m.table_owner = s.owner
           AND m.table_name  = s.table_name
           AND m.partition_name IS NULL
WHERE  s.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
       AND s.stale_stats = 'YES'
ORDER  BY pct_changed DESC NULLS LAST;

---

Locking and Unlocking Statistics

Lock statistics on a table to prevent any gather operation — including the automatic job — from overwriting them. Essential for stable reference data tables where auto-sampling produces poor histograms:

-- Lock statistics on a table
BEGIN
    DBMS_STATS.LOCK_TABLE_STATS(
        ownname => 'SALES',
        tabname => 'PRODUCT_CATALOGUE'
    );
END;
/

-- Unlock to allow re-gathering
BEGIN
    DBMS_STATS.UNLOCK_TABLE_STATS(
        ownname => 'SALES',
        tabname => 'PRODUCT_CATALOGUE'
    );
END;
/

-- Check which tables have locked statistics
SELECT owner, table_name, stattype_locked
FROM   dba_tab_statistics
WHERE  stattype_locked IS NOT NULL
AND    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
ORDER  BY owner, table_name;

---

Pending Statistics

Pending statistics allow you to gather new statistics and test them against workloads before publishing them globally. This is the safest way to validate a statistics change on a production system.

-- Enable pending mode for a table
BEGIN
    DBMS_STATS.SET_TABLE_PREFS(
        ownname => 'SALES',
        tabname => 'ORDERS',
        pname   => 'PUBLISH',
        pval    => 'FALSE'    -- Stats gathered into pending state, not live
    );
END;
/

-- Gather into pending state (no effect on optimizer globally yet)
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname  => 'SALES',
        tabname  => 'ORDERS',
        no_invalidate => FALSE
    );
END;
/

-- Activate pending stats for this session only to test execution plans
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

-- Examine plan quality, then publish if satisfied
BEGIN
    DBMS_STATS.PUBLISH_PENDING_STATS(
        ownname  => 'SALES',
        tabname  => 'ORDERS',
        no_invalidate => FALSE
    );
END;
/

-- View pending statistics
SELECT owner, table_name, last_analyzed, stattype_locked
FROM   dba_tab_pending_stats
WHERE  owner NOT IN ('SYS', 'SYSTEM')
ORDER  BY owner, table_name;

---

Incremental Statistics for Partitioned Tables

For large partitioned tables, incremental statistics allow Oracle to gather statistics only on changed partitions and then synthesize global statistics from partition-level summaries — dramatically faster than a full-table gather:

-- Enable incremental statistics for a partitioned table
BEGIN
    DBMS_STATS.SET_TABLE_PREFS(
        ownname => 'SALES',
        tabname => 'ORDERS',
        pname   => 'INCREMENTAL',
        pval    => 'TRUE'
    );
END;
/

-- Gather with incremental mode active — only changed partitions are sampled
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname     => 'SALES',
        tabname     => 'ORDERS',
        granularity => 'AUTO',      -- Incremental handles the logic automatically
        cascade     => TRUE,
        no_invalidate => FALSE
    );
END;
/

-- Verify incremental preference
SELECT dbms_stats.get_prefs('INCREMENTAL', 'SALES', 'ORDERS') AS incremental_pref
FROM   dual;

---

Generating a Statistics Report

From Oracle 11g onwards, DBMS_STATS.REPORT_GATHER_STATS_JOB generates a report of what the last auto-stats job collected:

-- Report on the most recent auto-stats job run
SELECT DBMS_STATS.REPORT_GATHER_AUTO_STATS_JOB(
           format    => 'TEXT',
           detail    => 'FULL'
       )
FROM   dual;

---

Common Patterns

Targeted Refresh After Bulk Load

-- Gather stats on the loaded table and all its indexes immediately after load
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'SALES',
        tabname          => 'ORDERS_STAGING',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => 8,
        no_invalidate    => FALSE
    );
END;
/

Copy Statistics Between Environments

-- Export statistics from production to a staging table
BEGIN
    DBMS_STATS.EXPORT_SCHEMA_STATS(
        ownname  => 'SALES',
        stattab  => 'STATS_EXPORT',
        statown  => 'DBA_ADMIN'
    );
END;
/

-- Import statistics in the target database
BEGIN
    DBMS_STATS.IMPORT_SCHEMA_STATS(
        ownname  => 'SALES',
        stattab  => 'STATS_EXPORT',
        statown  => 'DBA_ADMIN'
    );
END;
/

---

Best Practices

1. Always use AUTO_SAMPLE_SIZE — Oracle’s adaptive sampling algorithm produces statistics as accurate as 100% sampling at a fraction of the I/O cost for most tables.
2. Prefer AUTO for method_opt — ‘FOR ALL COLUMNS SIZE AUTO’ creates histograms where the optimizer can actually use them, based on column usage tracking. Hard-coding histogram sizes is rarely beneficial.
3. Use incremental stats for partitioned tables — Setting INCREMENTAL=TRUE reduces gather time from hours to minutes on large partitioned tables with active insert-only partitions.
4. Do not disable the auto-stats job — Instead, lock statistics on volatile tables you manage manually, and let the auto job handle everything else.
5. Test statistics changes with pending mode — On production systems, never publish statistics that could cause plan regressions without first validating them in a session with OPTIMIZER_USE_PENDING_STATISTICS = TRUE.
6. Set STALE_PERCENT for high-churn tables — Reduce the default 10% threshold for tables where even small row changes cause significant plan variability.
7. Export before major changes — Always export current statistics to a staging table before a major gather (DBMS_STATS.EXPORT_TABLE_STATS) so you can restore the previous statistics if a plan regression occurs.

---

Related Resources

• DBA_TABLES — Query LAST_ANALYZED and NUM_ROWS to identify tables needing statistics refreshes
• DBA_INDEXES — Check LAST_ANALYZED on indexes to confirm cascade statistics gather completed
• V$SQL — Identify SQL statements affected by statistics changes by monitoring plan hash value changes after gathering
• DBA_TAB_STATISTICS — Detailed statistics status including STALE_STATS flag and LAST_ANALYZED for every table and partition
• DBA_TAB_MODIFICATIONS — DML change counts since last gather, used to identify the most volatile tables for targeted re-analysis