When Oracle Statistic Gathering times out — I
This first part is about running manually, killing the job, and locking the stats
In a previous post, I explained how to see where the Auto Stats job has been running and timed out:
SYS.STATS_TARGET$
I got a case where it always timed out at the end of the standard maintenance window. One table takes many hours, longer than the largest maintenance window, it will always be killed at the end. And, because it stayed stale, and staler each day, this table was always listed first by the Auto Stat job. And many tables never got their chance to get their stats gathered for … years.
In that case, the priority is to gather statistics. That can be long. Then I run the job manually: