Re: stale partition statistics
Date: Fri, 5 Mar 2021 15:38:22 +0000
Message-ID: <AM0PR10MB2081EB7B162B9CC855B7FB0D85969_at_AM0PR10MB2081.EURPRD10.PROD.OUTLOOK.COM>
Please bear in mind that with incremental partition stats, they may gather more frequently that you think depending upon a few reasons - even when stated as NOT being stale. By default, 1 DML in an incremental partition means it is stale. It will show as not stale in the DBA views, but will gather (unless you set incremental staleness).
dbms_stats.set_table_prefs(owner,table,'INCREMENTAL_STALENESS','USE_STALE_PERCENT');
If you have your METHOD_OPT set to "FOR ALL COLUMNS SIZE AUTO, ", new SQL may result in the stats gather deciding to add histogram(s) based upon the contents of sys.col_usage$, and you will get every partiton gathered regardless of staleness.
Can you clarify "I have incremental statistics and the statistics job ran with REPEAT," - do you mean in the method_opt ?
Neil Chandler.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Bob-W1TI <w1ti_at_comcast.net> Sent: 05 March 2021 15:18
To: nenad.noveljic_at_vontobel.com <nenad.noveljic_at_vontobel.com>; ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org> Subject: Re: stale partition statistics
check out dba_tab_statistics i use that all the time to determine if tables go stale and when to work on them
bob
On 3/5/21 10:07 AM, Noveljic Nenad wrote:
Is there a way to find out if partition statistics were stale at some point in time without restoring the database?
I’m trying to find out why the partition statistics were calculated on 19c. I have incremental statistics and the statistics job ran with REPEAT, so it shouldn’t have initiated the calculation of partition statistics unless they were stale. But I doubt that, because these are partitions with old data, which shouldn’t have changed.
Best regards,
Nenad
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 05 2021 - 16:38:22 CET