10 Stale Optimizer Stats

oracle_dba_perf_toolkit/daily_dba/10_stale_optimizer_stats.sql

Disclaimer: This script is provided for educational and learning purposes. Review it carefully, test in a non-production environment, confirm privileges, and validate impact before using it in production.

SET PAGESIZE 200
SET LINESIZE 220
SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK ON
SET TAB OFF

COLUMN owner FORMAT A25
COLUMN table_name FORMAT A35
COLUMN stale_stats FORMAT A5
COLUMN last_analyzed FORMAT A20

SELECT
    owner,
    table_name,
    stale_stats,
    TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') last_analyzed
FROM
    dba_tab_statistics
WHERE
    stale_stats = 'YES'
ORDER BY
    owner,
    table_name;