RE: Slow Update

Is this database dedicated to EBS?  

IF so, and since it has already been noted that a setting is not in keeping with EBS recommendations, that is suspicious.  

Quite often when this happens, someone who is a general Oracle practitioner has slapped a global parameter change in to fix some other problem.  

Probably a useful thing to do is go to your patch and upgrade full testing database and put in all the EBS recommendations, purge workflow if you are not already doing that routinely, run all the EBS recommended stats collections, and do a test deletion.  

IF that fixes the problem, then run your regression performance and correctness tests to see if something pops up as nasty.  

Also check your dba changes notebook to see if anyone noted the reason why the parameter was changed from the standard EBS recommendations to begin with.  

Good luck.  

Probably all the good stuff JL and others mentioned will fix this particular problem.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, November 03, 2023 11:10 AM To: ORACLE-L (oracle-l_at_freelists.org) Subject: Re: Slow Update  

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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_dwl.be> 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

--
http://www.freelists.org/webpage/oracle-l