Re: Need help in SQL tunning

Hi Laurentiu ,

Thanks for response , i was wondering is their any parameter which does move predicate around .

i was trying to generate plan with some hints , but i failed .

infact post applying the profile i was reviewing the outline details , but i was not able to see any exceptional

Regards,
Krishna

On Thu, 20 Oct 2022 at 20:50, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello Krishna,
>
> You can follow this article to see what hints did the tuning advisor task
> generated :
>
>
> https://www.dbi-services.com/blog/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly/
>
>
>
> În joi, 20 oct. 2022 la 13:55, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> a scris:
>
>> 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