Hello All,
There is a select statement for which I am looking to improve performance as it got stuck while fetching data.
The count in wk_temp_tb is Approx. 775K however when I try to run same query with less count in wk_temp_tb like Approx. 225K then it fetches within 10-15 seconds.
If you can please suggest any way to re-write the query or use some hint which can be helpful when the data in wk_temp_tb increase then it will not impact the performance of the query.
SELECT /*+ use_hash(ol,dv1) */ 390210,'?' GLActKey1 , '?' GLActKey2,'?' BaseNum,
ab.dealid,ab.firmid,ab.sysactid,ab.crcycd,fv.MicroCntrlStrtgyC,fv.expnscd,0 trncd,
0.0 origamt , 0.0 localamt , 0.0 usdamt,'C' indcr
FROM
firmtb fv
,apfirmtb ca
,firmplustb fp
,trntb tx
,acttb fa
,dealtb dv
,actbaltb ab
,summarytb pf
WHERE fv.legalcd=ca.legalcd
AND ca.legalcd in ('00G','00Q','04M','05M','05U','06X','07X','08Y','09U','0AH','0BV','0BX','0BY','0CU','0CV','0DM','0DN','0EU','0FH','0FJ','0KV','0MA','0MB','0NP','0OQ','0QQ','0RA','0RZ','0SZ')
AND ( tx.stsindcr=1 or ( tx.actltermdt != '01-JAN-1900' AND tx.actltermdt >= '14-DEC-23' ))
AND fa.actnum = fv.actnum
AND fp.actnum = fa.actnum
AND '14-DEC-23' between fp.StartD AND fp.EndD
AND fp.postf = 'Y'
AND tx.deal = dv.deal AND tx.trnId =dv.trnId
AND dv.deald=ab.deald AND fv.firmid=ab.firmid
AND ab.sysactid = pf.sysactid AND pf.SumryActI = 33
AND NOT EXISTS (SELECT 1 FROM wk_temp_tb OL, dealtb dv1
WHERE OL.FEEDSETID = 390210
AND dv1.deal = dv.deal AND ol.firmid = fv.firmid
);
Plan hash value: 1347286106
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1703 | 86419 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 34 | 4454 | 82375 (1)| 00:00:04 |
| 2 | NESTED LOOPS | | 34 | 4454 | 82375 (1)| 00:00:04 |
| 3 | NESTED LOOPS | | 33 | 3531 | 82310 (1)| 00:00:04 |
| 4 | NESTED LOOPS | | 1277 | 106K| 78478 (1)| 00:00:04 |
| 5 | NESTED LOOPS | | 25537 | 1670K| 1857 (1)| 00:00:01 |
|* 6 | HASH JOIN | | 25546 | 1521K| 1856 (1)| 00:00:01 |
| 7 | INLIST ITERATOR | | | | | |
|* 8 | INDEX UNIQUE SCAN | apfirmtb_IDX1 | 29 | 116 | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 51091 | 2843K| 1854 (1)| 00:00:01 |
| 10 | INLIST ITERATOR | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| firmtb | 14073 | 371K| 916 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | firmtb_IDX3 | 14073 | | 30 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 170K| 4993K| 938 (1)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | summarytb_IDX2 | 11 | 88 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | actbaltb_IDX3 | 15496 | 332K| 85 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | acttb_IDX1 | 1 | 6 | 0 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | dealtb_IDX3 | 1 | 18 | 3 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 3 | 57 | 12 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | wk_temp_tb | 2 | 24 | 6 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | dealtb_IDX2 | 2 | 14 | 3 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | trntb | 1 | 22 | 3 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | trntb_IDX1 | 1 | | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | firmplustb_IDX1 | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | firmplustb | 1 | 24 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("FV"."legalcd"="CA"."legalcd")
8 - access("CA"."legalcd"='00G' OR "CA"."legalcd"='00Q' OR "CA"."legalcd"='04M' OR "CA"."legalcd"='05M' OR "CA"."legalcd"='05U' OR
"CA"."legalcd"='06X' OR "CA"."legalcd"='07X' OR "CA"."legalcd"='08Y' OR "CA"."legalcd"='09U' OR "CA"."legalcd"='0AH' OR
"CA"."legalcd"='0BV' OR "CA"."legalcd"='0BX' OR "CA"."legalcd"='0BY' OR "CA"."legalcd"='0CU' OR "CA"."legalcd"='0CV' OR
"CA"."legalcd"='0DM' OR "CA"."legalcd"='0DN' OR "CA"."legalcd"='0EU' OR "CA"."legalcd"='0FH' OR "CA"."legalcd"='0FJ' OR
"CA"."legalcd"='0KV' OR "CA"."legalcd"='0MA' OR "CA"."legalcd"='0MB' OR "CA"."legalcd"='0NP' OR "CA"."legalcd"='0OQ' OR
"CA"."legalcd"='0QQ' OR "CA"."legalcd"='0RA' OR "CA"."legalcd"='0RZ' OR "CA"."legalcd"='0SZ')
9 - access("FV"."firmid"="AB"."firmid")
12 - access("FV"."legalcd"='00G' OR "FV"."legalcd"='00Q' OR "FV"."legalcd"='04M' OR "FV"."legalcd"='05M' OR "FV"."legalcd"='05U' OR
"FV"."legalcd"='06X' OR "FV"."legalcd"='07X' OR "FV"."legalcd"='08Y' OR "FV"."legalcd"='09U' OR "FV"."legalcd"='0AH' OR
"FV"."legalcd"='0BV' OR "FV"."legalcd"='0BX' OR "FV"."legalcd"='0BY' OR "FV"."legalcd"='0CU' OR "FV"."legalcd"='0CV' OR
"FV"."legalcd"='0DM' OR "FV"."legalcd"='0DN' OR "FV"."legalcd"='0EU' OR "FV"."legalcd"='0FH' OR "FV"."legalcd"='0FJ' OR
"FV"."legalcd"='0KV' OR "FV"."legalcd"='0MA' OR "FV"."legalcd"='0MB' OR "FV"."legalcd"='0NP' OR "FV"."legalcd"='0OQ' OR
"FV"."legalcd"='0QQ' OR "FV"."legalcd"='0RA' OR "FV"."legalcd"='0RZ' OR "FV"."legalcd"='0SZ')
14 - access("PF"."SUMRYACTI"=33)
15 - access("AB"."sysactid"="PF"."sysactid")
16 - access("FA"."actnum"="FV"."actnum")
17 - access("DV"."deald"="AB"."deald")
filter( NOT EXISTS (SELECT 0 FROM "dealtb" "DV1","wk_temp_tb" "OL" WHERE
"OL"."firmid"=:B1 AND "OL"."FEEDSETID"=390210 AND "DV1"."deal"=:B2))
19 - filter("OL"."firmid"=:B1 AND "OL"."FEEDSETID"=390210)
20 - access("DV1"."deal"=:B1)
21 - filter("TX"."stsindcr"=1 OR "TX"."actltermdt">='14-DEC-23' AND "TX"."actltermdt"<>TO_DATE(' 1900-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
22 - access("TX"."deal"="DV"."deal" AND "TX"."trnId"="DV"."trnId")
23 - access("FP"."actnum"="FA"."actnum" AND "FP"."STARTD"<='14-DEC-23')
24 - filter("FP"."postf"='Y' AND "FP"."ENDD">='14-DEC-23')
Note
-----
- this is an adaptive plan