Re: Help with implementing SQL PATCH in Oracle 12.1

I won't add anything to Laurentiu's suggestion, but I am curious about the multiple appearances of this type of expression:

NLSSORT(INTERNAL_FUNCTION("ACTIVEFLAG"),'nls_sort=''BINARY_CI''')=NLSSORT(:SYS_B_25,'nls_sort=''BINARY_CI''')

what has the original SQL got in it that gets transformed into something like this?

Does it start life as "active_flag = 'Y'", or does it actually include an NLSSORT() function call on both sides.

I'm going to hazard a guess (i.e. I could be COMPLETELY WRONG) that any problem you have with poor plans and "bad statistics" actually comes from this predicate appearing all over the place and Oracle using one of its standard guesses when there's a massively skewed distribution of Y and N for the column.

Regards
Jonathan Lewis

P.S. It's a common misunderstanding that *use_nl(X Y)* means "do a nested loop from table X to table Y"; it's actually a shorthand for *use_nl(X) use_nl(Y)*
See: https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/

On Thu, 28 Jan 2021 at 05:55, 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