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 <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