Optimizer Statistics

49.1 Types of Statistics

Table: number of rows, blocks, average row length.

Column: NDV (number of distinct values), nulls, low/high values, histograms (frequency, top-frequency, height-balanced (legacy), hybrid).

Index: B-level, leaf blocks, distinct keys, clustering factor.

System statistics: CPU speed, I/O.

Dictionary and fixed object statistics. Extended statistics: column groups and expression statistics.

49.2 Gathering Statistics

Pending statistics (PUBLISH = FALSE, test with OPTIMIZER_USE_PENDING_STATISTICS).

Online statistics gathering for bulk loads (12c), real-time statistics and high-frequency automatic stats (19c, Exadata/ADB).

Dynamic sampling (OPTIMIZER_DYNAMIC_SAMPLING), statistics for GTTs (session-private, 12c).

EXEC DBMS_STATS.GATHER_TABLE_STATS('SHOP', 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree => 4);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SHOP', options => 'GATHER STALE');
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

-- Column group (correlated columns)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SHOP', 'CUSTOMERS', '(CITY, COUNTRY)') FROM dual;

-- Preferences
EXEC DBMS_STATS.SET_TABLE_PREFS('SHOP', 'ORDERS', 'INCREMENTAL', 'TRUE');     -- partitioned tables
EXEC DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT', '5');

-- Lock, export, import, restore, compare
EXEC DBMS_STATS.LOCK_TABLE_STATS('SHOP', 'GTT_CART');
EXEC DBMS_STATS.CREATE_STAT_TABLE('SHOP', 'STATS_BKP');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('SHOP', 'STATS_BKP');
EXEC DBMS_STATS.RESTORE_TABLE_STATS('SHOP', 'ORDERS', SYSTIMESTAMP - INTERVAL '1' DAY);
SELECT * FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY('SHOP', 'ORDERS', SYSTIMESTAMP - 1));

-- Inspect
SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics;

…