Re: Need help in SQL tunning
Date: Fri, 21 Oct 2022 13:43:00 +0200
Message-ID: <bb9c32d3-966a-336b-b590-fede46954e8d_at_bluewin.ch>
*the join between A and B is a left join, so "b.something = value" ...*
Am 21.10.2022 um 13:31 schrieb Jonathan Lewis:
>
> Two things that struck me about the query: the SYS_B_nnn say it's a
> query modified by cursor_sharing = force, and arrived with literal
> values. Did the tuning task tune for the original query or for the
> bind variable version - it may be that the plan produced by the tuning
> tool is generally valid ONLY if there are actual values supplied.
>
> The predicate: b.wi_name = :"SYS_B_0" looks wrong - but I can say
> how it should change. The join between A and B is a left join, so
> "b.something = value" will immediately eliminate any preserved
> ("outer") rows. So I don't think it can be moved. I think I'd ask for
> an explanation of the logic before I tried to tune the state,ent.
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Fri, 21 Oct 2022 at 10:57, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> 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 - 13:43:00 CEST