OracleStatistics

Tables that have never been analyzed

Application tables with no statistics at all, which forces dynamic sampling or guesswork. Locked statistics are shown so you know which ones are deliberate.

12c+

Not yet verified. How scripts are tested

ora-no-stats.sql

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN owner       FORMAT A204COLUMN table_name  HEADING "TABLE"  FORMAT A305COLUMN stattype_locked HEADING "LOCKED" FORMAT A66 7SELECT owner, table_name, stattype_locked8FROM dba_tab_statistics9WHERE last_analyzed IS NULL10  AND object_type = 'TABLE'11  AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')12ORDER BY owner, table_name;

Save it as ora-no-stats.sql and run it with SQL> @ora-no-stats.

Open in denrepo

More Oracle scripts: Statistics

Tables and partitions with stale statisticsApplication objects the optimizer considers stale. The flush at the top makes sure recent DML is counted before the check.
Automatic statistics job status and historyWhether the nightly optimizer stats job is enabled, and how each run in the last week went. A run that keeps hitting the window end isn't finishing…
Gather statistics for a table or schemaTemplates for gathering stats by hand. New statistics can change execution plans, so do this at a quiet time and know which plans matter.