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@xxxxxxxxx> 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@xxxxxxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Pierre Labrousse <Pierre.Labrousse@xxxxxxxxxx>
*Sent:* 15 November 2023 08:36
*To:* ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>;
eramitsaroha@xxxxxxxxx <eramitsaroha@xxxxxxxxx>
*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>**@
<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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> de
la part de Amit Saroha <eramitsaroha@xxxxxxxxx>
*Envoyé :* mardi 14 novembre 2023 23:50
*À :* ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
*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
