Re: Need help in SQL tunning
Date: Fri, 21 Oct 2022 11:57:36 +0200
Message-ID: <c81b1880-80b0-6492-191c-27f2cf8892da_at_bluewin.ch>
Hi,
the current plan is wrong because the estimate for the hash join in step
5 is one row.
The most likely reason is that high and low values for column wi_name
for tables ng_vid_status and ng_vid_d_verify in DBA_TAB_COLUMNS are
not matching.
(https://jonathanlewis.wordpress.com/2006/11/29/low_value-high_value/)
One of the ranges could be out of bound of the other.
What strikes me however is that *b.wi_name = :"SYS_B_0" *is in the subquery.
I don't think it belongs there.*
*Correct the statement to
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 APPLICANT_TYPE = :"SYS_B_1")and b.wi_name = :"SYS_B_0"
and rownum =:"SYS_B_2"
and let us see how this works out.
Send on other monitor if it is still slow.
Thanks
Lothar
Am 20.10.2022 um 12:55 schrieb Krishnaprasad Yadav:
> 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"
>
>
>
>
> SQL Plan Monitoring Details (Plan Hash Value=2278711939)
> ===================================================================================================================================================================================================
> | Id | Operation | Name |
> Rows | Cost | Time | Start | Execs | Rows | Read | Read
> | Mem | Activity | Activity Detail |
> | | | |
> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
> (Max) | (%) | (# samples) |
> ===================================================================================================================================================================================================
> | 0 | SELECT STATEMENT | |
> | | | | 1 | | | |
> | | |
> | 1 | COUNT STOPKEY | |
> | | | | 1 | | | |
> | | |
> | 2 | FILTER | |
> | | | | 1 | | | |
> | | |
> | 3 | NESTED LOOPS SEMI | |
> 1 | 23561 | | | 1 | | | |
> | | |
> | 4 | FILTER | |
> | | 1 | +4 | 1 | 0 | | |
> | | |
> | 5 | HASH JOIN RIGHT OUTER | |
> 1 | 23558 | 3 | +2 | 1 | 2M | | |
> 150M | 66.67 | Cpu (2) |
> | 6 | TABLE ACCESS FULL | NG_VID_STATUS
> | 2M | 4914 | 1 | +4 | 1 | 2M | |
> | | | |
> | 7 | TABLE ACCESS FULL | NG_VID_D_VERIFY
> | 2M | 8768 | 2 | +3 | 1 | 2M | 2520 |
> 312MB | | 33.33 | direct path read (1) |
> | 8 | TABLE ACCESS BY INDEX ROWID BATCHED | NG_STRUCTURE_TXN
> | 4M | 3 | | | | |
> | | | | |
> | 9 | INDEX RANGE SCAN |
> CID_STRUCTURE_TXN_IDX | 1 | 2 | | |
> | | | | | | |
> ===================================================================================================================================================================================================
>
> Currently index is present on WI_NAME column of both tables ie
> NG_VID_STATUS and NG_VID_D_VERIFY
>
> I manually tried to tune it by adding hint of index and making
> NG_VID_STATUS as leading with use_nl hint but index was not picked up
>
> However i was trying something as below ,and query was optimised
> without any hint
>
> select a.status as docstatus ,
> b.status as vstatus
> from ng_vid_d_verify a left join ng_vid_status b
> on b.wi_name = a.wi_name
> and b.wi_name = :"SYS_B_0"
> where a.c_id in (select id
> from ng_structure_txn
> where
> APPLICANT_TYPE = :"SYS_B_1")
> and rownum =:"SYS_B_2"
>
> Plan of above was close to plan provided in below , which was
> generated by tuning advisior
>
> But recommending this needs code change . finally i used tuning
> advisor and it generated optimised plan similar to rewritten code ,
> lio was 7 from 70K
> time being i have used it but i don't know how tuning advisor was able
> to generate optimal plan .
>
> TUNING ADVISOR PLAN :
>
>
> -----------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes | Cost (%CPU)| Time |
> -----------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 106 |
> 10 (0)| 00:00:01 |
> |* 1 | COUNT STOPKEY | | | |
> | |
> | 2 | NESTED LOOPS SEMI | | 1 | 106 |
> 10 (0)| 00:00:01 |
> | 3 | MERGE JOIN CARTESIAN | | 1 | 88 |
> 7 (0)| 00:00:01 |
> | 4 | TABLE ACCESS BY INDEX ROWID | NG_VID_STATUS |
> 1 | 36 | 4 (0)| 00:00:01 |
> |* 5 | INDEX RANGE SCAN | NGI_VID_STATUS |
> 1 | | 3 (0)| 00:00:01 |
> | 6 | BUFFER SORT | | 1 | 52 |
> 3 (0)| 00:00:01 |
> | 7 | TABLE ACCESS BY INDEX ROWID| NG_VID_D_VERIFY |
> 1 | 52 | 3 (0)| 00:00:01 |
> |* 8 | INDEX RANGE SCAN | NGI_VID_D_VERIFY |
> 1 | | 2 (0)| 00:00:01 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | NG_STRUCTURE_TXN |
> 3948K| 67M| 3 (0)| 00:00:01 |
> |* 10 | INDEX RANGE SCAN | CID_STRUCTURE_TXN_IDX
> | 1 | | 2 (0)| 00:00:01 |
> -----------------------------------------------------------------------------------------------------------------
>
> Later i done further modifcation from Meger cartesian to USE_NL and
> used the same plan .
> It would be very helpfull if some one will assist me to
> understand what advisor done to generate plan above plan .
>
>
>
> Regards,
> Krishna
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2022 - 11:57:36 CEST