Re: Slow Update

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