Re: Slow Update

And another detail that you generally have to do in cases where you change from use_hash() to use_nl().
Find the line that is the full tablescan hint for the second table and change it to be an index() hint with the correct index.

I don't have time right now to get review the outline information and quote exactly what you need here.

Regards
Jonathan Lewis

On Fri, 3 Nov 2023 at 15:48, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Jonathan,
>
> I am grateful for the insightful comments. I'm going to open an Oracle SR.
> Before closing the thread, if it can be helpful in the interim, could you
> kindly advise on the correct hint to use, as indicated below, which I can
> implement using the SQL_PATCH you previously suggested?
>
> declare
> v1 varchar2(128);
> begin
> v1 := sys.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
> sql_id => '1fw8tsdwvmg0g',
> name => '1fw8tsdwvmg0g',
> hint_text => '*USE_NL(VW_NSO_1_at_SEL$9C09E64D
> POD_at_UPD$1*)'
> );
> dbms_output.put_line(v1);
> end;
> /
>
> Best Regards,
> AMIT SAROHA
>
>
> On Fri, Nov 3, 2023 at 11:10 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> Just re-ran the test from the blog note on 23.3, and it's fixed. (Still
>> wrong on 19.11)
>> Fix control 27982637 appeared labelled as 23.1; there's no matching bug
>> or patch visible on MOS, but you could try asking about a backport.
>>
>> 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