Re: Finding stats gather

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<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';

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