Re: Nested loop cost looks too high on 19c

>> And so I am thinking, how safe is it to just update the num_distinct at
the global level for that column to the same value as that of 11.2 system? Or will it be overridden when next partition gather happens.

Without knowing what has happened in the past, and exactly what calls to gather stats are taking place (including batch jobs and overnight jobs), and what preferences have been set, it's impossible to say why you've got a table where the global num_distinct is so far off the value that you would expect given the information you'vs shown so far. If you don't know why you're in the current position there's no way of knowing if, or when, Oracle would revert to that position if you tried to solve the problem by using an explicit call to dbms_stats.set column_stats - at worst it probably won't cause any harm in the short term, and the value might survive for a while before suddenly reverting (e.g. after a couple of weeks because the table stats have gone stale).

You can't really get any information from 11.2 about what's happening, but 19c has a couple of report options in dbms_stats to show you all the stats collection operations, and autostat job collection details, so you could review a little bit of the past to see if that tells you how the calls are made. See: https://jonathanlewis.wordpress.com/2018/09/10/stats-time-2/

Regards
Jonathan Lewis

On Wed, 1 Jun 2022 at 09:59, Pap <oracle.developer35_at_gmail.com> wrote:

> Yes Jonathan, as I had just replied couple of minutes ago it's a 24/7 data
> load table and stats gather happens manually throughout the scheduled job.
> But the test system is the one which has been restored from production
> only. But I doubt that stats gather job is really running there as because
> data load is not happening in that system 24/7 as it's in production. And
> so ideally statistics should not change/differ untill someone has manually
> gathered and 19c algo took effect on the num_distinct value. And so I am
> thinking, how safe is it to just update the num_distinct at the global
> level for that column to the same value as that of 11.2 system? Or will it
> be overridden when next partition gather happens.
>
> On Wed, 1 Jun 2022, 12:39 pm Jonathan Lewis, <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Sorry,
>> I forget you'd already said granularity=>'PARTITION'
>> Does that mean you have a procedure that populates a partition then
>> gathers stats (with a trickle of change thereafter)?
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Tue, 31 May 2022 at 23:47, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> One other thing to check on gathering stats - what was the setting for
>>> the granularity paramter?
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>

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