Re: Hint is not used in SQL

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