Re: Help with implementing SQL PATCH in Oracle 12.1

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