Hi we are using version 11.2.0.4 of oracle Exadata. We have below query which we want to improve/reduce the run duration. From the sql monitor i saw the query is actually spending significant time on reading/writing to temp during the HASH join operation. We do see there are other queries running at same time which are also spilling to temp and team saying it must be the effect of contention in reading/writing to temp causing the query running longer during that specific time, and by creating tempspace group will possibly make it better, so want to know experts view, if there is any other wait event we will see apart from "direct path read temp" or "direct path write temp", so that will confirm the issue is due to contention in tempspace read/write?
I tried executing the query by setting the HASH_AREA_SIZE as 2GB(which i suppose is the max limit we can set at session level) and workarea_size_policy as MANUAL, i see the query is finishing in quicker time as compared to before. So is this the only way we can make this query run faster and if anything need to be taken care before setting these in session level value so that it wont afect others negatively? Or any other option exists here to tune this query? I see in gv$parameter, we have pga_agreegate_size set as ~40GB and Hash_area_size set as "131072" and "sort_area_size" as "65536". Should we revisit/change these parameters?
I have mentioned below the sql monitor for the main query and the one by manual setting the HASH_AREA_SIZE. We have table tab1 and tab2 both daily range partitioned holding ~200million records in each partition and here we does partition prune to fetch one day worth of data.
SELECT COUNT (*) cnt, TAB1.DID, TAB1.SID, TAB1.SRID, TAB1.MCD,TAB1.CCD,TAB1.SCD,TAB1.SDT
FROM TAB1, TAB2, TMTD,TMC
WHERE TAB1.SDT = TRUNC (SYSDATE) - 1
AND TAB1.TDID = TAB2.TDID
AND TAB1.SDT = TAB2.SDT
AND TAB2.ASCD NOT IN ('X', 'Y')
AND TAB1.DID = TMTD.DID
AND TMTD.CID = TMC.CID
AND TMC.PTID = '1'
AND TAB1.ACD NOT IN ('XX', 'YY')
AND NOT ( TAB1.MCD = 'XX'
AND TAB1.ACD IN ('XX', 'YY'))
GROUP BY TAB1.DID, TAB1.SID, TAB1.SRID,TAB1.MCD,TAB1.CCD,TAB1.SCD,TAB1.SDT;
****************** query sql monitor without any modification*************** | |
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL ID : 6w5dxjaw4q8g4
SQL Execution ID : 67108864
Execution Started : 08/30/2018 04:14:53
First Refresh Time : 08/30/2018 04:14:53
Last Refresh Time : 08/30/2018 05:04:30
Duration : 2977s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 368
Global Stats
==============================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
==============================================================================================================================
| 2960 | 129 | 2820 | 0.00 | 0.00 | 11 | 368 | 12M | 127K | 99GB | 17065 | 4GB | 72.14% |
==============================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1527022616)
===========================================================================================================================================================================================================================================
| 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 | | | | 2411 | +567 | 1 | 2M | | | | | | | | 0.54 | Cpu (1) |
| | | | | | | | | | | | | | | | | | SQL*Net more data to client (15) |
| 1 | HASH GROUP BY | | 107M | 6M | 2411 | +567 | 1 | 2M | | | | | | 284M | | 0.51 | Cpu (15) |
| 2 | HASH JOIN | | 107M | 4M | 2945 | +1 | 1 | 22M | 17065 | 4GB | 17065 | 4GB | | 84M | 4G | 97.53 | Cpu (50) |
| | | | | | | | | | | | | | | | | | direct path read temp (2341) |
| | | | | | | | | | | | | | | | | | direct path write temp (487) |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 105M | 942K | 522 | +2 | 1 | 104M | | | | | | | | 0.54 | Cpu (16) |
| 4 | PARTITION RANGE SINGLE | | 105M | 942K | 522 | +2 | 1 | 104M | | | | | | | | | |
| 5 | TABLE ACCESS STORAGE FULL | TAB2 | 105M | 942K | 522 | +2 | 1 | 104M | 44411 | 41GB | | | 93.96% | 7M | | 0.07 | Cpu (2) |
| 6 | HASH JOIN | | 184M | 2M | 43 | +523 | 1 | 42M | | | | | | 49M | | 0.17 | Cpu (5) |
| 7 | JOIN FILTER CREATE | :BF0001 | 1M | 9461 | 3 | +523 | 1 | 1M | | | | | | | | | |
| 8 | HASH JOIN | | 1M | 9461 | 3 | +523 | 1 | 1M | | | | | | 34M | | | |
| 9 | TABLE ACCESS STORAGE FULL | TMC | 755K | 6148 | 1 | +523 | 1 | 757K | | | | | | | | 0.03 | Cpu (1) |
| 10 | INDEX STORAGE FAST FULL SCAN | TMTD_IX1 | 1M | 1084 | 3 | +523 | 1 | 1M | | | | | | | | | |
| 11 | JOIN FILTER USE | :BF0001 | 183M | 1M | 41 | +525 | 1 | 59M | | | | | | | | 0.24 | Cpu (7) |
| 12 | PARTITION RANGE SINGLE | | 183M | 1M | 41 | +525 | 1 | 183M | | | | | | | | | |
| 13 | TABLE ACCESS STORAGE FULL | TAB1 | 183M | 1M | 41 | +525 | 1 | 183M | 65286 | 54GB | | | 81.41% | 7M | | 0.37 | Cpu (11) |
===========================================================================================================================================================================================================================================
1830642 rows selected.
Elapsed: 00:49:37.31
Execution Plan
----------------------------------------------------------
Plan hash value: 1527022616
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106M| 8956M| | 5620K (1)| 18:44:11 | | |
| 1 | HASH GROUP BY | | 106M| 8956M| 10G| 5620K (1)| 18:44:11 | | |
|* 2 | HASH JOIN | | 106M| 8956M| 3012M| 3606K (1)| 12:01:18 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 105M| 1807M| | 941K (2)| 03:08:19 | | |
| 4 | PARTITION RANGE SINGLE | | 105M| 1807M| | 941K (2)| 03:08:19 | KEY | KEY |
|* 5 | TABLE ACCESS STORAGE FULL | TAB2 | 105M| 1807M| | 941K (2)| 03:08:19 | KEY | KEY |
|* 6 | HASH JOIN | | 184M| 11G| 40M| 1800K (1)| 06:00:08 | | |
| 7 | JOIN FILTER CREATE | :BF0001 | 1408K| 24M| | 9461 (1)| 00:01:54 | | |
|* 8 | HASH JOIN | | 1408K| 24M| 13M| 9461 (1)| 00:01:54 | | |
|* 9 | TABLE ACCESS STORAGE FULL | TMC | 754K| 5160K| | 6148 (1)| 00:01:14 | | |
| 10 | INDEX STORAGE FAST FULL SCAN| TMTD_IX1 | 1414K| 14M| | 1084 (1)| 00:00:14 | | |
| 11 | JOIN FILTER USE | :BF0001 | 183M| 9080M| | 1234K (2)| 04:06:51 | | |
| 12 | PARTITION RANGE SINGLE | | 183M| 9080M| | 1234K (2)| 04:06:51 |KEY(AP)|KEY(AP)|
|* 13 | TABLE ACCESS STORAGE FULL | TAB1 | 183M| 9080M| | 1234K (2)| 04:06:51 |KEY(AP)|KEY(AP)|
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB1"."SDT"="TAB2"."SDT" AND
"TAB1"."TDID"="TAB2"."TDID")
5 - storage("TAB2"."ASCD"<>'X' AND "TAB2"."ASCD"<>'Y' AND
"TAB2"."SDT"=TRUNC(SYSDATE@!)-1)
filter("TAB2"."ASCD"<>'X' AND "TAB2"."ASCD"<>'Y' AND
"TAB2"."SDT"=TRUNC(SYSDATE@!)-1)
6 - access("TAB1"."DID"="TMTD"."DID")
8 - access("TMTD"."CID"="TMC"."CID")
9 - storage("TMC"."PTID"='1')
filter("TMC"."PTID"='1')
13 - storage(("TAB1"."MCD"<>'XX' OR "TAB1"."ACD"<>'YY' AND
"TAB1"."ACD"<>'XX') AND "TAB1"."ACD"<>'XX' AND
"TAB1"."ACD"<>'YY' AND "TAB1"."SDT"=TRUNC(SYSDATE@!)-1 AND
SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."DID"))
filter(("TAB1"."MCD"<>'XX' OR "TAB1"."ACD"<>'YY' AND
"TAB1"."ACD"<>'XX') AND "TAB1"."ACD"<>'XX' AND
"TAB1"."ACD"<>'YY' AND "TAB1"."SDT"=TRUNC(SYSDATE@!)-1 AND
SYS_OP_BLOOM_FILTER(:BF0001,"TAB1"."DID"))
Statistics
----------------------------------------------------------
4268 recursive calls
0 db block gets
12464769 consistent gets
12952247 physical reads
536 redo size
59895274 bytes sent via SQL*Net to client
4498 bytes received via SQL*Net from client
368 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1830642 rows processed
****************** query sql monitor with session level HASH_AREA_SIZE set to 2GB***************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL ID : fwjyvpk6k8z2g
SQL Execution ID : 67108864
Execution Started : 08/30/2018 04:40:40
First Refresh Time : 08/30/2018 04:40:40
Last Refresh Time : 08/30/2018 05:03:54
Duration : 1394s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 368
Global Stats
============================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
============================================================================================================================================
| 1375 | 218 | 1103 | 0.00 | 3.28 | 2.13 | 48 | 368 | 12M | 120K | 101GB | 5894 | 6GB | 66.67% |
============================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1869260387)
=============================================================================================================================================================================================================================================
| 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 | | | | 1270 | +125 | 1 | 2M | | | | | | | | 9.19 | gc current block busy (1) |
| | | | | | | | | | | | | | | | | | buffer busy waits (3) |
| | | | | | | | | | | | | | | | | | Cpu (3) |
| | | | | | | | | | | | | | | | | | DFS lock handle (42) |
| | | | | | | | | | | | | | | | | | cell single block physical read (3) |
| | | | | | | | | | | | | | | | | | local write wait (73) |
| | | | | | | | | | | | | | | | | | read by other session (1) |
| 1 | HASH GROUP BY | | 106M | 5M | 1010 | +385 | 1 | 2M | | | | | | 413M | | 0.51 | Cpu (7) |
| 2 | HASH JOIN | | 106M | 2M | 1372 | +2 | 1 | 22M | | | | | | 108M | | 0.36 | Cpu (5) |
| 3 | TABLE ACCESS STORAGE FULL | TMC | 755K | 6148 | 1 | +2 | 1 | 757K | | | | | | | | | |
| 4 | HASH JOIN | | 106M | 2M | 1372 | +2 | 1 | 102M | | | | | | 78M | | 1.46 | Cpu (20) |
| 5 | JOIN FILTER CREATE | :BF0000 | 1M | 1084 | 2 | +1 | 1 | 1M | | | | | | | | 0.07 | Cpu (1) |
| 6 | INDEX STORAGE FAST FULL SCAN | TMTD_IX1 | 1M | 1084 | 1 | +2 | 1 | 1M | | | | | | | | | |
| 7 | HASH JOIN | | 106M | 2M | 1367 | +7 | 1 | 102M | 5894 | 6GB | 5894 | 6GB | | 2G | 6G | 85.85 | Cpu (153) |
| | | | | | | | | | | | | | | | | | direct path read temp (802) |
| | | | | | | | | | | | | | | | | | direct path write temp (222) |
| 8 | PART JOIN FILTER CREATE | :BF0001 | 105M | 942K | 374 | +8 | 1 | 104M | | | | | | | | 0.80 | Cpu (11) |
| 9 | PARTITION RANGE SINGLE | | 105M | 942K | 374 | +8 | 1 | 104M | | | | | | | | | |
| 10 | TABLE ACCESS STORAGE FULL | TAB2 | 105M | 942K | 380 | +2 | 1 | 104M | 44439 | 41GB | | | 93.96% | | | 0.73 | Cpu (5) |
| | | | | | | | | | | | | | | | | | reliable message (5) |
| 11 | JOIN FILTER USE | :BF0000 | 183M | 1M | 129 | +385 | 1 | 183M | | | | | | | | 0.15 | Cpu (2) |
| 12 | PARTITION RANGE SINGLE | | 183M | 1M | 129 | +385 | 1 | 183M | | | | | | | | | |
| 13 | TABLE ACCESS STORAGE FULL | TAB1 | 183M | 1M | 129 | +385 | 1 | 183M | 65280 | 54GB | | | 81.41% | | | 0.88 | Cpu (11) |
| | | | | | | | | | | | | | | | | | cell smart table scan (1) |
=============================================================================================================================================================================================================================================
1830642 rows selected.
Elapsed: 00:23:13.69
Execution Plan
----------------------------------------------------------
Plan hash value: 1869260387
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105M| 8870M| | 4548K (1)| 15:09:41 | | |
| 1 | HASH GROUP BY | | 105M| 8870M| 9715M| 4548K (1)| 15:09:41 | | |
|* 2 | HASH JOIN | | 105M| 8870M| | 2408K (2)| 08:01:48 | | |
|* 3 | TABLE ACCESS STORAGE FULL | TMC | 754K| 5160K| | 6148 (1)| 00:01:14 | | |
|* 4 | HASH JOIN | | 106M| 8200M| | 2402K (2)| 08:00:30 | | |
| 5 | JOIN FILTER CREATE | :BF0000 | 1414K| 14M| | 1084 (1)| 00:00:14 | | |
| 6 | INDEX STORAGE FAST FULL SCAN| TMTD_IX1 | 1414K| 14M| | 1084 (1)| 00:00:14 | | |
|* 7 | HASH JOIN | | 106M| 7086M| 3012M| 2401K (2)| 08:00:13 | | |
| 8 | PART JOIN FILTER CREATE | :BF0001 | 105M| 1807M| | 941K (2)| 03:08:19 | | |
| 9 | PARTITION RANGE SINGLE | | 105M| 1807M| | 941K (2)| 03:08:19 | KEY | KEY |
|* 10 | TABLE ACCESS STORAGE FULL | TAB2 | 105M| 1807M| | 941K (2)| 03:08:19 | KEY | KEY |
| 11 | JOIN FILTER USE | :BF0000 | 183M| 9080M| | 1234K (2)| 04:06:51 | | |
| 12 | PARTITION RANGE SINGLE | | 183M| 9080M| | 1234K (2)| 04:06:51 |KEY(AP)|KEY(AP)|
|* 13 | TABLE ACCESS STORAGE FULL | TAB1 | 183M| 9080M| | 1234K (2)| 04:06:51 |KEY(AP)|KEY(AP)|
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TMTD"."CID"="TMC"."CID")
3 - storage("TMC"."PTID"='1')
filter("TMC"."PTID"='1')
4 - access("TAB1"."DID"="TMTD"."DID")
7 - access("TAB1"."SDT"="TAB2"."SDT" AND
"TAB1"."TDID"="TAB2"."TDID")
10 - storage("TAB2"."ASCD"<>'X' AND "TAB2"."ASCD"<>'Y' AND
"TAB2"."SDT"=TRUNC(SYSDATE@!)-1)
filter("TAB2"."ASCD"<>'X' AND "TAB2"."ASCD"<>'Y' AND
"TAB2"."SDT"=TRUNC(SYSDATE@!)-1)
13 - storage(("TAB1"."MCD"<>'XX' OR "TAB1"."ACD"<>'YY' AND
"TAB1"."ACD"<>'XX') AND "TAB1"."ACD"<>'XX' AND
"TAB1"."ACD"<>'YY' AND "TAB1"."SDT"=TRUNC(SYSDATE@!)-1 AND
SYS_OP_BLOOM_FILTER(:BF0000,"TAB1"."DID"))
filter(("TAB1"."MCD"<>'XX' OR "TAB1"."ACD"<>'YY' AND
"TAB1"."ACD"<>'XX') AND "TAB1"."ACD"<>'XX' AND
"TAB1"."ACD"<>'YY' AND "TAB1"."SDT"=TRUNC(SYSDATE@!)-1 AND
SYS_OP_BLOOM_FILTER(:BF0000,"TAB1"."DID"))
Statistics
----------------------------------------------------------
5895 recursive calls
21455 db block gets
12464769 consistent gets
13176126 physical reads
148 redo size
42683726 bytes sent via SQL*Net to client
4498 bytes received via SQL*Net from client
368 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1830642 rows processed