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

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