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
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Jonathan Lewis
Sent: Thursday, November 2, 2023 10:31 PM
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Slow Update
The optimizer has unnested the "union all" IN subquery - which is probably a
very good idea since you're probably expecting to update only a few items
(normally).
The problem is that it's then chosen a full tablescan and hash join to get back
to the po_line_locations_all table when it probably should have done a nested
loop join using an index on line_location_id.
The error is due to a massive cardinality estimate that appears at operation 16
(2,337K) from an estimate 1 index entry at operation 1. That suggests a stats
problem with that specific index - is the release_id a column with a very
skewed distribution?
If you can fix that bit of the problem you should see the nested loop and
suitable performance - if not you'll have to hint the code into a nested loop
join (possibly with an SQL Patch).
Regards
Jonathan Lewis
On Thu, 2 Nov 2023 at 19:38, Amit Saroha
<eramitsaroha@xxxxxxxxx<mailto:eramitsaroha@xxxxxxxxx>> wrote:
Hi All,
We have an issue where users are taking a long time to cancel a purchase order
line. Using AH data, I was able to identify one of the queries that was taking
a while in this procedure. I've attached the monitoring report for your
perusal, and I ask that you let me know if you have any suggestions on how to
make the query run better.
I appreciate your support and assistance in advance.
Best Regards,
AMIT
DE WITTE LIETAER INTERNATIONAL TEXTILES nv
Postadres: Koningin Astridlaan 48 | B-8930 Lauwe
Bezoekadres: Julien Cagniestraat 24 | B-8930 Lauwe
Ondernemingsnummer : BE 0878.742.103
RPR KORTRIJK
--------------------------------------------------------------------------------
**** DISCLAIMER ****
This e-mail and any attachment thereto may contain information which is
confidential and/or protected by intellectual property
rights and are intended for the sole use of the recipient(s) named above. Any
use of the information contained herein
(including, but not limited to, total or partial reproduction, communication or
distribution in any form) by other persons than
the designated recipient(s) is prohibited. The content of this mail is
professional in nature. Should you not agree with its
professional character, you need to send it back to helpdesk@xxxxxx. If you
have received this e-mail in error, please notify
the sender either by telephone or by e-mail and delete the material from any
computer.
This e-mail message has been scanned and cleared by Trustwave M86 MailMarshal
--------------------------------------------------------------------------------
