RE: Hint is not used in SQL
Date: Wed, 15 Nov 2023 08:36:32 +0000
Message-ID: <PR1P264MB1824FFEEC3B08998A01E8176F6B1A_at_PR1P264MB1824.FRAP264.PROD.OUTLOOK.COM>
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)
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
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-lReceived on Wed Nov 15 2023 - 09:36:32 CET