Re: Need help in SQL tunning

Hi Lothar/Jonathan,

Thanks for revert !!!

I have used the SQLTrpt and supplied the sql_id , which provided the me tunned plan as profile outcome .

logic for query i.e filter b.wi_name = :"SYS_B_0" part of subquery , so wanted to know does this filter will be threaten outside the subquery as subquery is not referring the table with alias 'b'

i am not sure what optimiser transformation is used by tuning advisor ,this is what iam looking for.

Regards,
Krishna

On Fri, 21 Oct 2022 at 17:13, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> *the join between A and B is a left join, so "b.something = value" ...*
>
> Yes, came to me too after I wrote my mail.
> But it might still confuse matters. Thus we are left with the condition in
> the subquery.
> Maybe unnesting the subquery yields something.
>
> Regards
>
> Lothar
>
>
> 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-l