Re: Hint is not used in SQL

I was going to point out that you have an SQL Patch that has been applied which might be causing the problem.

Although you've said it mirrors the hints you used, I would be interested in see you check that, since (without running a test on the version and checking it myself) I would have expected the hint report to add "Duplicate Hint" in the hint report if the patch and the query held the same hints.

As Pierre says, if you add "+hint_report" to the call to display_cursor() you will get the full hint report (not just the "hint_report_unused" bit; and that might shed a little extra light.

Are you running this locally, or running it on a test database in the cloud - I think the autonomous cloud d/w is configured with the "disable hints" parameter set (I can't remember the exact name. Might that be relevant.

Regards
Jonathan Lewis

On Wed, 15 Nov 2023 at 11:38, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Dominic,
>
> Actually I have created this patch and these are the two hints in the
> patch and in the hint report section I can see they are not used.
>
> How I can figure out why these are not used and there is any other hint I
> can use to ensure correct join order and nested loop.
>
> Thank you.
>
> On Wed, Nov 15, 2023 at 6:25 AM Dominic Brooks <dombrooks_at_hotmail.com>
> wrote:
>
>> It depends on what is in your SQL Patch.
>> Really we need to see what hintset is in that patch.
>>
>> A SQL Patch may be created from a set of outline hints as seen in your
>> execution plan and if that includes IGNORE_OPTIM_EMBEDDED_HINTS then
>> anything else in the original SQL should be ignored.
>>
>>
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Pierre Labrousse <Pierre.Labrousse_at_digora.com>
>> *Sent:* 15 November 2023 08:36
>> *To:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>;
>> eramitsaroha_at_gmail.com <eramitsaroha_at_gmail.com>
>> *Subject:* RE: Hint is not used in SQL
>>
>> Hello Amit,
>>
>> Your query used a SQL Patch (SQL patch "7wh1j104vstdg" used for this
>> statement), and as SQL profile they have a higher priority to hint. So you
>> have to put your hints into the SQL patch.
>> If you use 19c database you can generate a report to display the reason
>> of not using hint with dbms_xplan :
>>
>> select * from TABLE(dbms_xplan.display_cursor(format=>'BASIC +
>> *HINT_REPORT*'));
>>
>> Pierre
>>
>>
>>
>> *Pierre **LABROUSSE*
>> * Consultant DBA ORACLE (OCM 10g/11g/12c)*
>> *M*obile +33 (0)7 56 05 27 38
>> *pierre.labrousse
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>**_at_
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>**digora.co
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>m
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com>*
>>
>> ------------------------------
>> *De :* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> de
>> la part de Amit Saroha <eramitsaroha_at_gmail.com>
>> *Envoyé :* mardi 14 novembre 2023 23:50
>> *À :* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>> *Objet :* Hint is not used in SQL
>>
>> 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
>>
>

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