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@xxxxxxxxx> 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(@"SEL$897A7E7E" "E"@"SEL$2")',
name => 'tst_patch');
END;
/
Thanks,
Rakesh T
