Re: Help with implementing SQL PATCH in Oracle 12.1

From: Rakesh T <aryan.goti_at_gmail.com>
Date: Thu, 28 Jan 2021 11:34:50 +0530
Message-ID: <CAOzfMuq+LEoM1jyhwYqwJtHEcsczbjyHeeENV3EsR6vmypd7eg_at_mail.gmail.com>



Forgot to mention.. Also is it possible to set force_matching via SQL PATCH?

Thanks,
Rakesh T

On Thu, Jan 28, 2021 at 11:25 AM Rakesh T <aryan.goti_at_gmail.com> wrote:

> 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
Received on Thu Jan 28 2021 - 07:04:50 CET

Original text of this message