Re: How to check for invalid incremental stats
Date: Mon, 12 Jun 2023 14:44:24 +0000
Message-ID: <AM8P194MB1628727AA5FA445792B541B48554A_at_AM8P194MB1628.EURP194.PROD.OUTLOOK.COM>
William,
You do need to be aware of the incremental stats "gotcha" in relation to staleness. You can see if your stats are stale if DBA_TAB_STATISTICS.STALE_STATS is not "N". Except with INCREMENTAL stats, this is not true.
By default, if you update a single row in a partition, your stats will be regathered for that partition. STALE_STATS still shows as "N" but they are gathered anyway as the synopses "cannot be relied upon" by Oracle (well, that's their excuse)
The way around this, from 12C, is to set INCREMENTAL_STALENESS in your stats prefs, so partition stats only become stale at the same percentage as table stats. I would also recommend settings "USE_LOCKED_STATS" as if stats become stale on a partition with locked stats, INCREMENTAL gathering switches off completely (rather than use stats which are guaranteed to be out of date).
SQL> exec dbms_stats.set_table_prefs('schema','table-name','INCREMENTAL_STALENESS','USE_STALE_PERCENT,USE_LOCKED_STATS');
Another cause of doing a full gather is if the stats gathering process decides to add a new histogram. This would occur if a new SQL adds a predicate for a table column which has not previously had a predicate. Column with predicates, and therefore candidates for histograms, can be viewed in SYS.COL_USAGE$. If your METHOD_OPT is set to "FOR ALL COLUMNS SIZE AUTO", this is what is used to determine if a column is a candidate for a histogram. It can be worthwhile having a specific METHOD_OPT to maintain the current histograms on such tables and preventing new ones implicitly appearing due to a novel SQL (do not​ use FOR ALL COLUMNS SIZE REPEAT - they changed how this works in 12C and it can have very unpleasant side effects, degrading the number of buckets allowed for a histogram)
e.g. SQL> exec dbms_stats.set_table_prefs('schema','table-name','METHOD_OPT','FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 255 column-a, column-b, column-z FOR COLUMNS SIZE 2000 column-c, column-e');]
regards
Neil Chandler
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Stefan Koehler <contact_at_soocs.de> Sent: 12 June 2023 13:41
To: william_at_williamrobertson.net <william_at_williamrobertson.net>; oracle-l <oracle-l_at_freelists.org> Subject: Re: How to check for invalid incremental stats
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: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.soocs.de%2F&data=05%7C01%7C%7C8669659615814fa88d4c08db6b428074%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638221705686910789%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=TFPKQDqehSiSkNeqdhfJWcLoo9caPIPFFbbA9OtEoWA%3D&reserved=0<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--
> https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=05%7C01%7C%7C8669659615814fa88d4c08db6b428074%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638221705686910789%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2F3cI7IbZ1Wn7S1t%2FFq9%2FPbnPr62WylWdLpqEG7m3lp4%3D&reserved=0<http://www.freelists.org/webpage/oracle-l>
--
https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=05%7C01%7C%7C8669659615814fa88d4c08db6b428074%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638221705686910789%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2F3cI7IbZ1Wn7S1t%2FFq9%2FPbnPr62WylWdLpqEG7m3lp4%3D&reserved=0<http://www.freelists.org/webpage/oracle-l>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 12 2023 - 16:44:24 CEST