Hi, its version 11.2.0.4 of Oracle Exadata. We have below query which runs sometimes fast(<1minutes) and sometimes its running for >15minutes. After looking into details, we saw its the variable tempspace spill during HASH JOIN adding to the additional times. But we are expecting it to finish in <~1 minute always. During the slow execution most of the time its spending at line no-6 followed by line no- 2 in the execution path with wait event "Direct path read temp". So wanted to understand , what are the possible way to fix this issue?
I understand by setting the workarea_size_policy to MANUAL and hash_area_size to 2GB, we can get those execution happened fully in memory, but that would be a code change and also considering this query sometimes executing concurrently from many sessions(~50+ executions) , so that seems risky. And all the column which we were exposing out of the table sin the query are all required ones only. Is there any other option we can opt to fix this issue in short term using some hints(a better path may be) and then may opt for some code change for long term fix?
Also this tempspill started increasing gradually from past 15 days, what must be the reason behind it, can it be the volume of the base table increased or it can be bind variable dependent? I am not able to verify/confirm any of these though.
Below is the sample sql and sql monitor. I also attached the same as the code format seems not working fine.
SELECT ....
FROM (SELECT ....
FROM "USER1"."BOS" "A2"
FULL OUTER JOIN
(SELECT ...
FROM "USER1"."CS" "A4"
FULL OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
WHERE "A1"."BI0" = :b1
OR "A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND ( "A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
attach_1.txt (5.16 KB).
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL Execution ID : 16777403
Execution Started : 12/22/2020 10:08:16
First Refresh Time : 12/22/2020 10:08:20
Last Refresh Time : 12/22/2020 10:26:15
Duration : 1080s
Global Stats
==================================================================================
| Elapsed | Cpu | IO | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
==================================================================================
| 1124 | 120 | 1004 | 81196 | 808K | 92GB | 7100 | 832MB | -2.04% |
==================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2475526405)
=======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
=======================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | | | |
| -> 1 | VIEW | VW_FOJ_0 | 2M | 42883 | 1072 | +8 | 1 | 0 | | | | | | | | | |
| -> 2 | HASH JOIN FULL OUTER | | 2M | 42883 | 1079 | +2 | 1 | 390K | 147K | 17GB | 2979 | 349MB | 1M | 559M | 17.55 | Cpu (7) | 8% |
| | | | | | | | | | | | | | | | | direct path read temp (180) | |
| | | | | | | | | | | | | | | | | direct path write temp (2) | |
| 3 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3044 | 1 | +4 | 1 | 1M | | | | | | | | | |
| 4 | VIEW | | 2M | 16655 | 888 | +8 | 1 | 2M | | | | | | | | | |
| 5 | VIEW | VW_FOJ_1 | 2M | 16655 | 888 | +8 | 1 | 2M | | | | | | | | | |
| 6 | HASH JOIN FULL OUTER | | 2M | 16655 | 892 | +4 | 1 | 2M | 661K | 76GB | 2583 | 303MB | | | 82.45 | Cpu (56) | |
| | | | | | | | | | | | | | | | | direct path read temp (829) | |
| | | | | | | | | | | | | | | | | direct path write temp (3) | |
| 7 | TABLE ACCESS STORAGE FULL | CS | 944K | 2683 | 3 | +4 | 1 | 948K | | | | | | | | | |
| 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 3 | +6 | 1 | 2M | | | | | | | | | |
=======================================================================================================================================================================================================================
1 - filter(("A3"."BID1"=:B1 OR ("A2"."BI"=:B2 AND "A2"."I_DT" IS NULL
AND (("A3"."BID1" IS NOT NULL AND "A3"."CT3"='XXX') OR ("A3"."BID1" IS
NULL AND "A3"."CT3"='YYY')))))
2 - access("A2"."BI"="A3"."BID1" AND
"A2"."OID"=TO_NUMBER("A3"."OID2"))
6 - access("A5"."EID"="A4"."EID")