Re: Slow Update

Thank you, Jonathan, for your help and amazing inputs.

Best Regards,
AMIT On Fri, Nov 3, 2023 at 3:29 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> 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