Re: DBMS_STATS.GATHER_DICTIONARY_STATS not gathering stale statistics

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
> http://www.linkedin.com/in/fairlierego
> https://fairlierego.wordpress.com/
>
> Twitter _at_fairlierego
>

--
http://www.freelists.org/webpage/oracle-l