Re: Help with implementing SQL PATCH in Oracle 12.1

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"

> 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