DBMS_STATS, Histograms, Column Groups, Incremental Stats, and Stale Statistics

Learning outcomes

A ServiceHub plan is accurate for the common CLOSED status but badly wrong for rare ESCALATED rows. Another query assumes country_code and region_code are independent even though each country maps to a narrow region set. The optimizer estimates from the statistics available to it, so statistics must model useful distribution and correlation rather than simply exist.

1. DBMS_STATS maintains the optimizer's model of the data

Table statistics include row and block counts. Column statistics include distinct values, null counts, density, low/high values, and optional histograms. Index statistics include leaf blocks, distinct keys, clustering factor, and more. The automatic statistics framework can gather missing or stale statistics; manual gathers are useful after controlled data loads or when deployment timing requires immediate evidence.

SELECT table_name,num_rows,blocks,sample_size,last_analyzed,stale_statsFROM user_tab_statistics WHERE table_name='SERVICEHUB_STATS_CASE';SELECT column_name,num_distinct,num_nulls,density,histogram,num_bucketsFROM user_tab_col_statistics WHERE table_name='SERVICEHUB_STATS_CASE' ORDER BY column_id;SELECT index_name,num_rows,distinct_keys,leaf_blocks,clustering_factor,last_analyzedFROM user_indexes WHERE table_name='SERVICEHUB_STATS_CASE';

2. Histograms model skew

Without a useful histogram, equality estimation often begins from a uniform-style assumption. If 97% of rows are CLOSED and only 0.5% are ESCALATED, that assumption can be poor. Oracle supports histogram types including frequency, top-frequency, hybrid, and legacy height-balanced metadata. METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' allows Oracle to choose histograms using distribution and observed column usage.

BEGIN DBMS_STATS.GATHER_TABLE_STATS(   ownname=>USER, tabname=>'SERVICEHUB_STATS_CASE', cascade=>TRUE,   method_opt=>'FOR ALL COLUMNS SIZE AUTO');END;/SELECT column_name,histogram,num_buckets,num_distinctFROM user_tab_col_statisticsWHERE table_name='SERVICEHUB_STATS_CASE' ORDER BY column_id;

3. Column groups model correlation

If country_code='AZ' strongly predicts region_code='BAKU', multiplying separate selectivities can misestimate the combined predicate. Extended statistics on a column group give Oracle joint-distribution evidence. Create only groups that represent stable relationships used by real predicates; the number of possible combinations grows rapidly.

DECLARE n VARCHAR2(128);BEGIN n:=DBMS_STATS.CREATE_EXTENDED_STATS(USER,'SERVICEHUB_STATS_CASE','(COUNTRY_CODE,REGION_CODE)'); DBMS_OUTPUT.PUT_LINE(n); DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_STATS_CASE',cascade=>TRUE);END;/SELECT extension_name,extension FROM user_stat_extensionsWHERE table_name='SERVICEHUB_STATS_CASE';

4. Stale statistics depend on modification tracking and preferences

STALE_PERCENT is an optimizer-statistics preference controlling the DML-change threshold used for stale decisions. Preferences can be set at different scopes, so inspect the effective value rather than assuming a global default. Modification monitoring is useful operational evidence but is not a transactional real-time counter.

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'SERVICEHUB_STATS_CASE') AS stale_percent FROM dual;SELECT table_name,inserts,updates,deletes,timestampFROM user_tab_modifications WHERE table_name='SERVICEHUB_STATS_CASE';SELECT table_name,stale_stats,last_analyzedFROM user_tab_statistics WHERE table_name='SERVICEHUB_STATS_CASE';

5. Incremental statistics solve a partitioned-table problem

For large partitioned tables, the INCREMENTAL preference enables partition synopses so global statistics can be maintained from changed partitions rather than scanning the whole table. INCREMENTAL_STALENESS influences stale decisions. It has no benefit on a nonpartitioned table. This chapter keeps the mandatory lab nonpartitioned and treats partition incremental statistics as architecture, because production partitioning must be checked against the deployed offering and workload.

SELECT DBMS_STATS.GET_PREFS('INCREMENTAL',USER,'SERVICEHUB_STATS_CASE') AS incremental_pref,       DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'SERVICEHUB_STATS_CASE') AS incremental_stalenessFROM dual;

6. Wrong approach: identical nightly gather recipes for every table

A fixed sample percentage, forced histogram recipe, immediate invalidation, and full-schema nightly gather can waste resources and still miss correlated/skewed predicates. Current DBMS_STATS supports AUTO sampling, AUTO histograms, rolling invalidation, preferences, pending statistics, extended statistics, and incremental partition synopses. Choose the mechanism that addresses the measured problem.

7. Hands-on lab: skew plus correlation

DROP TABLE servicehub_stats_case IF EXISTS PURGE;CREATE TABLE servicehub_stats_case( case_id NUMBER PRIMARY KEY,status_code VARCHAR2(12) NOT NULL,country_code VARCHAR2(4) NOT NULL, region_code VARCHAR2(12) NOT NULL,amount NUMBER(10,2) NOT NULL);INSERT INTO servicehub_stats_caseSELECT LEVEL,CASE WHEN LEVEL<=9700 THEN 'CLOSED' WHEN LEVEL<=9950 THEN 'OPEN' ELSE 'ESCALATED' END,       CASE WHEN MOD(LEVEL,5)=0 THEN 'TR' ELSE 'AZ' END,       CASE WHEN MOD(LEVEL,5)=0 THEN 'IST' WHEN MOD(LEVEL,20)=0 THEN 'GANJA' ELSE 'BAKU' END,       MOD(LEVEL*31,10000)/10FROM dual CONNECT BY LEVEL<=10000;CREATE INDEX sh09_stats_status_ix ON servicehub_stats_case(status_code);BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_STATS_CASE',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO'); END;/SELECT column_name,num_distinct,histogram,num_bucketsFROM user_tab_col_statistics WHERE table_name='SERVICEHUB_STATS_CASE' ORDER BY column_id;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER,'SERVICEHUB_STATS_CASE','(COUNTRY_CODE,REGION_CODE)') AS ext FROM dual;BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_STATS_CASE',cascade=>TRUE); END;/SELECT extension_name,extension FROM user_stat_extensions WHERE table_name='SERVICEHUB_STATS_CASE';BEGIN FOR r IN (SELECT extension_name FROM user_stat_extensions WHERE table_name='SERVICEHUB_STATS_CASE') LOOP   DBMS_STATS.DROP_EXTENDED_STATS(USER,'SERVICEHUB_STATS_CASE',r.extension_name); END LOOP;END;/DROP TABLE servicehub_stats_case PURGE;

8. Production judgment

Gather statistics when evidence needs refreshing, not by superstition. Histograms model relevant skew; column groups model correlation; preference scope matters; incremental statistics are a partitioned-table mechanism. No management pack is required for DBMS_STATS itself.

Lesson 3 compares estimates to the child cursor that actually ran so that statistics defects become visible as E-Rows versus A-Rows divergence.

Authoritative references

• DBMS_STATS — gathering, histograms, extended statistics and preferences
• Managing Optimizer Statistics — automatic statistics and maintenance
• ALL_TAB_STATISTICS — table statistics and staleness
• ALL_STAT_EXTENSIONS — extended-statistics metadata