Re: Hint is not used in SQL

Thanks for that.

The set of hints you showed in the latest patch do look suitable - though some of the outline, outline_leaf and merge hints MIGHT be redundant.

The key details that give the "big picture" of the plan were the unnest(), the leading() and use_nl() - but maybe some of the merge() and outline_leaf hints were also necessary to make sure that Oracle reached the right query block names that could make the hints (as specified) become relevant.

Regards
Jonathan Lewis

On Wed, 22 Nov 2023 at 21:22, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Jonathan,
>
> I have enclosed the patch and the new plan for your review. Please let me
> know if you need any other info from my side.
>
> Best Regards,
> AMIT
>
>
> On Wed, Nov 22, 2023 at 2:59 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> If you've managed to sort out hints that get the path you want it would
>> be rather nice if you showed us the final solution, viz: section of SQL
>> with the hints in place, execution plan including Outline Data (and notes
>> so that we can see that there aren't further hints in an SQL Patch etc.)
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Thu, 16 Nov 2023 at 17:49, Amit Saroha <eramitsaroha_at_gmail.com> wrote:
>>
>>> Hi Timur and Jonathan,
>>>
>>> After adding HINTs one at a time, I was able to create the proper plan
>>> as Timur showed with a faster execution time. Thank you for all of your
>>> assistance and advice in addressing my problem.
>>>
>>> Could you help me understand why my system has a bad plan and whether I
>>> need to reorganize my tables and rebuild my indexes?
>>>
>>> Best Regards,
>>> AMIT
>>>
>>>
>>> On Thu, Nov 16, 2023 at 11:41 AM Amit Saroha <eramitsaroha_at_gmail.com>
>>> wrote:
>>>
>>>> Hi Timur,
>>>>
>>>> I tried all of the hints, but nothing changed in the plan. According to
>>>> the included report, it appears that some HINTS fail and I couldn't figure
>>>> out why they failed even after several attempts.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> *Hint Report (identified by operation id / Query Block Name / Object
>>>> Alias):Total hints for statement: 16 (E - Syntax error
>>>> (3))---------------------------------------------------------------------------
>>>> 0 - SEL$11 - MERGE(_at_SEL$11 >SEL$10) 0 - SEL$14 -
>>>> MERGE(_at_SEL$14 >SEL$13) 0 - SEL$17 - MERGE(_at_SEL$17
>>>> >SEL$16) 0 - SEL$1CF66C63 - MERGE(_at_SEL$1CF66C63 >SEL$12)
>>>> 0 - SEL$2 - MERGE(_at_SEL$2 >SEL$1) 0 - SEL$285A8194
>>>> - MERGE(_at_SEL$285A8194 >SEL$9) 0 - SEL$5 - MERGE(_at_SEL$5
>>>> >SEL$4) 0 - SEL$7286615E - MERGE(_at_SEL$7286615E >SEL$3) 0
>>>> - SEL$8 - MERGE(_at_SEL$8 >SEL$7) 0 - SEL$8A3193DA -
>>>> MERGE(_at_SEL$8A3193DA >SEL$6) 0 - SEL$97CCBC9E -
>>>> MERGE(_at_SEL$97CCBC9E >SEL$15) 0 - SEL$F5BB74E1 -
>>>> MERGE(_at_SEL$F5BB74E1 >UPD$1) 0 - UPD$1 E - OU E -
>>>> TLINE E - X_RS_ASC 5 - SET$FCA7A018 -
>>>> UNNEST(_at_SET$1 UNNEST_INNERJ_DISTINCT_VIEW) Note----- - SQL patch
>>>> "7wh1j104vstdg" used for this statement*
>>>>
>>>> Best Regards,
>>>> AMIT
>>>>
>>>>
>>>> On Thu, Nov 16, 2023 at 4:18 AM Timur Akhmadeev <
>>>> timur.akhmadeev_at_gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I've the following plan in my ebs db:
>>>>> https://pastebin.com/raw/2z3Kh7sj
>>>>> Can you try using all the hints from the outline and see how the plan
>>>>> in your db looks like?
>>>>>
>>>>> On Wed, Nov 15, 2023 at 1:52 AM Amit Saroha <eramitsaroha_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi All,
>>>>>>
>>>>>> I have included a tiny update statement and plan in which I am
>>>>>> attempting to include two hints; unfortunately, no matter what I try, the
>>>>>> hints are never used.
>>>>>>
>>>>>> Please have a look and let me know what I'm missing and why hints
>>>>>> aren't being used.
>>>>>>
>>>>>> Thank you in advance for all of your feedback.
>>>>>>
>>>>>> Best Regards,
>>>>>> AMIT
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards
>>>>> Timur Akhmadeev
>>>>>
>>>>

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