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(@"SEL$897A7E7E" "LO"@"SEL$1" "CM"@"SEL$9" "EU"@"SEL$1"
"AU"@"SEL$6" "E"@"SEL$2" "EAM"@"SEL$3" "AD"@"SEL$4" "SU"@"SEL$5"
"URM"@"SEL$7" "AR"@"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@xxxxxxxxx> 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(@"SEL$897A7E7E" "E"@"SEL$2")',
name => 'tst_patch');
END;
/
Thanks,
Rakesh T
