Re: DBMS_STATS.GATHER_DICTIONARY_STATS not gathering stale statistics

I cannot run that, it takes 37 hours.

I tried this, looks better

SQL> exec dbms_stats.gather_schema_stats('SYS')

PL/SQL procedure successfully completed.

SQL> 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(*)
-------- ----------
20210714 11

so it seems to me the bug still exists despite having applied the patch

On Wed, Jul 14, 2021 at 5:01 PM Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org> wrote:

> 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> 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>
> 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> 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> wrote:
>
> Are your statistics set to publish?
>
>
>
> Mark Powell
>
> Database Administration
>
> (313) 592-5148
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Ls Cheng <exriscer_at_gmail.com>
> *Sent:* Tuesday, July 13, 2021 8:37 AM
> *To:* Oracle Mailinglist <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