Re: Hint is not used in SQL

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