Oracle Database Query: Find Tables with Outdated Statistics

Purpose

This Oracle Database query helps database administrators monitor the progress of table statistics collection by identifying tables that have analyze dates older than the current date. It's particularly useful when running large-scale ANALYZE operations and you need to track how many tables still require statistics updates.

This is useful if you are running an analyze and want to see how much is left to do

SQL Code

1select	count(last_analyzed) left_to_do
2from	dba_tables
3where	owner = '&schema'
4and	trunc(last_analyzed) < trunc(sysdate)
5order	by 1
6/

Code Breakdown

1select count(last_analyzed) left_to_do from dba_tables where owner = '&schema' and trunc(last_analyzed) < trunc(sysdate) order by 1 / 

Key Components Explained

1. SELECT Clause

• count(last_analyzed) left_to_do: Counts the number of tables with outdated statistics
• Uses an alias left_to_do for clear result interpretation

2. FROM Clause

• dba_tables: Oracle data dictionary view containing metadata about all tables in the database
• Requires DBA privileges or SELECT_CATALOG_ROLE to access

3. WHERE Conditions

• owner = '&schema': Filters tables by specific schema owner (uses substitution variable)
• trunc(last_analyzed) < trunc(sysdate): Compares truncated analyze date with current date

4. Functions Used

• TRUNC(): Removes time portion from dates for accurate day-level comparison
• SYSDATE: Returns current database server date and time

Key Insights and Use Cases

When to Use This Query

• Before starting analyze operations: Determine scope of work required
• During long-running statistics jobs: Monitor progress and estimate completion time
• Regular maintenance checks: Identify schemas with stale statistics
• Performance troubleshooting: Find tables that might have outdated optimizer statistics

Important Considerations

1. NULL Values Handling

• Tables never analyzed will have last_analyzed = NULL
• The COUNT(last_analyzed) function excludes NULL values automatically
• Consider using COUNT(*) if you want to include never-analyzed tables

2. Time Zone Considerations

• SYSDATE reflects database server time zone
• Ensure consistency when comparing across different time zones

3. Performance Impact

• Query accesses data dictionary views
• Minimal performance impact for monitoring purposes
• Consider adding additional filters for large databases

Enhanced Variations

Include Never-Analyzed Tables

1select count(*) total_tables_needing_analysis from dba_tables where owner = '&schema' and (last_analyzed is null or trunc(last_analyzed) < trunc(sysdate))` 

Show Detailed Information

1select table_name, last_analyzed,  trunc(sysdate) - trunc(last_analyzed) days_old from dba_tables where owner = '&schema' and trunc(last_analyzed) < trunc(sysdate) order by last_analyzed nulls first` 

Best Practices

1. Statistics Management Strategy

• Regular Schedule: Implement automated statistics collection
• Incremental Updates: Use Oracle's automatic statistics gathering
• Monitoring: Track statistics freshness across critical schemas

2. Query Optimization

• Index Usage: Query benefits from indexes on owner column in data dictionary
• Result Caching: Consider caching results for frequently monitored schemas
• Batch Processing: Group multiple schema checks for efficiency

3. Integration with Maintenance Scripts

1-- Example integration in maintenance script DECLARE  v_count NUMBER; BEGIN  SELECT count(last_analyzed) INTO v_count FROM dba_tables WHERE owner = 'PRODUCTION_SCHEMA' AND trunc(last_analyzed) < trunc(sysdate);  DBMS_OUTPUT.PUT_LINE('Tables requiring analysis: ' || v_count); END; /` 

Related Oracle Features

• DBMS_STATS Package: Automated statistics collection
• Optimizer Statistics: Impact on query execution plans
• AWR Reports: Performance monitoring and statistics analysis
• Enterprise Manager: GUI-based statistics monitoring

Conclusion

This simple yet powerful query provides essential visibility into your Oracle database's statistics health. Regular monitoring of table statistics ensures optimal query performance and helps database administrators maintain efficient database operations.

Combine this query with automated alerting systems to proactively manage database statistics and prevent performance degradation due to stale optimizer statistics.