Quick Question: Why statistics are not collected though the automatic job shown it has ran sucessfully

Why you statistics are not collecting via automatic stats gather job, though your job showing succeeded?

Well the answer is, coz of Duration of schedule window, yes

As you aware , the jobs now scheduled in windows Read here

• The weekday windows start at 10PM and run for 8 hours.
• The weekend windows start at 6am and run for 20 hours.

Consider that, on monday the job kicked off and its analyzing a big table say about 200gb and has many partitions and took 6 hours, (normally table stats run one by one, not concurrently unlike 11g feature if you opt so)

So once its completed its duration, the job silently finishes off and does not report any error, resulting the other tables statistics to be stale and indeed this big table too.

To make worsen things, on tuesday, the job again started and we aware the weekday window is of 8 hours and again this big table started first to analyze (that’s the priority oracle chooses, verify the gather_stats_job_prog) and didn’t completed though in 8 hours, again resulted other tables statistics to be stale and this table too.

Its get go on for whole week and then weekend window came up, remember we have 20 hours for weekend window, and our job proceeded to collect big table and also the other tables whole weekend and , bingo we got stats.

But what about other days i.e weekdays, without statistics you well aware how your optimizer behaves, to fix this either lock the big table and exclude from the job and manually create a separate job for the same.

Foot Note:- You can leverage the concurrent stats gathering feature in 11g to mitigate this. But caveat is more resource usage.

-Thanks

Geek DBA