Re: Stats collection issue in 19C

Thank you so much. Got your point.

You are correct. The application is having these table range partitioned and when the transition happen from one of the monthly/weekly range partition to new one and the stats is not collected immediately or the stats is not representative then the query suffers and to tackle this issue dev team has build a job which keep on checking the zero stats in all the future partitions in each few minutes interval and then copy the stats from the MAX num zero partition to all the future partition using " copy_table_stats" something as below.

dbms_stats.copy_table_stats('USER1','TAB1','P_18OCT2022','P_25OCT2022', 1, , false);

But yes this current incremental stats gathering(which runs twice each day) makes all of the future partitions zero and the copy table stats process again makes it non zero. There is some kind of overlap going on. But the same is happening in other databases too in which the incremental stats work fine without gathering the global column stats scanning all partitions. So we may need to look deeper into it to find the cause of this or say, as you suggested earlier the complete delete + gather with 'approx global and partition' may work.

On Fri, Nov 11, 2022 at 3:56 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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