Re: Hint is not used in SQL
Date: Wed, 15 Nov 2023 11:25:12 +0000
Message-ID: <DBAPR02MB64706E37E7B97AB2E8A9F794A1B1A_at_DBAPR02MB6470.eurprd02.prod.outlook.com>
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.
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
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,
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
Consultant DBA ORACLE (OCM 10g/11g/12c)
Mobile +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
AMIT
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 15 2023 - 12:25:12 CET