Re: Hint is not used in SQL

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




text/plain attachment: plan.txt



application/octet-stream attachment: 7wh1j104vstdg.sql