Its version 12.1.0.2 of Oracle Exadata with 11.2 optimizer_feature_enable. We have below query which spends significant time on the exists query evaluation i.e line number -8. It consumes ~90%+ of the total execution time(~5-6minutes). We want to get this query finished in <~1minutes. And i see "TABLE ACCESS BY INDEX ROWID" is resulting into 1 rows after visiting through ~456 million rows through a execution of ~392k times for an index range scan. It seems from the plan that if we would have a composite index on column(PSTS,PFUN,PENT), it would have been faster, but we don't have any such index currently in the table. One index exists on (PSTS,PFUN) and another on (different column, PENT, Different column).
So my question is, if there exists any other optimization strategy which Oracle can opt here which will make that step execute once rather ~392k times and then a FTS on table STG(it has total ~56k rows only) will be enough to get back the result? Or the only way here is to have the composite index created on the three columns as because that exists query has to be executed ~392k times?
select *
FROM A , B , E
WHERE A.MID = 1
AND A.BID = B.BID
AND B.STS IN ('X', 'Y','Z','A')
AND A.CID = E.CID
AND ( A.LST_DT >= TO_TIMESTAMP ( :b4, 'YYYY-MM-DD-HH24.MI.SS.FF')
OR ( E.LST_DT >= TO_TIMESTAMP ( :b4, 'YYYY-MM-DD-HH24.MI.SS.FF')
AND E.LST_ID NOT IN ('AAA', 'BBB'))
OR EXISTS
(SELECT 'X'
FROM STG STG
WHERE STG.PSTS = 'XXX' AND PFUN = 'YYYY' AND STG.PENT = A.PENT))
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : gkcgfa800r1cb
SQL Execution ID : 33554432
Execution Started : 08/26/2020 15:31:36
First Refresh Time : 08/26/2020 15:31:36
Last Refresh Time : 08/26/2020 15:37:15
Duration : 339s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 195
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B4 | 1 | VARCHAR2(4001) | 2020-08-26-12.46.12.000000 |
========================================================================================================================
Global Stats
============================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
============================================================================================================================
| 320 | 318 | 0.56 | 0.00 | 0.00 | 0.00 | 1.80 | 195 | 350M | 4354 | 2GB | 28.06% |
============================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=102282368)
====================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
====================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 332 | +8 | 1 | 194 | | | | | | |
| 1 | FILTER | | | | 332 | +8 | 1 | 194 | | | | | 0.31 | Cpu (1) |
| 2 | HASH JOIN | | 440K | 138K | 339 | +1 | 1 | 393K | | | | 483M | 0.62 | Cpu (2) |
| 3 | HASH JOIN | | 440K | 55859 | 5 | +2 | 1 | 393K | | | | 108M | | |
| 4 | TABLE ACCESS STORAGE FULL | B | 440K | 7889 | 1 | +2 | 1 | 442K | 401 | 360MB | 80.35% | 15M | | |
| 5 | TABLE ACCESS STORAGE FULL | A | 866K | 15316 | 5 | +2 | 1 | 868K | 1265 | 688MB | 15.97% | 15M | 1.25 | Cpu (4) |
| 6 | TABLE ACCESS STORAGE FULL | E | 879K | 21762 | 334 | +6 | 1 | 879K | 1802 | 1GB | 16.67% | 15M | | |
| 7 | TABLE ACCESS BY INDEX ROWID | STG | 1 | 64 | 334 | +6 | 392K | 1 | 875 | 7MB | | | 75.08 | Cpu (241) |
| 8 | INDEX RANGE SCAN | STG_IX1 | 271 | 5 | 334 | +6 | 392K | 456M | 11 | 90112 | | | 22.74 | Cpu (73) |
====================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."LST_DT">=TO_TIMESTAMP(:B4,'YYYY-MM-DD-HH24.MI.SS.FF') OR
"E"."LST_DT">=TO_TIMESTAMP(:B4,'YYYY-MM-DD-HH24.MI.SS.FF') AND
"E"."LST_ID"<>'AAA' AND "E"."LST_ID"<>'BBB'
OR EXISTS (SELECT 0 FROM "USER1"."STG" "STG" WHERE
"PFUN"='YYYY' AND "STG"."PSTS"='XXX' AND "STG"."PENT"=:B1))
2 - access("A"."CID"="E"."CID")
3 - access("A"."BID"="B"."BID")
4 - storage("B"."STS"='X' OR "B"."STS"='Y' OR "B"."STS"='Z' OR "B"."STS"='A')
filter("B"."STS"='X' OR "B"."STS"='Y' OR "B"."STS"='Z' OR "B"."STS"='A')
5 - storage("A"."MID"=1) filter("A"."MID"=1)
7 - filter("STG"."PENT"=:B1)
8 - access("STG"."PSTS"='XXX' AND "PFUN"='YYYY')