How to Gather Oracle Statistics with DBMS_STATS

How to Gather Oracle Statistics with DBMS_STATS

The Oracle Cost-Based Optimizer (CBO) uses statistics — row counts, data distribution, column histograms — to select the most efficient execution plan. Stale or missing statistics are one of the most common causes of sudden query performance degradation. This guide covers gathering statistics at every level using DBMS_STATS.

Prerequisites

To gather statistics on your own objects, no special privilege is needed. To gather statistics on objects owned by other users, you need the ANALYZE ANY system privilege, or the DBA role.

-- Check current statistics freshness
SELECT owner, table_name, num_rows, last_analyzed,
       stale_stats
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND (last_analyzed IS NULL OR stale_stats = 'YES')
ORDER BY table_name;

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

Step 1: Gather Table Statistics

-- Gather statistics for a single table (using automatic sampling)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,    -- Also gather index statistics
  degree => 4         -- Parallel degree
);

-- With explicit 20% sample rate (for very large tables where AUTO is slow)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDER_HISTORY',
  estimate_percent => 20,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 8,
  no_invalidate => FALSE  -- Immediately invalidate cursors using this table
);

Step 2: Gather Schema Statistics

-- Gather all statistics for a schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'APP_SCHEMA',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4,
  options => 'GATHER'  -- Gather all objects regardless of staleness
);

-- Gather only stale statistics (more efficient for maintenance windows)
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'APP_SCHEMA',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4,
  options => 'GATHER STALE'  -- Only gather where stale or missing
);

Step 3: Gather Database-Wide Statistics

-- Gather statistics for the entire database (run during low-activity window)
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 8,
  options => 'GATHER STALE'
);

Step 4: Gather Index Statistics

-- Gather statistics for a specific index
EXEC DBMS_STATS.GATHER_INDEX_STATS(
  ownname => 'APP_SCHEMA',
  indname => 'ORDERS_CUSTOMER_ID_IDX',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  degree => 4
);

Step 5: Gather System Statistics

System statistics describe the I/O and CPU capabilities of the hardware. Accurate system statistics improve plan quality, particularly for large queries.

-- Gather system statistics during a representative workload window
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(
  gathering_mode => 'INTERVAL',
  interval => 60,       -- Gather for 60 minutes
  stattab => NULL,      -- Store in the data dictionary
  statid  => NULL
);

-- Check current system statistics
SELECT pname, pval1, pval2
FROM sys.aux_stats$;

Step 6: Configure Histograms

Histograms describe the distribution of values in a column — critical for skewed data (e.g., a status column where 95% of rows are ‘COMPLETED’).

-- Gather with frequency histograms for specific columns
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  method_opt => 'FOR ALL COLUMNS SIZE AUTO ' ||
                'FOR COLUMNS STATUS SIZE 254 ' ||     -- Frequency histogram
                'FOR COLUMNS ORDER_DATE SIZE 100',    -- Height-balanced
  cascade => TRUE,
  degree => 4
);

-- Force no histograms (useful when histograms cause plan instability)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  method_opt => 'FOR ALL COLUMNS SIZE 1',   -- SIZE 1 = no histogram
  cascade => TRUE
);

Advanced Examples

Lock and Unlock Statistics

Lock statistics to prevent the automatic stats job from overwriting carefully tuned statistics.

-- Lock statistics for a table (auto-stats job will skip it)
EXEC DBMS_STATS.LOCK_TABLE_STATS('APP_SCHEMA', 'ORDERS');

-- Lock all statistics in a schema
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('APP_SCHEMA');

-- Unlock when you want to refresh
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('APP_SCHEMA', 'ORDERS');

-- Check locked statistics
SELECT owner, table_name, stattype_locked
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
  AND owner = 'APP_SCHEMA';

Save and Restore Statistics

Before a major statistics gather, save the current statistics so you can restore them if plans regress.

-- Create a staging table to save statistics
EXEC DBMS_STATS.CREATE_STAT_TABLE(
  ownname => 'APP_SCHEMA',
  stattab => 'SAVED_STATS'
);

-- Export current statistics to the staging table
EXEC DBMS_STATS.EXPORT_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  stattab => 'SAVED_STATS',
  statid  => 'BEFORE_REFRESH'
);

-- If plans get worse, restore the saved statistics
EXEC DBMS_STATS.IMPORT_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  stattab => 'SAVED_STATS',
  statid  => 'BEFORE_REFRESH'
);

Set Manual Statistics (for testing or workarounds)

-- Set specific statistics values manually (overrides gathered stats)
EXEC DBMS_STATS.SET_TABLE_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  numrows => 5000000,
  numblks => 625000,
  avgrlen => 100
);

-- Set column statistics (for testing plan behavior)
EXEC DBMS_STATS.SET_COLUMN_STATS(
  ownname => 'APP_SCHEMA',
  tabname => 'ORDERS',
  colname => 'STATUS',
  distcnt => 6,         -- 6 distinct values
  nullcnt => 0
);

Gather Statistics After Data Load

-- After a bulk data load, gather fresh statistics immediately
BEGIN
  -- Gather table stats
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'APP_SCHEMA',
    tabname          => 'DAILY_TRANSACTIONS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => 8,
    no_invalidate    => FALSE
  );
  DBMS_OUTPUT.PUT_LINE('Statistics gathered successfully.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Common Mistakes and Pitfalls

Not gathering stats after large data loads — The auto-stats job runs overnight. If you load millions of rows at 9 PM, queries between load completion and the nightly job will use stale statistics.

Using a fixed sample percentage on skewed data — ESTIMATE_PERCENT => 5 misses data distribution for skewed columns. Use AUTO_SAMPLE_SIZE — Oracle’s adaptive sampling is usually superior.

Gathering stats during peak hours — Parallel statistics gathering uses significant I/O and CPU. Schedule statistics collection during off-peak windows.

Over-locking statistics — Locking statistics prevents the auto-stats job from keeping them fresh. Only lock statistics for specific, carefully tuned objects.

Not using CASCADE=TRUE — Without CASCADE, index statistics are not gathered along with table statistics. Stale index statistics can cause the optimizer to miss index usage.

Gathering stats on a temp table with permanent scope — Global temporary tables have session- or transaction-specific data. Use DBMS_STATS.GATHER_TABLE_STATS with NO_INVALIDATE=FALSE and be aware that stats apply to all sessions.

Verification Queries

-- Confirm statistics were gathered successfully
SELECT table_name, num_rows, avg_row_len, blocks,
       last_analyzed, stale_stats, stattype_locked
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'ORDERS';

-- Check column statistics and histograms
SELECT column_name, num_distinct, num_nulls,
       histogram, num_buckets, last_analyzed
FROM dba_col_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'ORDERS'
ORDER BY column_name;

-- Check index statistics
SELECT index_name, num_rows, leaf_blocks, distinct_keys,
       blevel, last_analyzed
FROM dba_ind_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'ORDERS'
ORDER BY index_name;

-- Find tables with no statistics or very old statistics
SELECT table_name, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND (last_analyzed IS NULL
       OR last_analyzed < SYSDATE - 7
       OR stale_stats = 'YES')
ORDER BY last_analyzed NULLS FIRST;

Related Topics

• How to Create an Index - Gather index stats after creation
• How to Import a Schema - Gather stats after Data Pump import
• Performance Analysis Scripts - Statistics monitoring scripts
• Oracle AWR Overview - Identify plan regressions using AWR