Re: Slow Update

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

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