Re: How to check for invalid incremental stats
Date: Mon, 12 Jun 2023 14:41:04 +0200 (CEST)
Message-ID: <936339583.9014.1686573664114_at_ox.hosteurope.de>
Hello William,
I played a little bit around with DBMS_STATS tracing ("SQL> exec dbms_stats.set_global_prefs('TRACE',4+8+256+2048+8192);") and tried to figure out how Oracle does it.
The tracing reveals this kind of logic (after I have added a new column to the partitioned table which already got valid incremental statistics):
-----------8<--------
DBMS_STATS: delete synopses of all stale partitions (2 groups)
DBMS_STATS: Starting query at 12-JUN-23 02.22.18.733996000 PM +02:00 DBMS_STATS: select /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ 1
from dual where exists (select null from wri$_optstat_synopsis_head$ h, table(:clist_syn) c where h.bo# = :bo# and h.intcol# = c.column_value and h.analyzetime is not null and h.spare1 is null and group# in (76410,76412) )
DBMS_STATS: Starting query at 12-JUN-23 02.22.18.734396000 PM +02:00
DBMS_STATS: delete /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */
from sys.wri$_optstat_synopsis_head$ where intcol# in (select column_value from table(:clist_syn)) and bo# = :bo# and group# in (76410,76412) (objn=38204)
DBMS_STATS: delete synopses of a single partition
DBMS_STATS: Starting query at 12-JUN-23 02.22.18.737961000 PM +02:00 DBMS_STATS: select /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ 1
from dual where exists (select null from wri$_optstat_synopsis_head$ h, table(:clist_syn) c where h.bo# = :bo# and h.intcol# = c.column_value and h.analyzetime is not null and h.spare1 is null and group# in (:singlegroup) )
DBMS_STATS: delete synopses of a single partition
DBMS_STATS: Starting query at 12-JUN-23 02.22.18.752917000 PM +02:00 DBMS_STATS: select /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ 1
from dual where exists (select null from wri$_optstat_synopsis_head$ h, table(:clist_syn) c where h.bo# = :bo# and h.intcol# = c.column_value and h.analyzetime is not null and h.spare1 is null and group# in (:singlegroup) )
-----------8<--------
... so based on these queries it should be pretty easy to check in advance, if you need to re-gather from the scratch or if your incremental synopses are still valid.
-----------8<--------
select object_id, object_type from dba_objects where object_name = 'PART_TEST' and object_type = 'TABLE';
OBJECT_ID OBJECT_TYPE
---------- -----------------------
38204 TABLE
--> Before adding a new column
select * from wri$_optstat_synopsis_head$ where bo# = 38204;
BO# GROUP# INTCOL# SYNOPSIS# SPLIT ANALYZET SPARE1 SPARE2
---------- ---------- ---------- ---------- ---------- -------- ---------- --------------------------------------------------------------------------------
38204 76410 1 0 12.06.23 1 0D0C00E9000300000000000000030000000000000004000000000000000000000000000000000000
38204 76410 2 0 12.06.23 1 0D0C048E000100000000000000010000000000000004000000000000000000000000000000000000
38204 76410 3 0 12.06.23 1 0D0C0414000400000000000000040000000000000004000000000000000000000000000000000000
38204 76410 4 0 12.06.23 1 0D0C0822000100000000000000010000000000000001000000000000000000000000000000000000
38204 76412 1 0 12.06.23 1 0D0C0D67000100000000000000010000000000000003000000000000000000000000000000000000
38204 76412 2 0 12.06.23 1 0D0C0DE1000100000000000000010000000000000003000000000000000000000000000000000000
38204 76412 3 0 12.06.23 1 0D0C0822000200000000000000020000000000000003000000000000000000000000000000000000
38204 76412 4 0 12.06.23 1 0D0C0A0E000100000000000000010000000000000001000000000000000000000000000000000000
--> After adding a new column, which triggers a re-gather from the scratch select * from wri$_optstat_synopsis_head$ where bo# = 38204;
BO# GROUP# INTCOL# SYNOPSIS# SPLIT ANALYZET SPARE1 SPARE2
---------- ---------- ---------- ---------- ---------- -------- ---------- --------------------------------------------------------------------------------
38204 0 5 0 12.06.23 1
38204 76410 1 0 12.06.23 1 0D0C00E9000300000000000000030000000000000004000000000000000000000000000000000000
38204 76410 2 0 12.06.23 1 0D0C048E000100000000000000010000000000000004000000000000000000000000000000000000
38204 76410 3 0 12.06.23 1 0D0C0414000400000000000000040000000000000004000000000000000000000000000000000000
38204 76410 4 0 12.06.23 1 0D0C0822000100000000000000010000000000000001000000000000000000000000000000000000
38204 76412 1 0 12.06.23 1 0D0C0D67000100000000000000010000000000000003000000000000000000000000000000000000
38204 76412 2 0 12.06.23 1 0D0C0DE1000100000000000000010000000000000003000000000000000000000000000000000000
38204 76412 3 0 12.06.23 1 0D0C0822000200000000000000020000000000000003000000000000000000000000000000000000
38204 76412 4 0 12.06.23 1 0D0C0A0E000100000000000000010000000000000001000000000000000000000000000000000000
-----------8<--------
... so just check for INTCOL# without a group and/or if SPARE2 is empty.
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: _at_OracleSK<
> William Robertson <william_at_williamrobertson.net> hat am 11.06.2023 11:48 CEST geschrieben:
>
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 12 2023 - 14:41:04 CEST