How to check for invalid incremental stats
Date: Sun, 11 Jun 2023 10:48:30 +0100
Message-Id: <46E86E63-EFC3-406B-9A87-FE59B52830F7_at_williamrobertson.net>
Hi all,
I am looking for a way to detect the case where a table set to incremental statistics has had its synopses invalidated and will therefore require a full re-gather to get them back into a usable state.
Let's say there is a large list-partitioned table and it takes 2 hours to regather stats from scratch, or 30 seconds using incremental stats. Now imagine there is a batch job to gather stats on the table following a load. Normally this runs in 30 seconds, but if you add or modify a column, the incremental synopses are invalidated, dbms_stats has to start from scratch and the next time the job runs it takes 2 hours. (Or, there's no separate batch job but the overnight/weekend auto stats job will now lose 2 hours with unpredictable consequences). Worse, sometimes this doesn't fix it and it remains in a broken state until you manually delete the stats and regather them (Oracle 12.2, planning to migrate to 19c one day soon-ish.) Now multiply that by about 10 tables. Yes we have code reviews, but this stuff gets through from time to time.
Is there a way to detect this scenario (synopses for a table are in an invalid state) other than by attempting to gather stats and seeing if it takes more than a few minutes? I can't see anything obvious in the WRI$_OPTSTAT tables.
Thanks
William Robertson--