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> 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> 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> 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
>> Twitter: _at_OracleSK
>>
>> > Pap <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