Re: Stats collection issue in 19C

My previous reply was supposed to go to Oracle-L, not just be a private email, so I'm replying back to this one to the list so that my previous response shows up at the end:

>> And in these partition table we notice , one of the job keep on checking
"zero row" partition in few minutes interval and copies stats from the MAX "non zero" partition to all the future partition those having "zero num_rows stats" and that is mostly in place to avoid bad plan for.

You comment is a little ambiguous: "one of the jobs" -- is this a built-in Oracle job or an in-house piece of code. It sounds very like a piece of code that could have been created in the 10g/11g timeline to work around problems of recently add (range-based) partitions which have been loaded with new data but have not had their stats collected (and Oracle eventually added some procedures to dbms_stats to help in this task). With newer versions of Oracle that may be redundant and may be the caused of the oddity you're seeing - maybe you just need to stop that job.

>> But i am still not able to understand , howcome copying stats on the
future "zero rows" partition can make oracle to go for making to collect global column level stats each time?

There is no reason why anyone (but the original Oracle developers) should understand why this happens. We do not know the algorthms; but we can make GUESSES. For example - Oracle may detect that the copied partition stats are not self-consistent because they don't include the necessary synopsis data, so Oracle can't update the table-level stats by combining the current stats with the partition stats that have magically appeared, so falls back to a full gather.

You may find that even after a complete refresh of stats on the current tables that this "copy forward" process is still being used it simply re-introduces the problem the next time these table acquire new partitions, so you need to test the idea carefully.

Regards
Jonathan Lewis

On Fri, 11 Nov 2022 at 08:59, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Jonathan for the details.
>
> We found the INCREMENTAL_STALENESS preference is set as
> "USE_STALE_PERCENT,USE_LOCKED_STATS" and the NOTES column in the
> dba_part_col_statistics were "blank/null" for the partitions those were
> having stats set as 'LOCKED' and are mostly historical partitions. Now that
> we gathered the stats on them with granularity=>partition, force=>true ,
> something as below and keeping other preferences remain(i.e. as below). The
> immediate post gather finish in quick time without collecting the global
> column stats using the sql query which we used to encounter in past.
>
> EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'USER1', TABNAME=>'TAB1',
> PARTNAME=>'P_24JAN2023', granularity=> 'PARTITION', force=> true, degree=>
> 8);
>
> PREFERENCE_NAME PREFERENCE_VALUE
>
> DEGREE 8
>
> GRANULARITY AUTO
>
> INCREMENTAL TRUE
>
> INCREMENTAL_STALENESS USE_STALE_PERCENT,USE_LOCKED_STATS
>
> METHOD_OPT FOR ALL COLUMNS SIZE REPEAT
>
> However, one thing i see for some other table, even the NOTES column is
> showing "hyperloglog" for all partitions but still its going for doing a
> global column stats gather scanning full table after sometimes. And in
> these partition table we notice , one of the job keep on checking "zero
> row" partition in few minutes interval and copies stats from the MAX "non
> zero" partition to all the future partition those having "zero num_rows
> stats" and that is mostly in place to avoid bad plan for. But i am still
> not able to understand , howcome copying stats on the future "zero rows"
> partition can make oracle to go for making to collect global column level
> stats each time? But anyway, as you said we are planning to do a full
> delete + gather using 'GLOBAL AND PARTITION' as below and see the behaviour.
>
> EXEC DBMS_STATS.DELETE_TABLE_STATS (OWNNAME => 'USER1', TABNAME => 'TAB1');
>
> exec
> DBMS_STATS.SET_TABLE_PREFS('USER1','TAB1','APPROXIMATE_NDV_ALGORITHM','HYPERLOGLOG');
> -- set at table level so that global "HYPERLOGLOG" will be in effect.
>
> EXEC dbms_stats.SET_TABLE_PREFS ('USER1','TAB1','incremental_staleness',
> 'NULL'); -- so that immediatekly it will collect hyperloglog for all.
>
> EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'user1', TABNAME => 'TAB1',
> GRANULARITY=> 'GLOBAL AND PARTITION', degree=> 16);
>
> exec
> DBMS_STATS.set_TABLE_PREFS('USER1','TAB1','INCREMENTAL_STALENESS','USE_STALE_PERCENT,USE_LOCKED_STATS')
> ; -- setting back the incremantal_staleness to as it was before.
>
> exec
> DBMS_STATS.delete_TABLE_PREFS('USER1','TAB1','APPROXIMATE_NDV_ALGORITHM') ;
> -- Delete at table level so that global "REPEAT OR HYPERLOGLOG" will be in
> effect.
>
> On Wed, 9 Nov, 2022, 7:22 pm Jonathan Lewis, <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Version by version you never really know what details Oracle has changed
>> in things like stats collection, and sometimes relatively small tests don't
>> reflect what happens in very large cases but, having said that, I would
>> first try gathering stats for the partitions which are not currently using
>> hyperloglog, using "PARTITION" as the granularity. That MIGHT be
>> sufficient to ensure that future gathers don't do the full table analysis.
>>
>> ONLY IF the full table analysis kept on after that would I delete all the
>> stats for that table and gather for the table with granularity 'GLOBAL AND
>> PARTITION' (which is actually what the DEFAULT is supposed to be) and the
>> NDV algorithm set to HYPERLOGLOG.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>

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