Re: Nested loop cost looks too high on 19c

If the partition stats for 11g and 19c look very similar (and you're not using incremental stats with synopses) then all it takes to get a significant change in the global num_distinct is a change in the algorithm that Oracle uses to derive table num_distinct from partition num_distinct. I don't know if this has happened, but you could create a small model and test the effects. Having 20% of your partitions empty may also change the algorithm so include that in the model.

Another detail to check before you build a model is the variation in low_value and high_value between the versions, and between partitions. Oracle tended to over-estimate the number of distinct values when combining partitions, which is why I wasn't too surprised to a drop in the num_distinct for 19c; but the way in which estimate is derived may depend heavily on how much the overlap of tx_id is between partitions.

(Obviously if the low_value is pretty much the same and the high_value pretty much the same in all partitions then the number of distinct values in the table is quite likely to be similar to the number of distinct values in a single partition; on the other hand if the low_value of each partition is higher than the high value of the previous partition then the global num_distinct is going to be more like the sum of the num_distinct across all the partitions. Your case may be somewhere between those two extremes, and it may be handled differently in the two versions)

Regards
Jonathan Lewis

On Tue, 31 May 2022 at 21:21, Pap <oracle.developer35_at_gmail.com> wrote:

> I do not see any entry in sys.wri$_optstat_synopsis$ for that object,
> which means the stats gathering is not happening as incremental. Also
> checked the dba_tab_stat_prefs, they are exactly the same in both the
> databases with granularity as PARTITION, estimate_percent as
> 'DBMS_STATS.AUTO_SAMPLE_SIZE'. I see stale_percent set as '10' in table
> level preference, but I hope that won't play a role here because global
> default is also 10% belief.
>
> I tried fetching the sample_size, num_rows, num_distinct for column TX_ID
> using below query side by side for each partition from both the
> environments, i am not seeing any such anomaly. I pasted the results in the
> link below. On 11.2 versions out of total ~267 partitions ~210 were non
> zero partitions and on 19c out of total ~266 partitions ~211 were non zero
> partitions. So in both ~55 partitions hold zero rows as they are future
> partitions, so that should not cause this big deviation in both the
> databases. Am I missing anything here?
>
> https://gist.github.com/oracle9999/28e991d0069befcf7d4d77f5b8e0420a
>
> select dtp.table_name, dtp.partition_name,dtp.sample_size, dtp.num_rows,
> dpcs.num_distinct from dba_tab_partitions dtp, dba_part_col_statistics dpcs
>
> where dtp.table_name='TAB_PART' and dtp.table_name=dpcs.table_name and
> dtp.partition_name= dpcs.partition_name
>
> and dpcs.column_name='TX_ID' and dtp.table_owner= dpcs.owner
>
> order by sample_size asc
>
>
> On Wed, Jun 1, 2022 at 12:53 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> You are correct - that global num_distinct is driving the difference in
>> the arithmetic
>>
>> Your next step would be to check the sample_size reported by the two
>> systems. One may have been using a small sample while the other was using
>> the approximate_ndv with 100% sample. Then check if you're using
>> incremental statistics (and how it's configured) in both systems. If you're
>> using incremental in 19c but not in 11g then the num_distinct in 11g would
>> almost inevitably be larger than that in 19c which would be using synopses
>> to get a better global
>> num_distinct. Then check rows and num_distinct partition by partition.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Tue, 31 May 2022 at 19:37, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Thank You Jonathan.
>>>
>>> The line no-3 in the plan is the join between table TAB_PART and TSFS
>>> and the estimation
>>>
>>> on 11.2 is coming as 3(cardinality of TAB_PART)*154(cardinality of TSFS)
>>> =462 which is close to ~455
>>>
>>> however on 19c its 176(cardinality of TAB_PART)*209(cardinality of
>>> TSFS)= 36784 which is close to ~36692
>>>
>>> So here the key factor which is influencing the figure drastically is
>>> cardinality estimation of TAB_PART i.e 3 in 11.2 vs 176 in 19C and which is
>>> determined by the predicate "TAB_PART.TX_ID=TO_NUMBER(:B2)". Here TAB_PART
>>> is a range partitioned table and on this table stats is being gathered on
>>> individual partitions which is getting rolled up by oracle itself. We don't
>>> use incremental stats collection yet on this table. That is why the
>>> global_stats column is showing as 'NO'.
>>>
>>> Now comparing the table and column statistics of this between 11.2 and
>>> 19c databases below, the cardinality = density*num_rows, so here the
>>> difference in num_distinct/density for this column at the global level is
>>> playing a key role. I am sure the data pattern of column TX_ID is closely
>>> the same in both , so I was wondering why there is such a big difference in
>>> calculation of num_distinct/density by stats gathering process in both the
>>> versions? Or is there really a difference in how the num_distinct/density
>>> gets rolled up to global level from individual partitions in these two
>>> versions and thus moving to 19C with this may impact other queries too?
>>>
>>> *ON 11.2:- *
>>>
>>> *from dba_tables:- *
>>>
>>>
>>> *TABLE_NAME NUM_ROWS BLOCKS*
>>>
>>> TAB_PART 68889040371 1132560197
>>>
>>> *from dba_tab_col_statistics:- *
>>>
>>>
>>> *TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY
>>> NUM_NULLS HISTOGRAM GLOBAL_STATS DENSITY*68889040371*
>>>
>>> TAB_PART TX_ID 23151767844 4.3193245834968E-11 0
>>> NONE NO 2.97554125607964
>>>
>>> *ON 19C:- *
>>>
>>> *from dba_tables:- *
>>>
>>> *TABLE_NAME NUM_ROWS BLOCKS*
>>>
>>> TAB_PART 64791566202 1055609782
>>>
>>> *from dba_tab_col_statistics:-*
>>>
>>>
>>> *TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY
>>> NUM_NULLS HISTOGRAM GLOBAL_STATS DENSITY*64791566202*
>>>
>>> TAB_PART TX_ID 368368793 2.71467078374362E-9
>>> 0 NONE NO 175.88777180156
>>>
>>>
>>> On Tue, May 31, 2022 at 10:23 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>> In the 11g plan:
>>>> Operation 2 has a cost of 768K because operation 3 is telling it that
>>>> it will call operation 10 455 times at a cost of 1683 each time.
>>>> 455 * 1683 = 765,765, then add a bit for the line itself using CPU.
>>>>
>>>> In the 19c hinted plan
>>>> Operation 2 has a cost of t1M because operation 3 is telling it that it
>>>> will call operation 10 36693 times at a cost of 1677 each time.
>>>> 36693 * 1677 = 61,534,161
>>>>
>>>> Thanks to self-caching and other details the multiplication isn't
>>>> always this close - but it's a good guideline in many cases.
>>>>
>>>> Your first step should be to work out why the cardinality estimate of
>>>> line 3 is so much higher.
>>>>
>>>> Regards
>>>> Jonathan Lewis
>>>>
>>>>
>>>>
>>>> On Mon, 30 May 2022 at 16:11, Pap <oracle.developer35_at_gmail.com> wrote:
>>>>
>>>>> Hello, While testing the behavior of queries on one of the 19.11.0.0.0
>>>>> database. Seeing the same query which running in quick time on version
>>>>> 11.2.0.4 is running longer on 19C with different path. But when forced the
>>>>> outline of the 11.2 path the cost comes higher in the 19C database , which
>>>>> i was expecting but not this much high it's 760k vs 61million . And when i
>>>>> checked the path the cost of nested loop operation is too high on 19C at
>>>>> plan_line_id-2. So wanted to understand if this is expected behavior or
>>>>> hitting any bug here?
>>>>>
>>>>> Below is the sql and i have added its execution path with version
>>>>> 11.2, 19c and forced 11.2 path on 19c database. Also i have added the sql
>>>>> monitor for each of those in below location.
>>>>>
>>>>> https://gist.github.com/oracle9999/69651125d9a3942e8fb261669611e7aa
>>>>>
>>>>>
>>>>> select *
>>>>>
>>>>> FROM TAB_PART TAB_PART, FEES fees
>>>>>
>>>>> WHERE fees.SB_ID IN (SELECT DISTINCT RS_ID FROM TSFS WHERE OS_ID
>>>>> = TAB_PART.SB_ID)
>>>>>
>>>>> AND fees.B_STS = 'XXX'
>>>>>
>>>>> AND TAB_PART.SM_ID = fees.OB_ID
>>>>>
>>>>> AND TAB_PART.TX_ID = :b2;
>>>>>
>>>>

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