Re: Help with implementing SQL PATCH in Oracle 12.1

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