RE: Slow Update
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_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Jonathan Lewis
Sent: Thursday, November 2, 2023 10:31 PM
To: ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
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_at_gmail.com<mailto:eramitsaroha_at_gmail.com>> 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
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_at_dwl.be. 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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 03 2023 - 09:23:28 CET
Original text of this message
