Re: Need help in SQL tunning
Date: Thu, 20 Oct 2022 18:20:16 +0300
Message-ID: <CA+riqSWkEGBKgxbSOd3p+hhS35zzyxcnqkug6oYhywyOgiRCsg_at_mail.gmail.com>
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-lReceived on Thu Oct 20 2022 - 17:20:16 CEST