How to check for invalid incremental stats

From: William Robertson <william_at_williamrobertson.net>
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--
http://www.freelists.org/webpage/oracle-l Received on Sun Jun 11 2023 - 11:48:30 CEST

Original text of this message