Re: Help with implementing SQL PATCH in Oracle 12.1

In theory yes but to be absolutely sure you need to baseline the good plan. Will this good plan be good enough for all binds?. Again I`ll advise you to check your stats.

Good luck!

În joi, 28 ian. 2021 la 10:19, Rakesh T <aryan.goti_at_gmail.com> a scris:

> Thanks Laurentiu. It worked. Will sql patch wok for different bind values
> as well?
>
> Thanks,
> Rakesh T
>
>
> On Thu, Jan 28, 2021 at 1:16 PM Laurentiu Oprea <
> laurentiu.oprea06_at_gmail.com> wrote:
>
>> It is a bit strange the way your hint influenced the plan, but anyway,
>> assuming oracle will nail all other instructions:
>>
>> declare
>> v_sql_text CLOB;
>> BEGIN
>> select sql_text into v_sql_text from dba_hist_sqltext where
>> sql_id='3vspnuzbn588y' and rownum=1;
>> sys.dbms_sqldiag_internal.i_create_patch(
>> sql_text => v_sql_text,
>> hint_text => 'LEADING(_at_"SEL$897A7E7E" "LO"_at_"SEL$1" "CM"@"SEL$9"
>> "EU"_at_"SEL$1" "AU"_at_"SEL$6" "E"@"SEL$2" "EAM"@"SEL$3" "AD"@"SEL$4"
>> "SU"_at_"SEL$5" "URM"_at_"SEL$7" "AR"@"SEL$8") USE_NL(@"SEL$897A7E7E"
>> "E"_at_"SEL$2") NLJ_BATCHING(_at_"SEL$897A7E7E"
>> "E"_at_"SEL$2") INDEX(_at_"SEL$897A7E7E" "E"@"SEL$2" ("FO_ENTITY"."ENTITYID"))
>> ',
>> name => 'tst_patch');
>> END;
>> /
>>
>> În joi, 28 ian. 2021 la 08:53, Rakesh T <aryan.goti_at_gmail.com> a scris:
>>
>>> Hi,
>>>
>>> Thanks a lot Laurentiu.
>>>
>>> I have attached the bad plan as well as the good plan.
>>>
>>> I have not tried the SQL plan baseline for this SQL. This is the first
>>> time I am trying SQL PATCH and would like to know how this would behave.
>>>
>>>
>>> Thanks,
>>> Rakesh T
>>>
>>>
>>> On Thu, Jan 28, 2021 at 11:54 AM Laurentiu Oprea <
>>> laurentiu.oprea06_at_gmail.com> wrote:
>>>
>>>> Hello Rakesh,
>>>>
>>>> The procedure you use to create the sql patch looks correct to me.
>>>>
>>>> For the hint:
>>>>
>>>> I see in your outline:
>>>> q'[LEADING(_at_"SEL$897A7E7E" "LO"_at_"SEL$1" "CM"@"SEL$9" "EU"@"SEL$1"
>>>> "AU"_at_"SEL$6" "E"_at_"SEL$2" "EAM"@"SEL$3" "AD"@"SEL$4" "SU"@"SEL$5"
>>>> "URM"_at_"SEL$7" "AR"_at_"SEL$8")]', -> this will be the order in which the
>>>> tables will be joined
>>>>
>>>> I see your hint is /*+ use_nl(E LO) */
>>>> -> your hint is not correct. According to your leading hint LO is the
>>>> driving table and looks to me that you want to have E as the driving table.
>>>> Your hint should be something like /*+leading(E) use_nl(lo)*/ but we need
>>>> to put it in the context of your query
>>>>
>>>> You mentioned that if you introduce that hint the query is executed in
>>>> the desired time, this means you can provide the outline of the good plan
>>>> as well so please attach that as well to make sure I provide you the
>>>> correct hints you need to add via sql patch.
>>>>
>>>> Did you consider baseline the good plan and import the baseline in the
>>>> environment where you have issues?
>>>>
>>>> All above were for quick fixes, question is why you have the bad plan ,
>>>> do you have up to date stats? A sql monitor report can help fully
>>>> understand your issue.
>>>>
>>>> Thanks.
>>>>
>>>> În joi, 28 ian. 2021 la 07:55, Rakesh T <aryan.goti_at_gmail.com> a scris:
>>>>
>>>>> Hi Listers,
>>>>>
>>>>> The DB version is 12.1.
>>>>>
>>>>> Application is having a sql query as below...
>>>>>
>>>>> WITH temp
>>>>> AS (
>>>>> SELECT /*+ use_nl(E LO) */
>>>>> EU.UserID IS NULL
>>>>> OR (
>>>>> EU.UserID IS NOT NULL
>>>>> AND EU.UserStatusCode <> 90011
>>>>> )
>>>>> )
>>>>> ......
>>>>>
>>>>> The above takes some 20 seconds to execute.
>>>>>
>>>>> When implementing the below hints, the SQL runs in 2seconds.
>>>>>
>>>>> WITH temp
>>>>> AS (
>>>>> SELECT */*+ use_nl(E LO) */* CASE
>>>>> WHEN (
>>>>> EU.UserID IS NULL
>>>>> OR (
>>>>> EU.UserID IS NOT NULL
>>>>> AND EU.UserStatusCode <> 90011
>>>>> )
>>>>> )
>>>>> ......
>>>>>
>>>>> Application cannot introduce the code change and hence I was thinking
>>>>> of implementing SQLPATCH for this SQL. The outline details for the SQL is
>>>>> attached. Can someone help me to understand how do we get the nested loops
>>>>> hinted via SQLPATCH?
>>>>>
>>>>> declare
>>>>> v_sql_text CLOB;
>>>>> BEGIN
>>>>> select sql_text into v_sql_text from dba_hist_sqltext where
>>>>> sql_id='3vspnuzbn588y' and rownum=1;
>>>>> sys.dbms_sqldiag_internal.i_create_patch(
>>>>> sql_text => v_sql_text,
>>>>> hint_text => 'USE_NL(_at_"SEL$897A7E7E" "E"_at_"SEL$2")',
>>>>> name => 'tst_patch');
>>>>> END;
>>>>> /
>>>>>
>>>>> Thanks,
>>>>> Rakesh T
>>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l