Re: Slow Update

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