Hi, We are using version 11.2.0.4 of oracle exadata. Table STAGE is stage table and TAB1 and TAB2 are transaction tables and both are range partitioned on column PART_DT. We are fetching most of the data from TAB2 only but there is noway we can JOIN table stage to TAB2, so we have to go through TAB1 here. these table TAB1 and TAB2 are big in size and heavy DML's happen so we are hesitating to go for creating new indexes on them. Other tables used in the query are reference data tables. Below query is running longer and i see few anomalies.
1)The table TAB1 is getting full scanned in a nested loop join thus seems consuming lot of time. I understand there is some deviation in the estimation of rows for the driving/stage table STAGE, but even i forced the estimation by using cardinality hint, it still putting the table TAB1 in a nested loop join. Also i see estimation of TAB1 is underestimated by 1/10th but even i tried force the cardinality estimation still , I am wondering why its not doing HASH join with TAB1? Is it that the way table REF1 is used in the query posing any difficulty for optimizer to join TAB1 using HASH JOIN?
2)Should we modify the query(mainly usage of table REF1 in the query) anyway to make simple for optimizer and then we can get better execution path?
3)Filter operation at line -2 is reducing the result sets from 26million to ~1million. Is there a way, we can we modify the query such that restrict the data much earlier in the path to avoid reading those unnecessary rows?
4)I do see OR expansion is happening here and major DB resource is spent on the first part of the OR expansion. Should we stop the OR expansion someway by modifying the query to stop it?
select...
FROM STAGE STAGE, TAB1 , TAB2,REF1 ,REF2
WHERE STAGE.SID = TAB1.SID AND STAGE.PART_DT =TAB1.PART_DT AND STAGE.STAT = 'I' AND STAGE.NAME = 'XXX'
AND TAB1.TXN_ID = TAB2.TXN_ID AND TAB1.PART_DT = TAB2.PART_DT AND TAB2.CODE = REF1.VAL
AND REF1.PUSER = 'XXX' AND REF1.NAME = 'CODE'
AND TRUNC (SYSDATE) BETWEEN REF1.SDATE AND REF1.EDATE AND TAB1.DID = REF2.DID
AND ( ( REF1.VAL NOT IN
(SELECT VAL FROM REF1 WHERE PUSER = 'XXX' AND NAME = 'YYY' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE)
OR REF2.CUST_ID IN
(SELECT CUST_ID FROM CUST_ORG_PK WHERE CUST_ORG_PK.INDICATR = 'Y'
MINUS
SELECT TO_NUMBER (VAL) FROM REF1 WHERE PUSER = 'ZZZ' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE
)
))
AND EXISTS (SELECT 1 FROM CONTROLTAB WHERE CONTROLTAB.CNTRLID = 9999 AND NVL (CONTROLTAB.STATUS, 'F') = 'S');
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554636
Execution Started : 07/02/2020 06:53:12
First Refresh Time : 07/02/2020 06:53:16
Last Refresh Time : 07/02/2020 10:17:17
Duration : 12245s
Fetch Calls : 8640
Global Stats
=================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
=================================================================================================================
| 12618 | 2184 | 10392 | 0.83 | 0.09 | 41 | 8640 | 13G | 108M | 97TB | 99.44% |
=================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1662617358)
===============================================================================================================================================================================================================================
| 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 | | | | 12086 | +5 | 1 | 864K | | | | | 0.01 | Cpu (1) |
| 1 | CONCATENATION | | | | 12086 | +5 | 1 | 864K | | | | | | |
| 2 | FILTER | | | | 12191 | +4 | 1 | 864K | | | | | 0.03 | Cpu (4) |
| 3 | FILTER | | | | 12191 | +4 | 1 | 26M | | | | | 0.01 | Cpu (1) |
| 4 | NESTED LOOPS | | 241 | 251K | 12191 | +4 | 1 | 26M | | | | | 0.02 | Cpu (3) |
| 5 | NESTED LOOPS | | 241 | 251K | 12191 | +4 | 1 | 26M | | | | | 0.07 | Cpu (8) |
| 6 | NESTED LOOPS | | 241 | 251K | 12232 | +4 | 1 | 26M | | | | | 0.05 | Cpu (6) |
| 7 | NESTED LOOPS | | 5407 | 233K | 12242 | +4 | 1 | 86M | | | | | | |
| 8 | MERGE JOIN CARTESIAN | | 1 | 35 | 12242 | +4 | 1 | 1000 | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +4 | 1 | 1 | | | | | | |
| 10 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 12242 | +4 | 1 | 1 | | | | | | |
| 11 | BUFFER SORT | | 84 | 32 | 12242 | +4 | 1 | 1000 | | | | 104K | | |
| 12 | TABLE ACCESS BY INDEX ROWID | STAGE | 84 | 32 | 1 | +4 | 1 | 1000 | | | | | | |
| 13 | INDEX RANGE SCAN | STAGE_IDX1 | 84 | 4 | 1 | +4 | 1 | 1000 | | | | | | |
| 14 | PARTITION RANGE ITERATOR | | 8292 | 232K | 12232 | +4 | 1000 | 86M | | | | | | |
| 15 | TABLE ACCESS STORAGE FULL | TAB1 | 8292 | 232K | 12245 | +1 | 1000 | 86M | 103M | 521GB | 1.96% | 7M | 51.81 | gc buffer busy acquire (1) |
| | | | | | | | | | | | | | | latch: cache buffers chains (1) |
| | | | | | | | | | | | | | | Cpu (1196) |
| | | | | | | | | | | | | | | gcs drm freeze in enter server mode (2) |
| | | | | | | | | | | | | | | reliable message (5) |
| | | | | | | | | | | | | | | cell single block physical read (2827) |
| | | | | | | | | | | | | | | cell smart table scan (1977) |
| | | | | | | | | | | | | | | read by other session (304) |
| 16 | PARTITION RANGE ITERATOR | | 1 | 12 | 12191 | +4 | 86M | 26M | | | | | 0.42 | Cpu (51) |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB2 | 1 | 12 | 12191 | +4 | 86M | 26M | 4M | 28GB | | | 32.14 | gc cr grant 2-way (20) |
| | | | | | | | | | | | | | | gc cr request (2) |
| | | | | | | | | | | | | | | gc remaster (6) |
| | | | | | | | | | | | | | | Cpu (319) |
| | | | | | | | | | | | | | | gcs drm freeze in enter server mode (4) |
| | | | | | | | | | | | | | | latch: gc element (2) |
| | | | | | | | | | | | | | | cell single block physical read (3563) |
| 18 | INDEX RANGE SCAN | TAB2_IX1 | 166 | 3 | 12210 | +2 | 86M | 26M | 1M | 11GB | | | 15.17 | Cpu (292) |
| | | | | | | | | | | | | | | cell single block physical read (1557) |
| 19 | INDEX UNIQUE SCAN | MTD_PK | 1 | 1 | 12242 | +4 | 26M | 26M | 292 | 2MB | | | 0.17 | Cpu (20) |
| | | | | | | | | | | | | | | cell single block physical read (1) |
| 20 | TABLE ACCESS BY INDEX ROWID | REF2 | 1 | 2 | 12191 | +4 | 26M | 26M | 7 | 57344 | | | 0.11 | Cpu (13) |
| 21 | TABLE ACCESS BY INDEX ROWID | CONTROLTAB | 1 | 1 | 1 | +4 | 1 | 1 | | | | | | |
| 22 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 1 | +4 | 1 | 1 | | | | | | |
| 23 | MINUS | | | | 102 | +4 | 25 | 3 | | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | CUST_ORG_PK | 1 | 3 | 942 | +4 | 25 | 10 | | | | | | |
| 25 | INDEX UNIQUE SCAN | MC_PK | 1 | 2 | 942 | +4 | 25 | 25 | | | | | | |
| 26 | SORT UNIQUE NOSORT | | 1 | 4 | 8 | +4 | 25 | 9 | | | | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 8 | +4 | 25 | 9 | | | | | | |
| 28 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 8 | +4 | 25 | 9 | | | | | | |
| 29 | FILTER | | | | | | 1 | | | | | | | |
| 30 | FILTER | | | | | | 1 | | | | | | | |
| 31 | NESTED LOOPS | | 4 | 236K | | | 1 | | | | | | | |
| 32 | NESTED LOOPS | | 4 | 236K | | | 1 | | | | | | | |
| 33 | NESTED LOOPS | | 4 | 236K | | | 1 | | | | | | | |
| 34 | NESTED LOOPS | | 270 | 233K | | | 1 | | | | | | | |
| 35 | MERGE JOIN CARTESIAN | | 1 | 35 | | | 1 | | | | | | | |
| 36 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | | | 1 | | | | | | | |
| 37 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | | | 1 | | | | | | | |
| 38 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +12245 | 1 | 1 | | | | | | |
| 39 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +12245 | 1 | 1 | | | | | | |
| 40 | BUFFER SORT | | 84 | 32 | | | | | | | | | | |
| 41 | TABLE ACCESS BY INDEX ROWID | STAGE | 84 | 32 | | | | | | | | | | |
| 42 | INDEX RANGE SCAN | STAGE_IDX1 | 84 | 4 | | | | | | | | | | |
| 43 | PARTITION RANGE ITERATOR | | 8292 | 232K | | | | | | | | | | |
| 44 | TABLE ACCESS STORAGE FULL | TAB1 | 8292 | 232K | | | | | | | | | | |
| 45 | PARTITION RANGE ITERATOR | | 1 | 12 | | | | | | | | | | |
| 46 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB2 | 1 | 12 | | | | | | | | | | |
| 47 | INDEX RANGE SCAN | TAB2_IX1 | 166 | 3 | | | | | | | | | | |
| 48 | INDEX UNIQUE SCAN | MTD_PK | 1 | 1 | | | | | | | | | | |
| 49 | TABLE ACCESS BY INDEX ROWID | REF2 | 1 | 2 | | | | | | | | | | |
| 50 | TABLE ACCESS BY INDEX ROWID | CONTROLTAB | 1 | 1 | 1 | +4 | 1 | 1 | | | | | | |
| 51 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 1 | +4 | 1 | 1 | | | | | | |
| 52 | MINUS | | | | 101 | +5 | 25 | 3 | | | | | | |
| 53 | TABLE ACCESS BY INDEX ROWID | CUST_ORG_PK | 1 | 3 | 102 | +4 | 25 | 10 | | | | | | |
| 54 | INDEX UNIQUE SCAN | MC_PK | 1 | 2 | 942 | +4 | 25 | 25 | | | | | | |
| 55 | SORT UNIQUE NOSORT | | 1 | 4 | 8 | +4 | 25 | 9 | | | | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 8 | +4 | 25 | 9 | | | | | | |
| 57 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 8 | +4 | 25 | 9 | | | | | | |
===============================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS ( (SELECT /*+ INDEX_RS_ASC ("CUST_ORG_PK" "MC_PK") */ "CUST_ID" FROM "USER1"."CUST_ORG_PK" "CUST_ORG_PK" WHERE "CUST_ID"=:B1 AND "CUST_ORG_PK"."INDICATR"='Y')MINUS (SELECT /*+ INDEX_RS_ASC
("REF1" "REF1_PK") */ TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2
AND "SDATE"<=TRUNC(SYSDATE@!))))
3 - filter( EXISTS (SELECT /*+ INDEX_RS_ASC ("CONTROLTAB" "CONTROLTAB_PK") */ 0 FROM "USER2"."CONTROLTAB" "CONTROLTAB" WHERE "CONTROLTAB"."CNTRLID"=9999 AND
NVL("CONTROLTAB"."STATUS",'F')='S'))
9 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
10 - access("REF1"."PUSER"='XXX' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
13 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')
15 - storage("STAGE"."PART_DT"="TAB1"."PART_DT" AND "STAGE"."SID"="TAB1"."SID")
filter("STAGE"."PART_DT"="TAB1"."PART_DT" AND "STAGE"."SID"="TAB1"."SID")
17 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")
18 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND "TAB2"."CODE"="REF1"."VAL")
19 - access("TAB1"."DID"="REF2"."DID")
21 - filter(NVL("CONTROLTAB"."STATUS",'F')='S') 22 - access("CONTROLTAB"."CNTRLID"=9999)
24 - filter("CUST_ORG_PK"."INDICATR"='Y')
25 - access("CUST_ID"=:B1)
27 - filter("EDATE">=TRUNC(SYSDATE@!))
28 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
29 - filter(LNNVL( EXISTS ( (SELECT /*+ INDEX_RS_ASC ("CUST_ORG_PK" "MC_PK") */ "CUST_ID" FROM "USER1"."CUST_ORG_PK" "CUST_ORG_PK" WHERE "CUST_ID"=:B1 AND "CUST_ORG_PK"."INDICATR"='Y')MINUS (SELECT
/*+ INDEX_RS_ASC ("REF1" "REF1_PK") */ TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!)
AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!)))))
30 - filter( EXISTS (SELECT /*+ INDEX_RS_ASC ("CONTROLTAB" "CONTROLTAB_PK") */ 0 FROM "USER2"."CONTROLTAB" "CONTROLTAB" WHERE "CONTROLTAB"."CNTRLID"=9999 AND
NVL("CONTROLTAB"."STATUS",'F')='S'))
36 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
37 - access("REF1"."PUSER"='XXX' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
filter( NOT EXISTS (SELECT /*+ PUSH_SUBQ INDEX_RS_ASC ("REF1" "REF1_PK") */ 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC(SYSDATE@!) AND
"NAME"='YYY' AND "PUSER"='XXX' AND "EDATE">=TRUNC(SYSDATE@!) AND "VAL"=:B1))
38 - filter("EDATE">=TRUNC(SYSDATE@!))
39 - access("PUSER"='XXX' AND "NAME"='YYY' AND "VAL"=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
filter("VAL"=:B1)
42 - access("STAGE"."NAME"='XXX' AND "STAGE"."STAT"='I')
44 - storage("STAGE"."SID"="TAB1"."SID" AND "STAGE"."PART_DT"="TAB1"."PART_DT")
filter("STAGE"."SID"="TAB1"."SID" AND "STAGE"."PART_DT"="TAB1"."PART_DT")
46 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")
47 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND "TAB2"."CODE"="REF1"."VAL")
48 - access("TAB1"."DID"="REF2"."DID")
50 - filter(NVL("CONTROLTAB"."STATUS",'F')='S')
51 - access("CONTROLTAB"."CNTRLID"=9999)
53 - filter("CUST_ORG_PK"."INDICATR"='Y')
54 - access("CUST_ID"=:B1)
56 - filter("EDATE">=TRUNC(SYSDATE@!))
57 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))