For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
password : system when i try to connect it was error as above actually , during installation my laptop us switch off at ending. so i don't no Oracle 21c XE installed properly please help
Hi,
check if the filters related to the join are not generating a Cartesian product.
if you have diagnostic pack try to run a SQL Tuning Advisor for this query, which will give you general recommendations related to indexes, statistics, etc.
If it is possible to optimize by creating indexes, I don't see why not create it.
Thank You . My thought was before going for considering new indexes here in this query may be optimizer is choosing some wrong Order + JOIN mechanism causing this to run longer. And if we can tweak the query to help the optimizer by simplifying the query.
And yes again considering the base transaction tables are experiencing heavy DML and are big in size(~5TB+ but are partitioned though), so we need to evaluate those additionally indexes carefully.
OK, but anyway SQL Tuning Advisor can recommend you a profile for example that will give you a better plan than the current one (be careful that it also recommends profiles to use parallelism that is not always recommended).
Is this a new query or was it already executed and did you notice a performance degradation?
If it is old you can check if the degradation was caused by a change in plan.
Hi
Please correct me if I am wrong:
Currently, in where clause you have: STAGE.PART_DT =TAB1.PART_DT AND TAB1.PART_DT = TAB2.PART_DT so you may add STAGE.PART_DT = TAB2.PART_DT
Now to be able to remove TAB1 at all (you saying you not use its data) you need to consider if the following condition is necessary: TAB1.DID = REF2.DID
Then you may remove TAB1 from the query seems it can help..
Regards
Yes that condition is required and apart from those partition key column joins i.e. PART_DT the other condition i.e. "AND TAB1.TXN_ID = TAB2.TXN_ID " is required to filter out records from TAB2. So it look like TAB1 cant be bypassed.
Then, maybe, you can prepare the subset from tab1 that you need for the join and then join only on subset? and/or introduce HASH instead of NL
I think the cause of putting table TAB1 in a nested loop is because the plan_line_id -8 i.e. "MERGE JOIN CARTESIAN" between table STAGE and REF1 , oracle is expecting ~1 rows and that way its putting the FTS of TAB1 in a NESTED LOOP join.
Now i am trying to understand why the merge join Cartesian is yielding 1 row, might be because of some filter on REF1 making it to result ~0 rows which gets rounded up to ~1 by optimizer. And the way table REF1 is used in three different places in the same query might be causing the underestimation of cardinality. Is there a way , i can place table REF1 much better way in this query by tweaking someway which will help optimizer to have correct estimation. REF1 is s small reference data table holding only ~600 rows.
I tried tweaking the query by evaluating table REF1, REF2 separately using WITH clause + Added Materialize hint to restrict it getting merged with the main query and i am seeing its finishing considerably faster time as compared to earlier even with more data volume.
So my next thought is , as its clear that the way table REF1 and REF2 is used/joined in the query causing optimizer estimating the cardinality wrongly and thus a bad plan(mainly as it puts FTS of table TAB1 in a nested loop), so can i achieve same result without using the hints 'MATERIALIZE' and by some additional tweak or will it require any fixing of statistics by anyway rather simplifying/tweaking the query?
WITH tab as( select /*+materialize*/REF1.VAL,REF2.DID from REF1, REF2 where REF1.PUSER = 'ZZZ' AND REF1.NAME = 'CODE' AND TRUNC (SYSDATE) BETWEEN REF1.SDATE AND REF1.EDATE AND ( ( REF1.VAL NOT IN (SELECT VAL FROM REF1 WHERE PUSER = 'ZZZ' AND NAME = 'YYY' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE) OR REF2.CUST_ID IN (SELECT CUST_ID FROM CUST_ORG WHERE CUST_ORG.INDICATOR = 'Y' MINUS SELECT TO_NUMBER (VAL) FROM REF1 WHERE PUSER = 'ZZZ' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE ) ) ))SELECT .... FROM STAGE, TAB1, TAB2, TAB WHERE STAGE.SID = TAB1.SID AND STAGE.SBMT_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 = TAB.VAL AND TAB1.DID = TAB.DID AND EXISTS (SELECT 1 FROM CONTROLTAB WHERE CONTROLTAB.CNTRLID = 999 AND NVL (CONTROLTAB.STATUS, 'F') = 'F')
WITH tab as
( select /*+materialize*/REF1.VAL,REF2.DID
from REF1, REF2 where REF1.PUSER = 'ZZZ'
AND REF1.NAME = 'CODE' AND TRUNC (SYSDATE) BETWEEN REF1.SDATE AND REF1.EDATE
AND ( ( REF1.VAL NOT IN
(SELECT VAL FROM REF1 WHERE PUSER = 'ZZZ' AND NAME = 'YYY' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE)
OR REF2.CUST_ID IN
(SELECT CUST_ID FROM CUST_ORG WHERE CUST_ORG.INDICATOR = 'Y'
MINUS
SELECT TO_NUMBER (VAL) FROM REF1 WHERE PUSER = 'ZZZ' AND TRUNC (SYSDATE) BETWEEN SDATE AND EDATE
)
SELECT
....
FROM STAGE, TAB1, TAB2, TAB
WHERE STAGE.SID = TAB1.SID AND STAGE.SBMT_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 = TAB.VAL AND TAB1.DID = TAB.DID
AND EXISTS (SELECT 1 FROM CONTROLTAB WHERE CONTROLTAB.CNTRLID = 999 AND NVL (CONTROLTAB.STATUS, 'F') = 'F')
Global Information------------------------------ Status : DONE (ALL ROWS) Instance ID : 4 SQL Execution ID : 67108864 Execution Started : 07/09/2020 03:54:05 First Refresh Time : 07/09/2020 03:54:11 Last Refresh Time : 07/09/2020 03:56:02 Duration : 117s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 186 Global Stats===================================================================================================| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Cell || Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |===================================================================================================| 105 | 78 | 27 | 0.00 | 0.47 | 186 | 17M | 1M | 92GB | 80.58% |===================================================================================================SQL Plan Monitoring Details (Plan Hash Value=39601017)============================================================================================================================================================================================================================| 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 | | | | 94 | +24 | 1 | 924K | | | | | 3.96 | Cpu (1) || | | | | | | | | | | | | | | SQL*Net more data to client (3) || 1 | TEMP TABLE TRANSFORMATION | | | | 94 | +24 | 1 | 924K | | | | | | || 2 | LOAD AS SELECT | | | | 1 | +6 | 1 | 1 | | | | 276K | | || 3 | CONCATENATION | | | | 1 | +6 | 1 | 845 | | | | | | || 4 | FILTER | | | | 3 | +4 | 1 | 845 | | | | | 0.99 | Cpu (1) || 5 | MERGE JOIN CARTESIAN | | 12361 | 1225 | 1 | +6 | 1 | 2M | | | | | | || 6 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 1 | 1 | | | | | | || 7 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +6 | 1 | 1 | | | | | | || 8 | BUFFER SORT | | 2M | 1222 | 1 | +6 | 1 | 2M | | | | 64M | | || 9 | INDEX STORAGE FAST FULL SCAN | MTD_PK | 2M | 1222 | 1 | +6 | 1 | 2M | | | | | | || 10 | MINUS | | | | 1 | +6 | 861K | 17 | | | | | | || 11 | TABLE ACCESS BY INDEX ROWID | CUST_ORG | 1 | 3 | 1 | +6 | 861K | 36 | | | | | | || 12 | INDEX UNIQUE SCAN | MC_PK | 1 | 2 | 1 | +6 | 861K | 861K | | | | | | || 13 | SORT UNIQUE NOSORT | | 1 | 4 | 1 | +6 | 861K | 23 | | | | | | || 14 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 861K | 23 | | | | | | || 15 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 6 | +1 | 861K | 23 | | | | | 3.96 | Cpu (4) || 16 | FILTER | | | | | | 1 | | | | | | | || 17 | MERGE JOIN CARTESIAN | | 618 | 1225 | | | 1 | | | | | | | || 18 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | | | 1 | | | | | | | || 19 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | | | 1 | | | | | | | || 20 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 1 | 1 | | | | | | || 21 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +6 | 1 | 1 | | | | | | || 22 | BUFFER SORT | | 2M | 1222 | | | | | | | | | | || 23 | INDEX STORAGE FAST FULL SCAN | MTD_PK | 2M | 1222 | | | | | | | | | | || 24 | MINUS | | | | 1 | +6 | 861K | 17 | | | | | | || 25 | TABLE ACCESS BY INDEX ROWID | CUST_ORG | 1 | 3 | 1 | +6 | 861K | 36 | | | | | | || 26 | INDEX UNIQUE SCAN | MC_PK | 1 | 2 | 1 | +6 | 861K | 861K | | | | | | || 27 | SORT UNIQUE NOSORT | | 1 | 4 | 1 | +6 | 861K | 23 | | | | | | || 28 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 861K | 23 | | | | | | || 29 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +6 | 861K | 23 | | | | | | || 30 | FILTER | | | | 94 | +24 | 1 | 924K | | | | | | || 31 | NESTED LOOPS | | 1 | 6M | 94 | +24 | 1 | 924K | | | | | 0.99 | Cpu (1) || 32 | NESTED LOOPS | | 1826 | 6M | 94 | +24 | 1 | 924K | | | | | 0.99 | Cpu (1) || 33 | HASH JOIN | | 11 | 6M | 112 | +6 | 1 | 1M | | | | 104M | 22.77 | Cpu (23) || 34 | JOIN FILTER CREATE | :BF0001 | 500 | 659 | 1 | +6 | 1 | 845K | | | | | | || 35 | PART JOIN FILTER CREATE | :BF0000 | 500 | 659 | 1 | +6 | 1 | 845K | | | | | | || 36 | MERGE JOIN CARTESIAN | | 500 | 659 | 1 | +6 | 1 | 845K | | | | | | || 37 | VIEW | | 1 | 2 | 1 | +6 | 1 | 845 | | | | | | || 38 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FDA48F5B_58EE133C | 1 | 2 | 1 | +6 | 1 | 845 | | | | | | || 39 | BUFFER SORT | | 500 | 659 | 1 | +6 | 845 | 845K | | | | 104K | | || 40 | TABLE ACCESS BY INDEX ROWID | STAGE | 500 | 657 | 1 | +6 | 1 | 1000 | | | | | | || 41 | INDEX RANGE SCAN | STAGE_IDX1 | 2025 | 28 | 1 | +6 | 1 | 1000 | | | | | | || 42 | JOIN FILTER USE | :BF0001 | 802M | 6M | 112 | +6 | 1 | 249M | | | | | | || 43 | PARTITION RANGE JOIN-FILTER | | 802M | 6M | 112 | +6 | 1 | 249M | | | | | | || 44 | TABLE ACCESS STORAGE FULL | TAB1 | 802M | 6M | 112 | +6 | 1 | 249M | 96540 | 85GB | 87.94% | 7M | 4.95 | Cpu (5) || 45 | PARTITION RANGE ITERATOR | | 166 | 3 | 94 | +24 | 2M | 924K | | | | | 1.98 | Cpu (2) || 46 | INDEX RANGE SCAN | TAB2_IX1 | 166 | 3 | 94 | +24 | 2M | 924K | 862K | 7GB | | | 36.63 | gc current block 2-way (1) || | | | | | | | | | | | | | | gc current grant busy (1) || | | | | | | | | | | | | | | Cpu (20) || | | | | | | | | | | | | | | cell list of blocks physical read (14) || | | | | | | | | | | | | | | cell single block physical read (1) || 47 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB2 | 1 | 12 | 94 | +24 | 1M | 924K | 150K | 1GB | | | 21.78 | Cpu (11) || | | | | | | | | | | | | | | cell list of blocks physical read (4) || | | | | | | | | | | | | | | cell single block physical read (7) || 48 | TABLE ACCESS BY INDEX ROWID | CONTROLTAB | 1 | 1 | 1 | +6 | 1 | 1 | | | | | | || 49 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 1 | +6 | 1 | 1 | | | | | | |============================================================================================================================================================================================================================924077 rows selected.Elapsed: 00:01:57.22Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!)))) 6 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!)) 7 - access("REF1"."PUSER"='ZZZ' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!)) 11 - filter("CUST_ORG"."INDICATOR"='Y') 12 - access("CUST_ID"=:B1) 14 - filter("EDATE">=TRUNC(SYSDATE@!)) 15 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!)) filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!)) 16 - filter(LNNVL( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!))))) 18 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!)) 19 - access("REF1"."PUSER"='ZZZ' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!)) filter( NOT EXISTS (SELECT 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC(SYSDATE@!) AND "NAME"='YYY' AND "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND "VAL"=:B1)) 20 - filter("EDATE">=TRUNC(SYSDATE@!)) 21 - access("PUSER"='ZZZ' AND "NAME"='YYY' AND "VAL"=:B1 AND "SDATE"<=TRUNC(SYSDATE@!)) filter("VAL"=:B1) 25 - filter("CUST_ORG"."INDICATOR"='Y') 26 - access("CUST_ID"=:B1) 28 - filter("EDATE">=TRUNC(SYSDATE@!)) 29 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!)) filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!)) 30 - filter( EXISTS (SELECT 0 FROM "TH_STG_PROD"."CONTROLTAB" "CONTROLTAB" WHERE "CONTROLTAB"."CNTRLID"=999 AND NVL("CONTROLTAB"."STATUS",'F')='F')) 33 - access("STAGE"."SBMT_DT"="TAB1"."PART_DT" AND "STAGE"."SID"="TAB1"."SID" AND "TAB1"."DID"="TAB"."DID") 41 - access("STAGE"."NAME"='ZZZ' AND "STAGE"."STAT"='I') 44 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID")) filter(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID")) 46 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND "TAB2"."CODE"="TAB"."VAL") 47 - filter("TAB1"."PART_DT"="TAB2"."PART_DT") 48 - filter(NVL("CONTROLTAB"."STATUS",'F')='F') 49 - access("CONTROLTAB"."CNTRLID"=999)
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL Execution ID : 67108864
Execution Started : 07/09/2020 03:54:05
First Refresh Time : 07/09/2020 03:54:11
Last Refresh Time : 07/09/2020 03:56:02
Duration : 117s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 186
Global Stats
===================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
| 105 | 78 | 27 | 0.00 | 0.47 | 186 | 17M | 1M | 92GB | 80.58% |
SQL Plan Monitoring Details (Plan Hash Value=39601017)
============================================================================================================================================================================================================================
| 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 | | | | 94 | +24 | 1 | 924K | | | | | 3.96 | Cpu (1) |
| | | | | | | | | | | | | | | SQL*Net more data to client (3) |
| 1 | TEMP TABLE TRANSFORMATION | | | | 94 | +24 | 1 | 924K | | | | | | |
| 2 | LOAD AS SELECT | | | | 1 | +6 | 1 | 1 | | | | 276K | | |
| 3 | CONCATENATION | | | | 1 | +6 | 1 | 845 | | | | | | |
| 4 | FILTER | | | | 3 | +4 | 1 | 845 | | | | | 0.99 | Cpu (1) |
| 5 | MERGE JOIN CARTESIAN | | 12361 | 1225 | 1 | +6 | 1 | 2M | | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 1 | 1 | | | | | | |
| 7 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +6 | 1 | 1 | | | | | | |
| 8 | BUFFER SORT | | 2M | 1222 | 1 | +6 | 1 | 2M | | | | 64M | | |
| 9 | INDEX STORAGE FAST FULL SCAN | MTD_PK | 2M | 1222 | 1 | +6 | 1 | 2M | | | | | | |
| 10 | MINUS | | | | 1 | +6 | 861K | 17 | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | CUST_ORG | 1 | 3 | 1 | +6 | 861K | 36 | | | | | | |
| 12 | INDEX UNIQUE SCAN | MC_PK | 1 | 2 | 1 | +6 | 861K | 861K | | | | | | |
| 13 | SORT UNIQUE NOSORT | | 1 | 4 | 1 | +6 | 861K | 23 | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 861K | 23 | | | | | | |
| 15 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 6 | +1 | 861K | 23 | | | | | 3.96 | Cpu (4) |
| 16 | FILTER | | | | | | 1 | | | | | | | |
| 17 | MERGE JOIN CARTESIAN | | 618 | 1225 | | | 1 | | | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | | | 1 | | | | | | | |
| 19 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | | | 1 | | | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 1 | 1 | | | | | | |
| 21 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +6 | 1 | 1 | | | | | | |
| 22 | BUFFER SORT | | 2M | 1222 | | | | | | | | | | |
| 23 | INDEX STORAGE FAST FULL SCAN | MTD_PK | 2M | 1222 | | | | | | | | | | |
| 24 | MINUS | | | | 1 | +6 | 861K | 17 | | | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | CUST_ORG | 1 | 3 | 1 | +6 | 861K | 36 | | | | | | |
| 26 | INDEX UNIQUE SCAN | MC_PK | 1 | 2 | 1 | +6 | 861K | 861K | | | | | | |
| 27 | SORT UNIQUE NOSORT | | 1 | 4 | 1 | +6 | 861K | 23 | | | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | REF1 | 1 | 3 | 1 | +6 | 861K | 23 | | | | | | |
| 29 | INDEX RANGE SCAN | REF1_PK | 1 | 2 | 1 | +6 | 861K | 23 | | | | | | |
| 30 | FILTER | | | | 94 | +24 | 1 | 924K | | | | | | |
| 31 | NESTED LOOPS | | 1 | 6M | 94 | +24 | 1 | 924K | | | | | 0.99 | Cpu (1) |
| 32 | NESTED LOOPS | | 1826 | 6M | 94 | +24 | 1 | 924K | | | | | 0.99 | Cpu (1) |
| 33 | HASH JOIN | | 11 | 6M | 112 | +6 | 1 | 1M | | | | 104M | 22.77 | Cpu (23) |
| 34 | JOIN FILTER CREATE | :BF0001 | 500 | 659 | 1 | +6 | 1 | 845K | | | | | | |
| 35 | PART JOIN FILTER CREATE | :BF0000 | 500 | 659 | 1 | +6 | 1 | 845K | | | | | | |
| 36 | MERGE JOIN CARTESIAN | | 500 | 659 | 1 | +6 | 1 | 845K | | | | | | |
| 37 | VIEW | | 1 | 2 | 1 | +6 | 1 | 845 | | | | | | |
| 38 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FDA48F5B_58EE133C | 1 | 2 | 1 | +6 | 1 | 845 | | | | | | |
| 39 | BUFFER SORT | | 500 | 659 | 1 | +6 | 845 | 845K | | | | 104K | | |
| 40 | TABLE ACCESS BY INDEX ROWID | STAGE | 500 | 657 | 1 | +6 | 1 | 1000 | | | | | | |
| 41 | INDEX RANGE SCAN | STAGE_IDX1 | 2025 | 28 | 1 | +6 | 1 | 1000 | | | | | | |
| 42 | JOIN FILTER USE | :BF0001 | 802M | 6M | 112 | +6 | 1 | 249M | | | | | | |
| 43 | PARTITION RANGE JOIN-FILTER | | 802M | 6M | 112 | +6 | 1 | 249M | | | | | | |
| 44 | TABLE ACCESS STORAGE FULL | TAB1 | 802M | 6M | 112 | +6 | 1 | 249M | 96540 | 85GB | 87.94% | 7M | 4.95 | Cpu (5) |
| 45 | PARTITION RANGE ITERATOR | | 166 | 3 | 94 | +24 | 2M | 924K | | | | | 1.98 | Cpu (2) |
| 46 | INDEX RANGE SCAN | TAB2_IX1 | 166 | 3 | 94 | +24 | 2M | 924K | 862K | 7GB | | | 36.63 | gc current block 2-way (1) |
| | | | | | | | | | | | | | | gc current grant busy (1) |
| | | | | | | | | | | | | | | Cpu (20) |
| | | | | | | | | | | | | | | cell list of blocks physical read (14) |
| | | | | | | | | | | | | | | cell single block physical read (1) |
| 47 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB2 | 1 | 12 | 94 | +24 | 1M | 924K | 150K | 1GB | | | 21.78 | Cpu (11) |
| | | | | | | | | | | | | | | cell list of blocks physical read (4) |
| | | | | | | | | | | | | | | cell single block physical read (7) |
| 48 | TABLE ACCESS BY INDEX ROWID | CONTROLTAB | 1 | 1 | 1 | +6 | 1 | 1 | | | | | | |
| 49 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 1 | +6 | 1 | 1 | | | | | | |
924077 rows selected.
Elapsed: 00:01:57.22
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!))))
6 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
7 - access("REF1"."PUSER"='ZZZ' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
11 - filter("CUST_ORG"."INDICATOR"='Y')
12 - access("CUST_ID"=:B1)
14 - filter("EDATE">=TRUNC(SYSDATE@!))
15 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
filter(TO_NUMBER("VAL")=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
16 - filter(LNNVL( EXISTS ( (SELECT "CUST_ID" FROM "USER1"."CUST_ORG" "CUST_ORG" WHERE "CUST_ID"=:B1 AND "CUST_ORG"."INDICATOR"='Y')MINUS (SELECT TO_NUMBER("VAL") FROM "USER1"."REF1" "REF1" WHERE "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND TO_NUMBER("VAL")=:B2 AND "SDATE"<=TRUNC(SYSDATE@!)))))
18 - filter("REF1"."EDATE">=TRUNC(SYSDATE@!))
19 - access("REF1"."PUSER"='ZZZ' AND "REF1"."NAME"='CODE' AND "REF1"."SDATE"<=TRUNC(SYSDATE@!))
filter( NOT EXISTS (SELECT 0 FROM "USER1"."REF1" "REF1" WHERE "SDATE"<=TRUNC(SYSDATE@!) AND "NAME"='YYY' AND "PUSER"='ZZZ' AND "EDATE">=TRUNC(SYSDATE@!) AND "VAL"=:B1))
20 - filter("EDATE">=TRUNC(SYSDATE@!))
21 - access("PUSER"='ZZZ' AND "NAME"='YYY' AND "VAL"=:B1 AND "SDATE"<=TRUNC(SYSDATE@!))
filter("VAL"=:B1)
25 - filter("CUST_ORG"."INDICATOR"='Y')
26 - access("CUST_ID"=:B1)
28 - filter("EDATE">=TRUNC(SYSDATE@!))
29 - access("PUSER"='ZZZ' AND "SDATE"<=TRUNC(SYSDATE@!))
30 - filter( EXISTS (SELECT 0 FROM "TH_STG_PROD"."CONTROLTAB" "CONTROLTAB" WHERE "CONTROLTAB"."CNTRLID"=999 AND NVL("CONTROLTAB"."STATUS",'F')='F'))
33 - access("STAGE"."SBMT_DT"="TAB1"."PART_DT" AND "STAGE"."SID"="TAB1"."SID" AND "TAB1"."DID"="TAB"."DID")
41 - access("STAGE"."NAME"='ZZZ' AND "STAGE"."STAT"='I')
44 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."SID","TAB1"."PART_DT","TAB1"."DID"))
46 - access("TAB1"."TXN_ID"="TAB2"."TXN_ID" AND "TAB2"."CODE"="TAB"."VAL")
47 - filter("TAB1"."PART_DT"="TAB2"."PART_DT")
48 - filter(NVL("CONTROLTAB"."STATUS",'F')='F')
49 - access("CONTROLTAB"."CNTRLID"=999)
If you add an ORDER BY to your CTE, that should force Oracle to materialize it.
933257 wrote:I think the cause of putting table TAB1 in a nested loop is because the plan_line_id -8 i.e. "MERGE JOIN CARTESIAN" between table STAGE and REF1 , oracle is expecting ~1 rows and that way its putting the FTS of TAB1 in a NESTED LOOP join.Now i am trying to understand why the merge join Cartesian is yielding 1 row, might be because of some filter on REF1 making it to result ~0 rows which gets rounded up to ~1 by optimizer. And the way table REF1 is used in three different places in the same query might be causing the underestimation of cardinality. Is there a way , i can place table REF1 much better way in this query by tweaking someway which will help optimizer to have correct estimation. REF1 is s small reference data table holding only ~600 rows.
933257 wrote:
Good inference.
Note the predicates on the two objects feeding that MJC - we can compare actuals and estimates and see that the STAGE estimate as out by a factor of about 12, and the REF1 estimate must be very much less than 1 to give you a product where (rounded) 1 * 84 = 1.
I suggest you create extended stats on ref1 (puser, name) - but make sure that 'XXX' and 'CODE' are NOT out of range for the individual columns - this may address the estimate < 1.
Also (name, stat) on stage may give you a better estimate closer to 1,000. Possibly, though, you may need a histogram on STAT (and also on (name, stat)) - that's a guess based on the column name. ("State/Status"? => small number of distinct values with extreme skew).
Jonathan Lewis
You are spot on.
There does exists frequency histogram on column NAME(having 14 distinct value) and STAT(having 7 distinct values) of table STAGE. Also there already exists a frequency histogram on column PUSER and height balanced histogram on column NAME of table REF1. But still they were not helping the existing query.
By creating a column group on both on ref1 (puser, name) and stage(name, stat) with histogram for both i.e. 'FOR COLUMNS SIZE 254'. The merge Cartesian removed automatically from the path by optimizer and its doing a hash join now with TAB1 finishing the query in quick time.
I've written up a blog note giving some explanation of the suggestions I made: https://jonathanlewis.wordpress.com/2020/07/09/execution-plans-6/
password : system