CODEWITHSUNDEEP
oracle-databaseobjectstatistics
Landon Statis
Landon Statis

Reputation: 839

Oracle auto-gather statistics & stale statistics

Starting in Oracle 11g, GATHER_STATS_JOB is no longer valid, and has been replace by "auto optimizer stats collection".

This job supposedly runs during the "maintenance windows" and gathers statistics for objects which have changed 10% or more, or have stale stats. If this is true, then why when I run a query checking "stale_stats='YES'", I still get objects?

Maybe I'm not understanding how the job executes......

Upvotes: 0

Views: 900

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

Two broad possibilities

  1. Oracle updates stale_statistics to "YES" in dba_tab_statistics periodically throughout the day as tables undergo changes. It is entirely possible that a table had just under the threshold amount of changes when stats were gathered this morning and that stale_stats flipped to "YES" during the day today when a few more changes were made.

  2. Depending on how many objects had stale stats when the job ran and how much data those tables contained, how large your maintenance window is, and how powerful your server is, it is possible that the statistics job had to be aborted before it could re-gather all the stale statistics. If the job was aborted, that abort would be logged in the job history. If this happened because there happened to be a large number of changes one day (say you ran an annual purge process that deleted a large amount of data from almost every table in the database), the stale statistics would be updated over the course of several days worth of statistics job runs until the job caught up.

Upvotes: 1

Related Questions