Re: Nested loop cost looks too high on 19c

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