Re: How to check for invalid incremental stats

On 6/11/23 05:48, William Robertson wrote:
> 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
>
>
You didn't mention the version of the database. In newer versions, there is DBA_TAB_COL_STAT_MODELS view with the STATUS column. The value of the STATUS column is ENABLED or DISABLED. However, this view is only available in 21c and newer databases. Also there is DBA_TAB_STATISTICS view with the STALE_STATS column which can show you whether the stats on the table are stale or not. Also, in Oracle 19c the parameter OPTIMIZER_REAL_TIME_STATISTICS is only allowed on Exadata. In Oracle 21c documentation and Oracle 23c documentation, the warning about the license requirement is missing, which probably means that the parameter can be used on an ordinary database, on a commercial Linux or Winduhs server.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l