Re: Slow Update

Not easy to read all that output, but I think I've spotted one possibility. You have a column group on (org_id, po_release_id), but the po_release_id is always null in your case and the optimizer is going to produce a silly answer for the number of distinct values for the combination - reporting only the number of distinct values of ord_id. I wrote about this a few years ago: Column Groups | Oracle Scratchpad (wordpress.com) <https://jonathanlewis.wordpress.com/2018/10/22/column-groups-5/>

So you've got an index which is completely empty (hence the index cardinality of 1), and a table with 4 distinct combinations - hence the very large cardinality estimate on the table access. (You've got 9,3M rows, the estimate looks like roughly 9.3M / 4.

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