gathering statistics

— gather stale statistics

EXEC DBMS_STATS.gather_database_stats(options=>’GATHER STALE’, cascade => TRUE);

— change stale percentage: (default 10%)

EXEC DBMS_STATS.set_database_prefs(‘STALE_PERCENT’, ‘5’);

— gather statistics if empty

EXEC DBMS_STATS.gather_database_stats(options=>’GATHER EMPTY’, cascade => TRUE);

— gather full db stats

EXEC DBMS_STATS.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE);

— gather dict stats

EXEC DBMS_STATS.gather_dictionary_stats;

— gather system stats
EXEC DBMS_STATS.gather_system_stats(‘interval’, interval => 180);

— gather system stats @exadata

EXEC DBMS_STATS.gather_system_stats(‘EXADATA’);

— gather fixed obj stats:

EXEC DBMS_STATS.gather_fixed_objects_stats;

— gather table stats

EXEC DBMS_STATS.gather_table_stats(‘MXMCORP’, ‘CT2000_TTAT_LOG’, estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE);

— gather index stats

EXEC DBMS_STATS.gather_index_stats(‘MXMCORP’, ‘CT2000_TTAT_LOG_IDX1’, estimate_percent => dbms_stats.auto_sample_size);

— table stats lock

BEGIN
DBMS_STATS.LOCK_TABLE_STATS(‘OE’,’ORDERS’);
END;
/

BEGIN
DBMS_STATS.LOCK_SCHEMA_STATS(‘HR’);
END;
/

— table stats unlock

BEGIN
DBMS_STATS.UNLOCK_TABLE_STATS(‘OE’,’ORDERS’);
END;
/

reference:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why