Re: Hint is not used in SQL

From: Amit Saroha <eramitsaroha_at_gmail.com>
Date: Wed, 15 Nov 2023 10:03:22 -0500
Message-ID: <CAG67e6SRWWk06fdCNapU=m_dxx=S8ONgz8VxSsffw68OH7xYsA_at_mail.gmail.com>

Hi Jonathan,

Thank you for your response.

I'm not utilizing the hints in the query again; they were just there to display the update statement. The hints are in the patch. The statement used to generate the patch and the result of the hint reports are confined. I'm reading through your blog posts to try to understand the behavior, but I'm still not sure why Oracle chose to FTS the table rather than the view first, I'm using the on-premises EBS system.

*select * from
dbms_xplan.display_cursor('7wh1j104vstdg',format=>'+HINT_REPORT');*

*Hint Report (identified by operation id / Query Block Name / Object Alias):Total hints for statement: 2 (U - Unused (1), N - Unresolved

(1))---------------------------------------------------------------------------
 3 -  SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL_at_SEL$2         U -
 USE_NL_WITH_INDEX(_at_SEL$AC90CD92  PO_LINE_LOCATIONS_ALL_at_SEL$2)    5 -
 SET$632CC7ED         N -  LEADING(_at_SET$632CC7ED VW_NSO_1_at_SEL$AC90CD92

PO_LINE_LOCATIONS_ALL_at_SEL$2) Note----- - SQL patch "7wh1j104vstdg" used for this statement*

Best Regards,
AMIT On Wed, Nov 15, 2023 at 9:22 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> 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




text/plain attachment: hint_report.txt



text/plain attachment: patch.txt

Received on Wed Nov 15 2023 - 16:03:22 CET