Oracle Stale Optimizer Statistics

Finds tables with stale or missing statistics. Catches the most common cause of bad execution plans.

#statistics#stale-stats#optimizer

oracle · sql

SELECT owner, table_name, num_rows, last_analyzed, stale_stats
FROM   dba_tab_statistics
WHERE  owner NOT IN ('SYS','SYSTEM','XDB','MDSYS','CTXSYS')
  AND  object_type = 'TABLE'
  AND  (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER  BY num_rows DESC NULLS LAST;

Operational Notes & Remediation Guidance

Tables with stale statistics produce sub-optimal execution plans; gather stats via `DBMS_STATS.GATHER_TABLE_STATS`.

Production Safety & Execution Standards

This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
Prior to executing on production systems under high load, verify the target database version (Min. Version: 11g).
Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Other ORACLE Diagnostic Scripts

View All (100)

#61Performance

Oracle Active Sessions and Wait Events

Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.

View Script

#62Locking

Find Blocking Sessions in Oracle

Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.

View Script

#63Storage

Oracle Tablespace Usage Including Autoextend

Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.

View Script

#64Performance

Top 20 Most Expensive Oracle SQL with v$sqlstats

Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.

View Script