Re: Nested loop cost looks too high on 19c

If I were you I'd also check every single set of partition stats in case a couple of them had anomalous low/high values, or other values that didn't follow the pattern of the rest of the partitions (e.g. very low number of num_distinct or num_rows).

On Thu, 2 Jun 2022 at 11:37, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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