Thanks Laurentiu. It worked. Will sql patch wok for different bind values
as well?
Thanks,
Rakesh T
On Thu, Jan 28, 2021 at 1:16 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
It is a bit strange the way your hint influenced the plan, but anyway,
assuming oracle will nail all other instructions:
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 => '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") USE_NL(@"SEL$897A7E7E"
"E"@"SEL$2") NLJ_BATCHING(@"SEL$897A7E7E"
"E"@"SEL$2") INDEX(@"SEL$897A7E7E" "E"@"SEL$2" ("FO_ENTITY"."ENTITYID"))',
name => 'tst_patch');
END;
/
În joi, 28 ian. 2021 la 08:53, Rakesh T <aryan.goti@xxxxxxxxx> a scris:
Hi,
Thanks a lot Laurentiu.
I have attached the bad plan as well as the good plan.
I have not tried the SQL plan baseline for this SQL. This is the first
time I am trying SQL PATCH and would like to know how this would behave.
Thanks,
Rakesh T
On Thu, Jan 28, 2021 at 11:54 AM Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> wrote:
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
