Re: DBMS_STATS.GATHER_DICTIONARY_STATS not gathering stale statistics

  • From: Ls Cheng <exriscer@xxxxxxxxx>
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>
  • Date: Wed, 14 Jul 2021 20:09:22 +0200

Under SYS schema, I don't think so but I will have a look tomorrow

On Wed, Jul 14, 2021 at 6:39 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:

outside possibility: Check that you don’t have anything creating and
dropping objects in such large quantities that it quickly makes the stats
stale?



*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Ls Cheng
*Sent:* Wednesday, July 14, 2021 11:50 AM
*To:* Beckstrom, Jeffrey
*Cc:* Fairlie Rego; Powell, Mark; Oracle Mailinglist
*Subject:* 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@xxxxxxxxx>
wrote:

What happens if you run:             dbms_stats.gather_fixed_objects_stats;



*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Ls Cheng
*Sent:* Wednesday, July 14, 2021 10:55 AM
*To:* Fairlie Rego <fairlie.rego@xxxxxxxxx>
*Cc:* Powell, Mark <mark.powell2@xxxxxxx>; Oracle Mailinglist <
oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxx> wrote:

Are your statistics set to publish?



Mark Powell

Database Administration

(313) 592-5148




------------------------------

*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Ls Cheng <exriscer@xxxxxxxxx>
*Sent:* Tuesday, July 13, 2021 8:37 AM
*To:* Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
*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 @fairlierego


Other related posts: