Re: Help with implementing SQL PATCH in Oracle 12.1

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




text/plain attachment: bad_plan.txt



text/plain attachment: good_plan.txt