Thank you, Jonathan, for your help and amazing inputs.
Best Regards,
AMIT
On Fri, Nov 3, 2023 at 3:29 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
And another detail that you generally have to do in cases where you change
from use_hash() to use_nl().
Find the line that is the full tablescan hint for the second table and
change it to be an index() hint with the correct index.
I don't have time right now to get review the outline information and
quote exactly what you need here.
Regards
Jonathan Lewis
On Fri, 3 Nov 2023 at 15:48, Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Hi Jonathan,
I am grateful for the insightful comments. I'm going to open an Oracle
SR. Before closing the thread, if it can be helpful in the interim, could
you kindly advise on the correct hint to use, as indicated below, which I
can implement using the SQL_PATCH you previously suggested?
declare
v1 varchar2(128);
begin
v1 := sys.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
sql_id => '1fw8tsdwvmg0g',
name => '1fw8tsdwvmg0g',
hint_text => '*USE_NL(VW_NSO_1@SEL$9C09E64D
POD@UPD$1*)'
);
dbms_output.put_line(v1);
end;
/
Best Regards,
AMIT SAROHA
On Fri, Nov 3, 2023 at 11:10 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Just re-ran the test from the blog note on 23.3, and it's fixed. (Still
wrong on 19.11)
Fix control 27982637 appeared labelled as 23.1; there's no matching bug
or patch visible on MOS, but you could try asking about a backport.
Regards
Jonathan Lewis
On Fri, 3 Nov 2023 at 13:46, Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Hello All,
I appreciate your input.
After looking at the statistics, it appears that something is wrong
with them. For example, the PO_RELEASE_ID is inaccurate in a number of the
columns. Additionally, as this is the standard Oracle EBS code for PO line
cancellation, we are unable to alter it.
I'll gather the stats and try again, but I'd appreciate it if you could
look at the attached file and offer some suggestions if stats have to be
gathered in any specific way. Since it is their standard code, I might also
need to open an Oracle SR. However, this could take some time, and the user
would continue to suffer, so any workaround solution would be very
appreciated.
Best Regards,
AMIT
On Fri, Nov 3, 2023 at 8:42 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
You're right. And that's why it didn't switch. (Though that's not a
good enough reason to change the global setting just for this query, of
course.)
I scanned the list three times and still managed to miss it. Now, if
it had been an underscore !!
Regards
Jonathan Lewis
On Fri, 3 Nov 2023 at 11:49, Timur Akhmadeev <
timur.akhmadeev@xxxxxxxxx> wrote:
There's optimizer_adaptive_reporting_only=true which explains lack of
adaptive execution. It's not a recommended eBS setting BTW.
On Fri, Nov 3, 2023 at 2:38 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
I didn't say "swap the stats" I said "swap some details of the
stats". It's a fine distinction, but I certainly wouldn't expect Amit to
ask for and import a full metadata export with stats of Jo's table.
Don't forget that the plan is set by the optimizer's best guess of
what's going to happen at run time, so it's fairly irrelevant that one
of
the components of the guesswork didn't execute at all. However, it
feels a
little suprising that adaptive execution didn't come into play to switch
the hash join to a nested loop join (it is 19c, after all) when the
vw_nso_1 completed and acquired so little data. (I can't see any place
in
the output where we could find out if adaptive execution had been
disabled
- I thought it would show in the optimizer environment, but there's no
mention there. Maybe there's a restriction on adaptive execution when
there are "conditional" subqueries in play.)
Regards
Jonathan Lewis
On Fri, 3 Nov 2023 at 11:13, Timur Akhmadeev <
timur.akhmadeev@xxxxxxxxx> wrote:
I think swapping stats is not a good idea since the data is
different - things like ORG_ID at least - which will impact estimates
with
different inputs in different DB. Changing
num_distinct/num_rows/similar
things might be OK.
It's best to gather stats on the table and indexes and see if the
plan changes - perhaps the index/table stats are not in sync and
causing
this bug.
Also I find it funny that lines 15-17 are not even executed because
of the conditional FILTER operation, yet causing misestimates and wrong
choice of the join method.
On Fri, Nov 3, 2023 at 1:02 PM Jonathan Lewis <
jlewisoracle@xxxxxxxxx> wrote:
There are times when I get to feel really smug!
Maybe you could swap some details of the stats on that table, its
indexes and the column in the critical predicates with Amit to help
identify why the silly cardinality estimate has appeared.
Regards (and thanks)
Jonathan Lewis
On Fri, 3 Nov 2023 at 08:23, Holvoet Jo <J.Holvoet@xxxxxx> wrote:
I can find that exact same sql_id in our Oracle EBS db as well,
and our execution plan is basically doing what Jonathan says : a NL
join
between VW_NSO_1 and a unique index scan on PO_LINE_LOCATIONS_U1
instead of
your hash join with a full table scan. We end up with a cost of 6
for the
update and execution times in the centisecond range (although here
it is
hardly ever executed – at most once every few days).
Regards
Jo
--
Regards
Timur Akhmadeev
--
Regards
Timur Akhmadeev
