Re: Finding stats gather

From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Thu, 14 Dec 2023 09:43:19 +0000
Message-ID: <GVXP189MB20548ECE0A4FDCD882FDE9D5858CA_at_GVXP189MB2054.EURP189.PROD.OUTLOOK.COM>

Pap,

When using INCREMENTAL stats gather there's a couple reasons why you might gather stats on a "not stale" partition:

1. partitions which are not labelled as "stale" might actually be stale. The modification of a single row in a partition will cause the stats to re-gather.
2. if the stats on a partition are locked, and a single row is modified, INCREMENTAL is effectively (silently) disabled as Oracle can't trust the stats for a global asggregation
3. something like a new histogram appeared due to a new SQL having a previously unused predicate and the METHOD_OPT is default (for all columns size auto)
4. there are job(s) gathering stats, as well as the auto task, and the job is passing in parameters which are causing the issue (rather than relying on "prefs" for consistency)

For a) dbms_stats.set_table_prefs('schema','table','INCREMENTAL_STALENESS','USE_STALE_PERCENT')
For b) dbms_stats.set_table_prefs('schema','table','INCREMENTAL_STALENESS','USE_STALE_PERCENT,USE_LOCKED_STATS')
For c) check which predicates are candidates for a histogram (in SYS.COL_USAGE$) and explicitly control your histograms. This does mean that you don't get new histograms, which might not be your intention

  exec dbms_stats.set_table_prefs('NEIL','INTERVAL_TAB','METHOD_OPT',   'FOR ALL COLUMNS SIZE 1
   FOR COLUMNS SIZE 256 colA,colQ
   FOR COLUMNS SIZE 2000 colB,colZ');
For d) Consider ignoring any command line parameters for stats gather jobs (dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER','TRUE');

To validate what is going on, you can always (carefully!) trace the stats gather [exec dbms_stats.set_global_prefs('trace',65532); ]

regards

Neil Chandler

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Pap <oracle.developer35_at_gmail.com> Sent: 13 December 2023 18:23
To: Dominic Brooks <dombrooks_at_hotmail.com> Cc: Stefan Koehler <contact_at_soocs.de>; Oracle L <oracle-l_at_freelists.org> Subject: Re: Finding stats gather

Seems like you are spot on. So was it a bug or its expected behavior? In this case also, once the auto stats gather , some days crosses the set window the restore_stats triggers for some partition table. And the bad part is, post that the manual gather(which is incremental stats) on those partition table runs longer as it tries to gather on all the partitions which are not even modified. How should we tackle this scenario.

On Wed, Dec 13, 2023 at 12:43 AM Dominic Brooks <dombrooks_at_hotmail.com<mailto:dombrooks_at_hotmail.com>> wrote: I’ve got a vague/unreliable memory that this can be triggered when you hit the end of the stats window for whatever object was mid-collection.

Regards
Dominic

Sent from my iPhone

On 12 Dec 2023, at 18:46, Pap <oracle.developer35_at_gmail.com<mailto:oracle.developer35_at_gmail.com>> wrote:


Thank you Stefan.

Yes i think we don't have audit_trail set here, so it means we have to restart the database for it to be enabled. Also does it have any performance impact if we turn on this audit trail in a highly active/transactional database for a longer period of time(say for a week/month)?

On Tue, Dec 12, 2023 at 11:35 PM Stefan Koehler <contact_at_soocs.de<mailto:contact_at_soocs.de>> wrote: Hello Pap,
I guess the easiest way would be to audit DBMS_STATS usage for a short period of time.

• Set audit_trail to "db,extended", if not already done SQL> alter system set audit_trail=db,extended scope=spfile;
• Restart database (only needed, if audit_trail needs to be set) SQL> audit execute on sys.dbms_stats; SQL> select * from dba_audit_object where obj_name = 'DBMS_STATS';

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: www.soocs.de<http://www.soocs.de/> Twitter: _at_OracleSK

> Pap <oracle.developer35_at_gmail.com<mailto:oracle.developer35_at_gmail.com>> hat am 12.12.2023 13:48 CET geschrieben:
>
>
> Hi All,
> We have a customer database in which restore_table_stats gets triggered(as we see it from dba_optstat_operations) and we want to find where exactly it's getting triggered from. Basically the source(which might be app code) of this command. We don't see it in gv$sql or dba_hist_sqltext and can't tie it up with ASH/AWR views. Is there any way to find this out?
>
>
> Regards
> Pap

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

Received on Thu Dec 14 2023 - 10:43:19 CET