Nested loop cost looks too high on 19c
From: Pap <oracle.developer35_at_gmail.com>
Date: Mon, 30 May 2022 20:41:28 +0530
Message-ID: <CAEjw_fgYi3DDKyNeLQCcj_6fv-k0GESSy_8CS_aoc_-t1F4D9Q_at_mail.gmail.com>
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?
Date: Mon, 30 May 2022 20:41:28 +0530
Message-ID: <CAEjw_fgYi3DDKyNeLQCcj_6fv-k0GESSy_8CS_aoc_-t1F4D9Q_at_mail.gmail.com>
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-lReceived on Mon May 30 2022 - 17:11:28 CEST