Re: Slow Update

I should also have said that I wouldn't collect stats on the table and its indexes until I have some idea of what was wrong. It is highly likely that there is a statistical problem, but it's best to know what it is before trying to fix it - especially since it probably involves at most one index and a couple of columns in the table.

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
>

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