Mastering Oracle Statistics Collection: When, How, and What to Gather

1. Verify Automatic Statistics Collection

Check whether the automatic optimizer statistics collection task is enabled:

SELECT OPERATION_NAME, STATUS FROM DBA_AUTOTASK_OPERATION WHERE CLIENT_NAME LIKE '%stats%';

If the STATUS column shows ENABLED, the task is active. To enable it when disabled, run:

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

2. Inspect and Adjust Scheduler Windows

View existing scheduler windows: SELECT * FROM DBA_SCHEDULER_WINDOWS; Inspect recent job runs for the automatic statistics job (jobs start with ORA$AT_OS_OPT):

SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE job_name LIKE '%ORA$AT_OS_OPT%' ORDER BY LOG_DATE DESC;

If jobs stop because they exceed the window duration, increase parallelism: EXEC DBMS_STATS.SET_PARAM('DEGREE', 4); Or extend the window duration (example for a Tuesday window):

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'TUESDAY_WINDOW', attribute => 'duration', value => NUMTODSINTERVAL(480, 'minute'));
END;
/

When another window preempts the statistics job, raise the priority of the statistics window:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'TUESDAY_WINDOW', attribute => 'window_priority', value => 'HIGH');
END;
/

To modify the window schedule, disable it, change the repeat interval, and re‑enable:

BEGIN
  DBMS_SCHEDULER.DISABLE(name => 'TUESDAY_WINDOW');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=03;byminute=0;bysecond=0');
  DBMS_SCHEDULER.ENABLE(name => 'TUESDAY_WINDOW');
END;
/

3. Table‑Level Statistics Preferences

List current table‑level preferences: SELECT * FROM DBA_TAB_STAT_PREFS; Set a table to be considered always stale (force collection each run):

EXEC DBMS_STATS.SET_TABLE_PREFS('SCHEMA_NAME', 'TABLE_NAME', 'STALE_PERCENT', 100);

Gather statistics with custom sampling and parallelism:

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

4. Custom Collection for Large Tables

For very large tables (e.g., 800 GB), the default collection can take many hours. Adjust sampling rate and parallelism as shown above, or use a script that determines sampling based on table size:

DECLARE
  CURSOR stale_table IS
    SELECT owner, segment_name,
      CASE
        WHEN size_gb < 0.5 THEN 30
        WHEN size_gb >= 0.5 AND size_gb < 1 THEN 20
        WHEN size_gb >= 1 AND size_gb < 5 THEN 10
        WHEN size_gb >= 5 AND size_gb < 10 THEN 5
        WHEN size_gb >= 10 THEN 1
      END AS percent,
      8 AS degree
    FROM (
      SELECT owner, segment_name,
        SUM(bytes/1024/1024/1024) size_gb
      FROM dba_segments
      WHERE owner = '' AND segment_name IN (
        SELECT DISTINCT table_name FROM dba_tab_statistics
        WHERE (last_analyzed IS NULL OR stale_stats = 'YES') AND owner = ''
      )
      GROUP BY owner, segment_name);
BEGIN
  FOR stale IN stale_table LOOP
    DBMS_STATS.GATHER_TABLE_STATS(
      ownname => stale.owner,
      tabname => stale.segment_name,
      estimate_percent => stale.percent,
      method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
      degree => stale.degree,
      granularity => 'ALL',
      cascade => TRUE);
  END LOOP;
END;
/

5. Incremental Statistics for Partitioned Tables

Enable incremental collection on a partitioned table:

EXEC DBMS_STATS.SET_TABLE_PREFS('SCHEMA_NAME', 'TABLE_NAME', 'INCREMENTAL', 'TRUE');

Incremental stats are useful when a log table records frequent DML on source tables, allowing fast refresh of partition statistics.

6. Detecting Stale Statistics

A table is considered stale when STALE_STATS = 'YES' or LAST_ANALYZED IS NULL:

SELECT owner, table_name, object_type, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE owner = 'SCHEMA_NAME' AND table_name IN ('TABLE_NAME');

Oracle also triggers collection when a table’s data change exceeds 10 % of its rows. You can fine‑tune this threshold:

EXEC DBMS_STATS.SET_TABLE_PREFS('SCHEMA_NAME', 'TABLE_NAME', 'STALE_PERCENT', 1);

When the threshold is set to 1 %, statistics will be gathered automatically after a 1 % change.

7. When to Gather Histograms

Oracle decides to collect histograms based on column data distribution and workload. A column must be used in equality predicates and exhibit skewed data. The database distinguishes two collection methods:

HEIGHT BALANCED – used when distinct value count > 254 and data is skewed.

FREQUENCY – used when distinct value count ≤ 254.

You can also specify the number of buckets manually.

Choosing collection methods: AUTO – suitable for a newly deployed system. REPEAT – for a stable production environment. SKEWONLY – when you need custom collection for highly skewed columns.

These guidelines help DBA teams ensure optimal optimizer statistics, improve execution plans, and avoid performance regressions.