How to find Stale object and last analyzed for tables/indexes ?

To find stale objects and the last analyzed date for tables and indexes in an Oracle Database, you can query the data dictionary views DBA_TAB_STATISTICS, DBA_IND_STATISTICS, and DBA_OBJECTS. Stale statistics are indicated by the STALE_STATS column in the DBA_TAB_STATISTICS view for tables and in the DBA_IND_STATISTICS view for indexes.

Find Stale Tables

SELECT owner, table_name, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE stale_stats = 'YES';

Find Stale Indexes

SELECT owner, index_name, stale_stats, last_analyzed
FROM dba_ind_statistics
WHERE stale_stats = 'YES';

Find Last Analyzed Date for Tables

SELECT owner, table_name, last_analyzed
FROM dba_tables
ORDER BY last_analyzed DESC;

Find Last Analyzed Date for Indexes

SELECT owner, index_name, last_analyzed
FROM dba_indexes
ORDER BY last_analyzed DESC;

Example Query to Check Both Tables and Indexes for Stale Stats

Here is an example of how to combine information about stale statistics and the last analyzed date for both tables and indexes:

-- For Tables
SELECT 
    'TABLE' AS object_type, 
    owner, 
    table_name AS object_name, 
    stale_stats, 
    last_analyzed 
FROM 
    dba_tab_statistics 
WHERE 
    stale_stats = 'YES'
UNION ALL
-- For Indexes
SELECT 
    'INDEX' AS object_type, 
    owner, 
    index_name AS object_name, 
    stale_stats, 
    last_analyzed 
FROM 
    dba_ind_statistics 
WHERE 
    stale_stats = 'YES';

Additional Information

• STALE_STATS Column: Indicates whether the statistics are stale (YES or NO). If statistics are stale, it usually means that the data in the table has changed significantly since the last time statistics were gathered.
• LAST_ANALYZED Column: Provides the date and time when the statistics were last collected.

Scheduling Statistics Gathering

To ensure that your statistics are up-to-date, you can use the Oracle DBMS_STATS package to gather statistics. For example:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('YOUR_SCHEMA_NAME');

You can schedule this as a regular job using Oracle’s job scheduler (DBMS_SCHEDULER or DBMS_JOB).

Query to Find Objects Not Analyzed Recently

If you want to find objects that have not been analyzed recently, you can modify the query to include a date condition. For example, to find objects not analyzed in the last 30 days:

-- For Tables
SELECT 
    'TABLE' AS object_type, 
    owner, 
    table_name AS object_name, 
    last_analyzed 
FROM 
    dba_tab_statistics 
WHERE 
    last_analyzed < SYSDATE - 30
UNION ALL
-- For Indexes
SELECT 
    'INDEX' AS object_type, 
    owner, 
    index_name AS object_name, 
    last_analyzed 
FROM 
    dba_ind_statistics 
WHERE 
    last_analyzed < SYSDATE - 30;

These queries help you monitor and manage the statistics for your tables and indexes, ensuring that the optimizer has accurate and up-to-date information for query planning.