Hi We are using version 11.2.0.4 of Exadata.We have total ~20GB pga_aggregate_target set as 20GB and tempspace allocated is ~250GB. default work area policy set as AUTO.
We have two plans for same query(with Hints embedded) and both are executed with Manual work area size and with hash_area_size and sort_area_size being set as 2GB in session level to minimize temp spill. But when I compare these two plans then I see in case of PLAN-1 even the memory and tempspace consumption seems large but at anypoint in time only one HASH/BUILD AREA being active keeping the maximum memory consumption to ~2GB and tempspace to max ~17GB. Where as in case of PLAN-2, it runs faster and also the total memory + tempspill looks smaller, but i see at any point in time the memory consumption spikes up to ~6GB because of multiple HASH/Build AREA being active or in use to serve the query and the tempspace consumption being less than ~10GB.
My question is below
1)What should be the correct approach here to go for with PLAN-1 or PLAN-2? Or any other order of execution of HASH Join will make us consuming lesser memory+temp space usage?
2)I am not able to understand , why in case of PLAN-1 on plan_line_id- 2, the tempspill goes up to 17GB. And if it can be minimized?
3)Though Resource consumption wise plan-2 looks lesser but my concern with the memory usage as 6GB at any point in time out of total 20GB available in database. Is this still should be considered as better as compared to plan-1?
Sample Query without hint is as below:-
SELECT TAB1.c1, c2, c3, c4...,c10,,tab2.c1, REFTAB.c1
FROM STAGE, CONTRLTAB,USER1.TAB2,USER1.TAB1,REFTAB
WHERE CONTRLTAB.BFLCIND = 'Y' AND STAGE.BFID = CONTRLTAB.BFID
AND STAGE.PBSDNBR = TO_NUMBER ( TO_CHAR (CONTRLTAB.UASPDT, 'DD'))
AND TAB2.REFNBR = STAGE.REFNBR AND TAB2.PART_DT >= TRUNC (SYSDATE - 7)
AND TAB2.PMCD IN ('XX', 'YY', 'CC', 'VV') AND REFTAB.CTCD = 'EE'
AND TAB2.RRID = TAB1.RRID AND TAB2.SID = TAB1.SID AND TAB2.PART_DT = TAB1.PART_DT AND TAB2.MARID = REFTAB.CEID
AND TAB1.PART_DT >= TRUNC (SYSDATE - 7)
ORDER BY STAGE.REFNBR
******* PLAN-1*******
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 07/02/2020 04:51:54
First Refresh Time : 07/02/2020 04:51:58
Last Refresh Time : 07/02/2020 05:26:40
Duration : 2086s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 12290
Global Stats
========================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
========================================================================================================
| 1426 | 1001 | 424 | 0.04 | 12290 | 109M | 1M | 858GB | 27756 | 27GB | 71.51% |
========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1568361430)
====================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | (Max) | (Max) | (%) | (# samples) |
====================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1858 | +229 | 1 | 61M | | | | | | | | 2.83 | Cpu (35) |
| | | | | | | | | | | | | | | | | | SQL*Net more data to client (6) |
| 1 | SORT ORDER BY | | 20M | 264M | 1858 | +229 | 1 | 61M | 6534 | 6GB | 6528 | 6GB | | 2G | 7G | 16.03 | Cpu (84) |
| | | | | | | | | | | | | | | | | | direct path read temp (148) |
| 2 | HASH JOIN | | 20M | 263M | 1081 | +152 | 1 | 61M | 16211 | 16GB | 16211 | 16GB | | 2G | 17G | 58.33 | Cpu (619) |
| | | | | | | | | | | | | | | | | | direct path read temp (217) |
| | | | | | | | | | | | | | | | | | direct path write temp (8) |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 20M | 19M | 61 | +153 | 1 | 61M | | | | | | | | 0.69 | Cpu (10) |
| 4 | HASH JOIN | | 20M | 19M | 177 | +37 | 1 | 61M | 2539 | 2GB | 2539 | 2GB | | 2G | 3G | 3.11 | Cpu (24) |
| | | | | | | | | | | | | | | | | | direct path read temp (18) |
| | | | | | | | | | | | | | | | | | direct path write temp (3) |
| 5 | HASH JOIN | | 31M | 19M | 151 | +1 | 1 | 61M | 2478 | 2GB | 2478 | 2GB | | 2G | 3G | 5.11 | Cpu (48) |
| | | | | | | | | | | | | | | | | | direct path read temp (26) |
| 6 | JOIN FILTER CREATE | :BF0001 | 31M | 882K | 34 | +2 | 1 | 61M | | | | | | | | 0.83 | Cpu (12) |
| 7 | HASH JOIN | | 31M | 882K | 32 | +4 | 1 | 61M | | | | | | 12M | | 0.55 | Cpu (8) |
| 8 | JOIN FILTER CREATE | :BF0003 | 2106 | 22 | 1 | +4 | 1 | 2106 | | | | | | | | | |
| 9 | PART JOIN FILTER CREATE | :BF0002 | 2106 | 22 | 1 | +4 | 1 | 2106 | | | | | | | | | |
| 10 | TABLE ACCESS STORAGE FULL | CONTRLTAB | 2106 | 22 | 1 | +4 | 1 | 2106 | | | | | | | | | |
| 11 | JOIN FILTER USE | :BF0003 | 61M | 882K | 32 | +4 | 1 | 61M | | | | | | | | | |
| 12 | PARTITION LIST JOIN-FILTER | | 61M | 882K | 32 | +4 | 1 | 61M | | | | | | | | | |
| 13 | TABLE ACCESS STORAGE FULL | STAGE | 61M | 882K | 32 | +4 | 31 | 61M | 23851 | 22GB | | | 90.22% | | | 0.14 | Cpu (2) |
| 14 | JOIN FILTER USE | :BF0001 | 157M | 18M | 73 | +35 | 1 | 71M | | | | | | | | 2.00 | Cpu (29) |
| 15 | PARTITION RANGE ITERATOR | | 157M | 18M | 73 | +35 | 1 | 147M | | | | | | | | | |
| 16 | TABLE ACCESS STORAGE FULL | TAB2 | 157M | 18M | 73 | +35 | 37 | 147M | 141K | 129GB | | | 92.93% | | | 0.48 | Cpu (6) |
| | | | | | | | | | | | | | | | | | cell smart table scan (1) |
| 17 | TABLE ACCESS STORAGE FULL | REFTAB | 524K | 377K | 17 | +151 | 1 | 2M | 10890 | 11GB | | | 99.35% | | | | |
| 18 | PARTITION RANGE AND | | 2G | 243M | 682 | +213 | 1 | 2G | | | | | | | | | |
| 19 | PARTITION HASH ALL | | 2G | 243M | 682 | +213 | 7 | 2G | | | | | | | | | |
| 20 | TABLE ACCESS STORAGE FULL | TAB1 | 2G | 243M | 682 | +213 | 70 | 2G | 813K | 667GB | | | 80.12% | | | 9.88 | Cpu (125) |
| | | | | | | | | | | | | | | | | | cell smart table scan (18) |
====================================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB2"."PART_DT"="TAB1"."PART_DT" AND "TAB2"."RRID"="TAB1"."RRID" AND "TAB2"."SID"="TAB1"."SID")
4 - access("TAB2"."MARID"=TO_NUMBER("REFTAB"."CEID"))
5 - access("TAB2"."REFNBR"="STAGE"."REFNBR")
7 - access("STAGE"."PBSDNBR"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("CONTRLTAB"."UASPDT"),'DD')) AND "STAGE"."BFID"="CONTRLTAB"."BFID")
10 - storage("CONTRLTAB"."BFLCIND"='Y') filter("CONTRLTAB"."BFLCIND"='Y')
13 - storage(SYS_OP_BLOOM_FILTER(:BF0003,"STAGE"."BFID","STAGE"."PBSDNBR")) filter(SYS_OP_BLOOM_FILTER(:BF0003,"STAGE"."BFID","STAGE"."PBSDNBR"))
16 - storage(("TAB2"."PMCD"='VV' OR "TAB2"."PMCD"='XX' OR "TAB2"."PMCD"='YY' OR "TAB2"."PMCD"='CC') AND "TAB2"."PART_DT">=TRUNC(SYSDATE@!-7) AND SYS_OP_BLOOM_FILTER(:BF0001,"TAB2"."REFNBR"))
filter(("TAB2"."PMCD"='VV' OR "TAB2"."PMCD"='XX' OR "TAB2"."PMCD"='YY' OR "TAB2"."PMCD"='CC') AND "TAB2"."PART_DT">=TRUNC(SYSDATE@!-7) AND SYS_OP_BLOOM_FILTER(:BF0001,"TAB2"."REFNBR"))
17 - storage("REFTAB"."CTCD"='EE') filter("REFTAB"."CTCD"='EE')
20 - storage("TAB1"."PART_DT">=TRUNC(SYSDATE@!-7)) filter("TAB1"."PART_DT">=TRUNC(SYSDATE@!-7))
********* PLAN -2******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 07/02/2020 04:32:55
First Refresh Time : 07/02/2020 04:32:59
Last Refresh Time : 07/02/2020 05:03:31
Duration : 1836s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 12290
Global Stats
===================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
===================================================================================================================
| 1080 | 887 | 194 | 0.02 | 0.01 | 12290 | 109M | 1M | 842GB | 11476 | 11GB | 77.88% |
===================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=824069605)
======================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | (Max) | (Max) | (%) | (# samples) |
======================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1786 | +51 | 1 | 61M | | | | | | | | 4.33 | Cpu (36) |
| | | | | | | | | | | | | | | | | | SQL*Net more data to client (11) |
| 1 | SORT ORDER BY | | 19M | 263M | 1786 | +51 | 1 | 61M | 6534 | 6GB | 6528 | 6GB | | 2G | 7G | 19.24 | Cpu (89) |
| | | | | | | | | | | | | | | | | | direct path read temp (120) |
| 2 | HASH JOIN | | 19M | 262M | 888 | +4 | 1 | 61M | | | | | | 79M | | 0.28 | Cpu (3) |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 2106 | 22 | 1 | +4 | 1 | 2106 | | | | | | | | | |
| 4 | TABLE ACCESS STORAGE FULL | CONTRLTAB | 2106 | 22 | 1 | +4 | 1 | 2106 | | | | | | | | | |
| 5 | HASH JOIN | | 39M | 262M | 891 | +1 | 1 | 61M | 4948 | 5GB | 4948 | 5GB | | 2G | 5G | 12.80 | Cpu (92) |
| | | | | | | | | | | | | | | | | | direct path read temp (47) |
| 6 | PARTITION LIST JOIN-FILTER | | 61M | 882K | 17 | +4 | 1 | 61M | | | | | | | | | |
| 7 | TABLE ACCESS STORAGE FULL | STAGE | 61M | 882K | 17 | +4 | 31 | 61M | 21766 | 20GB | | | 90.22% | | | 0.28 | Cpu (3) |
| 8 | HASH JOIN | | 38M | 261M | 773 | +19 | 1 | 147M | | | | | | 171M | | 2.12 | Cpu (23) |
| 9 | TABLE ACCESS STORAGE FULL | REFTAB | 524K | 377K | 1 | +20 | 1 | 2M | 10891 | 11GB | | | 99.35% | | | | |
| 10 | PARTITION RANGE ITERATOR | | 60M | 261M | 759 | +33 | 1 | 147M | | | | | | | | | |
| 11 | HASH JOIN | | 60M | 261M | 772 | +20 | 37 | 147M | | | | | | 2G | | 49.26 | Cpu (535) |
| 12 | TABLE ACCESS STORAGE FULL | TAB2 | 60M | 18M | 664 | +20 | 37 | 147M | 141K | 129GB | | | 92.93% | | | 0.46 | Cpu (3) |
| | | | | | | | | | | | | | | | | | cell smart table scan (2) |
| 13 | PARTITION HASH ALL | | 2G | 243M | 759 | +33 | 7 | 2G | | | | | | | | | |
| 14 | TABLE ACCESS STORAGE FULL | TAB1 | 2G | 243M | 759 | +33 | 70 | 2G | 814K | 667GB | | | 80.12% | | | 11.23 | Cpu (107) |
| | | | | | | | | | | | | | | | | | cell single block physical read (1) |
| | | | | | | | | | | | | | | | | | cell smart table scan (14) |
======================================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STAGE"."PBSDNBR"=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("CONTRLTAB"."UASPDT"),'DD')) AND "STAGE"."BFID"="CONTRLTAB"."BFID")
4 - storage("CONTRLTAB"."BFLCIND"='Y') filter("CONTRLTAB"."BFLCIND"='Y')
5 - access("TAB2"."REFNBR"="STAGE"."REFNBR")
8 - access("TAB2"."MARID"=TO_NUMBER("REFTAB"."CEID"))
9 - storage("REFTAB"."CTCD"='EE') filter("REFTAB"."CTCD"='EE')
11 - access("TAB2"."PART_DT"="TAB1"."PART_DT" AND "TAB2"."RRID"="TAB1"."RRID" AND "TAB2"."SID"="TAB1"."SID")
12 - storage(("TAB2"."PMCD"='VV' OR "TAB2"."PMCD"='XX' OR "TAB2"."PMCD"='YY' OR "TAB2"."PMCD"='CC') AND "TAB2"."PART_DT">=TRUNC(SYSDATE@!-7))
filter(("TAB2"."PMCD"='VV' OR "TAB2"."PMCD"='XX' OR "TAB2"."PMCD"='YY' OR "TAB2"."PMCD"='CC') AND "TAB2"."PART_DT">=TRUNC(SYSDATE@!-7))
14 - storage("TAB1"."PART_DT">=TRUNC(SYSDATE@!-7)) filter("TAB1"."PART_DT">=TRUNC(SYSDATE@!-7))