Re: Nested loop cost looks too high on 19c

I picked three historical partition and checked the low_value and high_value for them from both the 11.2 and 19C DB. They are as below. Definitely its not same for each partition buit kind of incremental number. But in that case as you said the num_disttinct should be close to the sum(num_distinct) of all the partitions in both the cases comes ~60billion+ , but that figure is not coming in the global stats even in the 11.2 scenario too. So it may be going for some evaluation of algorithm which is more sophisticated in case of 19C making the num_distinct(368Million) to be at lower side as compared to 11.2(having num_distinct ~23billion). So wondering if its fine to just update the num_distinct/density on 19C version manually at global level to same value as that of 11.2 or will it cause any issue going forward?

11.2

PARTITION_NAME SAMPLE_SIZE NUM_ROWS NUM_DISTINCT low_value  high_value

TAB_PART_11032021_P 313505087 313505087 311689216 1189322939301 1189838022300

TAB_PART_11042021_P 326245686 326245686 324141056 1189801044801 1190335034361

TAB_PART_11052021_P 327734535 327734535 324337664 1190297838801 1190834547967

19C

PARTITION_NAME SAMPLE_SIZE NUM_ROWS NUM_DISTINCT low_value  high_value

TAB_PART_07062021_P 292667212 292667212 291307520 1134788213301 1135271453334

TAB_PART_07072021_P 288537592 288537592 288537592 1135234053801 1135711940348

TAB_PART_07082021_P 284530356 284530356 282689536 1135674726801 1136148323348

And also regarding the stats gather , its a daily range partitioned table and we do have stats gather runs daily at multiple times of the day and whatever data populated during that time updated against that partitions and some data (Very minimal) may have also inserted into the partition post that stats gather too.

Say for e.g for 31may partition we have stats gathered at below times on that partition. And also we have 19C one is test database which is having same volume and data as that of production. And 11.2 one is the real production. I will double check if the stats gather jobs are running on the test system on same frequency as its on production.

5/31/2022 9:40:13.675295 PM -04:00

5/31/2022 6:42:26.309756 PM -04:00

5/31/2022 3:38:44.966285 PM -04:00

5/31/2022 12:37:42.326791 PM -04:00

5/31/2022 9:35:06.532140 AM -04:00

5/31/2022 6:35:47.175729 AM -04:00

5/31/2022 2:42:00.463501 AM -04:00

5/31/2022 12:11:57.200724 AM -04:00

5/30/2022 9:41:17.624775 PM -04:00

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