OracleStatistics

Tables and partitions with stale statistics

Application objects the optimizer considers stale. The flush at the top makes sure recent DML is counted before the check.

12c+

Not yet verified. How scripts are tested

ora-stale-stats.sql

1EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;2 3CLEAR COLUMNS4SET LINESIZE 160 PAGESIZE 200 TRIMOUT ON TAB OFF5COLUMN owner          FORMAT A206COLUMN table_name     HEADING "TABLE"     FORMAT A307COLUMN partition_name HEADING "PARTITION" FORMAT A258COLUMN object_type    HEADING "LEVEL"     FORMAT A129COLUMN analyzed       FORMAT A1010COLUMN num_rows       HEADING "ROWS"      FORMAT 999,999,999,99011 12SELECT owner, table_name, partition_name, object_type,13       TO_CHAR(last_analyzed, 'YYYY-MM-DD') AS analyzed, num_rows14FROM dba_tab_statistics15WHERE stale_stats = 'YES'16  AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')17ORDER BY owner, table_name, partition_name;

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

Open in denrepo

More Oracle scripts: Statistics

Tables that have never been analyzedApplication tables with no statistics at all, which forces dynamic sampling or guesswork. Locked statistics are shown so you know which ones are…
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.