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;
