Oracle 11g自动统计收集疑问：为何仍存在stale stats对象？

Great question—this is a super common gotcha with Oracle’s automatic statistics collection in 11g and later. Let’s walk through the most likely reasons you’re still seeing objects marked stale_stats='YES' even after the auto job should have handled them:

1. Maintenance Window Constraints #

The auto optimizer stats collection job only runs during predefined maintenance windows (default settings are weeknights 10PM–6AM and full weekends, but these can be modified). If your database has a lot of stale objects, or some objects take an unusually long time to gather stats for, the job might hit the window’s time limit before finishing all work. Any unprocessed stale objects will stay marked as stale until the next window runs.

To check your maintenance window configuration:

SELECT window_name, repeat_interval, duration 
FROM dba_scheduler_windows 
WHERE window_name LIKE '%MAINTENANCE%';

2. Objects Excluded from Automatic Collection #

The auto job doesn’t process every object by default. Some common exclusions include:

• Tables marked NOVALIDATE
• Session-specific temporary tables (global temporary tables with ON COMMIT DELETE ROWS are skipped)
• Objects where you’ve explicitly disabled auto stats collection via preferences:

  SELECT owner, table_name 
  FROM dba_tab_stat_prefs 
  WHERE preference_name='AUTO_STAT_COLLECTION' 
    AND preference_value='OFF';
  

• Tables with custom STALE_PERCENT thresholds set above 10% (the job only processes objects where modifications exceed their specific threshold)

3. Stale Stats Calculation Edge Cases #

Oracle marks stats as stale based on modification counts tracked in dba_tab_modifications. Sometimes:

• The modification data might not have been flushed to this view yet (though the auto job usually runs DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO before starting, but exceptions exist)
• You’ve manually modified the STALE_PERCENT preference for an object (e.g., set to 50%), so even if it’s marked as stale by the 10% default, the auto job won’t touch it until modifications hit 50%

Check an object’s specific preferences with:

SELECT preference_name, preference_value 
FROM dba_tab_stat_prefs 
WHERE owner='YOUR_OWNER' AND table_name='YOUR_TABLE';

4. Job Failures or Skipped Runs #

The auto job might not have run successfully at all. Common issues here include:

• The database was down during the maintenance window
• Permissions issues (the job runs as SYS, so if SYS lacks access to certain objects, stats collection will fail for them)
• Object-level problems (e.g., locked tables, corrupted indexes, or invalid constraints that block stats gathering)

Check the job’s run history to spot failures:

SELECT job_name, status, run_start_date, error_message 
FROM dba_scheduler_job_run_details 
WHERE job_name='AUTO_OPTIMIZER_STATS_COLLECTION'
ORDER BY run_start_date DESC;

5. Partitioned Object Quirks #

For partitioned tables, the auto job typically handles stats at the partition level first. If a partition’s modifications hit the threshold, it will gather stats for that partition—but the table-level stats might still be marked as stale if they’re not set to aggregate partition stats automatically. Alternatively, if the table-level stats are stale but no individual partition meets the threshold, the job might skip updating the table-level stats.

Quick Troubleshooting Steps to Start With #

1. Verify the AUTO_OPTIMIZER_STATS_COLLECTION job ran successfully and completed within the maintenance window
2. Check if the stale objects are excluded from auto collection via preferences
3. Confirm the modification rate of the stale objects matches their configured STALE_PERCENT threshold

内容的提问来源于stack exchange，提问作者Landon Statis