RE: Need help in SQL tunning
Date: Sat, 22 Oct 2022 07:38:45 -0400
Message-ID: <09c001d8e60a$d8964da0$89c2e8e0$_at_rsiz.com>
As JL noted, an explanation of the logic from your application team is needed. I suggest you hand them my examples, because I think it is highly likely the code does not do what they think it does.
(I’ve never actually seen these three classic misunderstandings combined in a single query before: 1) The mistake that “LEFT JOIN” is different from “LEFT OUTER JOIN” (which is implicitly their belief by having b.<anything> as a predicate in the correlated subquery), 2) The mistake that if you access by an index without an order by you are guaranteed a result sorted in the order of the index and that if you have a sensible index it will always be chosen (this mistake is implicit because of rownum as a variable predicate, unless they mean to get only a single row and don’t care which row, in which case the code is clearer supplying the constant 1), and 3) that any equality predicate of rownum other than 1 can return any rows (implicit in using a variable.
Sigh.
IF it is doing what they think it does, then I wonder WHY they would so obscure the logic. The first step of tuning is whether or not the code produces the intended results.
Good luck. Perhaps JL will chime in if my explanation is unclear.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Krishnaprasad Yadav
Sent: Saturday, October 22, 2022 1:42 AM
To: Mark W. Farnham
Cc: Oracle L
Subject: Re: Need help in SQL tunning
Hi Mark,
Thanks for sharing your views , its really helpful
Since I was in a situation where I see a tuning advisor provides me benefits instantly rather than rewriting the query which application team take some time to change the code probably day or 2 .
so for time being i used recommendation from advisor
but yes , i am curious to know what changes optimizer is doing to get the plan , since i too tried with some hints , but i feel predicate b.col_name=val is getting transformed somehow , even i tried unnesting subquery too, but landed in no luck
Regards,
Krishna
On Sat, 22 Oct 2022 at 04:16, Mark W. Farnham <mwf_at_rsiz.com> wrote:
PS: I did all this early this morning, so I didn’t see the comments of the others, which are more about your existing query.
I still think my re-write is iso-functional and what you should do, but maybe someone can point out a flaw.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Friday, October 21, 2022 6:20 PM
To: chrishna0007_at_gmail.com; 'Oracle L'
Subject: RE: Need help in SQL tunning
Not answering your question, but puzzled…
select a.status, b.status from a left join b
on b.name = a.name
would be the outer join.
but then, you only want the rows returned when
a.c_id in (select id from c where b.name =: <a value>….)
now any no-match between a and b on name is going to return NULL for the value of b.name, that being the nature of outer joins.
That makes me wonder why you don’t prune a first, before the join, and then equijoin the pruned a with b where a.name = b.name.
further, any other value than 1 for sys_b_2 won’t get any rows, so it seems strange to use a variable there when using 1 would give the CBO more information. (If the application is going to submit for any other rownum than 1, simply don’t run the query.
Trivial example (which I think is version invariant):
SQL> r
1 select a.status astat, b.status bstat, a.name aname, b.name bname
2 from a left join b
3 on b.name = a.name
4 where a.c_id in (
5 select id from c
6 where b.name = 'one'
7 )
8* and rownum = 2
no rows selected
SQL> c/2/1
8* and rownum = 1
SQL> r
1 select a.status astat, b.status bstat, a.name aname, b.name bname
2 from a left join b
3 on b.name = a.name
4 where a.c_id in (
5 select id from c
6 where b.name = 'one'
7 )
8* and rownum = 1
ASTAT BSTAT ANAME BNAME
---------- ---------- ---------------------------------------- ----------------------------------------
1 4 one one
should be friendlier to the CBO as
SQL> r
1 with ax as (select a.status, a.name from a
2 where a.name = 'one'
3 and a.c_id in (select id from c)
4 )
5 select ax.status astat, b.status bstat, ax.name, b.name
6 from ax, b
7* where ax.name = b.name
ASTAT BSTAT NAME NAME
- ---------- ---------- ----------
1 4 one one
1 2 one one
SQL> i
8 and rownum = 1;
ASTAT BSTAT NAME NAME
- ---------- ---------- ----------
1 4 one one
SQL> 8 8* and rownum = 1
SQL> c/1/2
8* and rownum = 2
SQL> r
1 with ax as (select a.status, a.name from a
2 where a.name = 'one'
3 and a.c_id in (select id from c)
4 )
5 select ax.status astat, b.status bstat, ax.name, b.name
6 from ax, b
7 where ax.name = b.name
8* and rownum = 2
no rows selected
Now if b smaller than a, you can do the similar with statement on b. Since the original is using a b value as a coordinated equality predicate, only the inner join results can be delivered, so if either pruning a or b is expensive compared to joining it with the pruned other, just prune the one cheapest to prune before the join (remembering to restrict the results of to a.id in c if that pruning was skipped as the with clause).
Now if you really wanted 1,2 instead of 1,4 as the status results, then you need to order the data in the with clause anyway.
using the data:
SQL> select * from a;
ID NAME STATUS C_ID
- ---------- ---------- ----------
1 one 1 11
2 two 1 12
3 three 1 13
SQL> select * from b;
ID NAME STATUS
- ---------- ----------
1 one 2
3 three 2
1 one 4
SQL> select * from c;
ID TYPE
- ----------------------------------------
11 type11
11 type11
12 type11
12 type11
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Krishnaprasad Yadav
Sent: Thursday, October 20, 2022 6:55 AM
To: Oracle L
Subject: Need help in SQL tunning
Hi Experts ,
I have situation , where below was i need to tune , db version :12.1
SQL Text
select a.status ,
b.status
from ng_vid_d_verify a left join ng_vid_status b on b.wi_name = a.wi_name
where a.c_id in (select id
from ng_structure_txn
where b.wi_name = :"SYS_B_0"
and APPLICANT_TYPE = :"SYS_B_1")
and rownum =:"SYS_B_2"
<snip>
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 22 2022 - 13:38:45 CEST