RE: DBMS_STATS.GATHER_DICTIONARY_STATS not gathering stale statistics

From: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
Date: Wed, 14 Jul 2021 15:01:14 +0000
Message-ID: <DM6PR09MB467788C968396E3D54C3365CDF139_at_DM6PR09MB4677.namprd09.prod.outlook.com>



What happens if you run: dbms_stats.gather_fixed_objects_stats;

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Ls Cheng Sent: Wednesday, July 14, 2021 10:55 AM

To: Fairlie Rego <fairlie.rego_at_gmail.com>
Cc: Powell, Mark <mark.powell2_at_dxc.com>; Oracle Mailinglist <oracle-l_at_freelists.org>
Subject: Re: DBMS_STATS.GATHER_DICTIONARY_STATS not gathering stale statistics

Hi

Patch applied but still got strange results

SQL> exec dbms_stats.gather_dictionary_stats(degree => 8);

PL/SQL procedure successfully completed.

Elapsed: 00:37:07.89

select to_char(last_analyzed, 'yyyymmdd') , count(*) from dba_tab_statistics where stale_stats = 'YES' and owner = 'SYS' group by to_char(last_analyzed, 'yyyymmdd') order by 1;

TO_CHAR( COUNT(*)
-------- ----------

20210713          1
20210714        346

I should get very few o 0 tables with stale statistics under sys IMHO.

Thanks

On Wed, Jul 14, 2021 at 1:41 AM Ls Cheng <exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>> wrote: Hi

I am not 100% sure if the symptoms match because the last_analyzed for those stale stats tables are from a few days ago to a few months ago. However I am planning to apply the patch for bug 31464691 due to other issues (mentioned in 2448781.1) tomorrow, I will see if it fixes this issue and let you know.

Thanks

On Tue, Jul 13, 2021 at 11:18 PM Fairlie Rego <fairlie.rego_at_gmail.com<mailto:fairlie.rego_at_gmail.com>> wrote: Isn't this the bug documented below

Statistics for All Tables Getting Stale With No Data Change in 12.2 (Doc ID 2448781.1)

  • Data has not changed but many objects have stale statistics Ta Fairlie

On Wed, Jul 14, 2021 at 4:08 AM Ls Cheng <exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>> wrote: Hi

I think so

SQL> SELECT DBMS_STATS.get_prefs('PUBLISH') DBMS_STATS_PREFS from dual;

DBMS_STATS_PREFS



TRUE Thanks

On Tue, Jul 13, 2021 at 5:49 PM Powell, Mark <mark.powell2_at_dxc.com<mailto:mark.powell2_at_dxc.com>> wrote: Are your statistics set to publish?

Mark Powell
Database Administration
(313) 592-5148



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Ls Cheng <exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>> Sent: Tuesday, July 13, 2021 8:37 AM
To: Oracle Mailinglist <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: DBMS_STATS.GATHER_DICTIONARY_STATS not gathering stale statistics

Hi all

I am running 19.10 8 nodes RAC database with Multitenant. I noticed that after running DBMS_STATS.GATHER_DICTIONARY_STATS in the PDB I still see tables with stale statistics under SYS. For example:

exec DBMS_STATS.GATHER_DICTIONARY_STATS

SQL> select count(*)
  2 from dba_tab_statistics
  3 where STALE_STATS = 'YES'
  4 and owner = 'SYS';

  COUNT(*)


       305

Anyone seen this behaviour?

TIA
--

Fairlie Rego
Executive Database Architect
www.technoconsulting.com.au<https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.technoconsulting.com.au%2F&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C114bec41b2704cc873b908d946d755e1%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637618713974724938%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=vJJ2cRs3HYq1XTQ7nJNP7ym2ZhNUjhxBgtBoYg8rCuI%3D&reserved=0> http://www.linkedin.com/in/fairlierego<https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.linkedin.com%2Fin%2Ffairlierego&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C114bec41b2704cc873b908d946d755e1%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637618713974734893%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=mhyFHmglK4pfb8knrrmKL9RaOyMMvs6EfU2%2FIwm0yrA%3D&reserved=0> https://fairlierego.wordpress.com/<https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ffairlierego.wordpress.com%2F&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C114bec41b2704cc873b908d946d755e1%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637618713974744857%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=MRf9rJvqjIBRagUBfCGU1XmySIruQ9Ms4dpmZBOcEow%3D&reserved=0>

Twitter _at_fairlierego

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 14 2021 - 17:01:14 CEST

Original text of this message