Oracle 26ai · SQL Tuning · Module 8

Lesson 5

Gathering Optimizer Statistics with DBMS_STATS

Use DBMS_STATS to gather optimizer statistics in modern Oracle. ANALYZE retains other diagnostic uses, such as structure validation and chained-row investigation; it is not the recommended replacement for a current optimizer-statistics strategy. Do not spool broad ANALYZE commands across unrelated schemas.

Inspect before changing anything

Assume an ordinary, nonpartitioned EMPLOYEE table owned by your current training schema and created with unquoted identifiers. The USER_ views below describe that schema. For another owner, use appropriately privileged ALL_ or DBA_ views and explicit owner filters.

SELECT table_name, num_rows, blocks, last_analyzed,
       stale_stats, stattype_locked
FROM user_tab_statistics
WHERE table_name = 'EMPLOYEE'
  AND partition_name IS NULL
  AND subpartition_name IS NULL;

SELECT index_name, num_rows, distinct_keys, leaf_blocks,
       clustering_factor, last_analyzed
FROM user_indexes
WHERE table_name = 'EMPLOYEE';

NUM_ROWS is statistics metadata, not a continuously refreshed COUNT(*). LAST_ANALYZED records collection time. STALE_STATS is an indicator to interpret with workload changes, not proof that every estimate is accurate when it says NO. Locked statistics require understanding the reason for the lock before changing it.

A targeted training example

In the schema that owns the training table, and with permission to collect its statistics, the following block gathers table statistics and lets Oracle decide whether index statistics should also be gathered. Do not run it merely because a plan contains a full scan.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'EMPLOYEE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => DBMS_STATS.AUTO_CASCADE,
    no_invalidate    => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

AUTO_SAMPLE_SIZE delegates sampling choices to Oracle. SIZE AUTO considers distribution and column usage when deciding about histograms. AUTO_CASCADE controls index-statistics collection decisions. AUTO_INVALIDATE lets Oracle determine dependent cursor invalidation timing. Existing preferences, locks, and environment still matter; these settings are not a promise of an unchanged or improved plan.

Fit the operation into maintenance

Automatic collection normally operates through maintenance windows and prioritizes objects needing attention. Important bulk changes may justify a coordinated collection outside that schedule. Review object scope, resource impact, and the application's critical workload before making that decision.

Do not assume a 50-row sample represents a large skewed table. Partitioned tables introduce local and global statistics considerations and may require a different strategy. The single-table example does not prescribe settings for an entire database.

Verify collection and behavior

  1. Rerun the metadata queries and inspect timestamps and relevant values.
  2. Check for collection errors and unexpected locks or preference effects.
  3. Compare representative executions with the recorded baseline.
  4. Investigate significant estimate changes and regressions before expanding the change.

Statistics history and export/import facilities can support recovery planning. Select and test an appropriate restoration approach before a consequential production change; this page does not execute one. A successful collection call confirms an operation completed, not that every application query became faster.

Oracle references

Optional practice: exercise