Hello Listers, Its oracle database version 19C. Something odd behaviour we
are seeing , one of the queries which used to run fine suddenly started to
fail with Ora-01652, (consuming ~1TB+ temp space) after our infra team
applied a 19.15 patch. Basically it was version 19.11 earlier and was
working fine.
The sql monitor i have published below is from the actual query when it was
running successfully VS when it failed. In the sql monitor the line number
-30 is the one which is making the query fail now. The plan is mostly the
same in both the cases in that section. In one case it sorted ~7billion
rows with ~20GB temp space but in the other it consumed ~1TB+ temp space
and failed while it was sorting ~10 billion rows result.
Below is the access predicate which gets evaluated for the line no- 31
i.e."HASH JOIN RIGHT OUTER". As we have functions used in the predicates
during the out join evaluation, So wondering , if there are any bugs
related to how the Outer join gets evaluated in presence of functions in
the predicate or how the analytical functions works in 19.11 vs 19.15?
Below is the access Predicate for "HASH JOIN RIGHT OUTER" i.e. Plan line id
- 31:-
access("C"."PANL_CNT"(+)=LENGTH("T"."C_NBR") AND
"C"."BI_NBR"(+)=SUBSTR("T"."C_NBR",1,4))
filter("T"."C_NBR"<="C"."HIGH_VALUE"(+) AND
"T"."C_NBR">="C"."LOW_VALUE"(+))
********************* Execution which is failing in 19.15 version with high
temp space consumption**********************
https://gist.github.com/databasetech0073/05a8ccfb7022eb9dadb508d40286edb3
===================================================================================================================================================================================================================================================================================
| Id | Operation
| Name | Rows | Cost | Time | Start |
Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity
| Activity Detail |
| |
| | (Estim) | | Active(s) | Active |
| (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) |
(# samples) |
===================================================================================================================================================================================================================================================================================
| 25 | PX SEND HASH
| :TQ10011 | 9M | 4M | | |
| | | | | | . | . | |
|
| 26 | VIEW
| | 9M | 4M | | |
| | | | | | . | . | |
|
| 27 | WINDOW SORT PUSHED RANK
| | 9M | 4M | | |
| | | | | | . | . | |
|
| 28 | PX RECEIVE
| | 9M | 4M | | |
| | | | | | . | . | |
|
| 29 | PX SEND HASH
| :TQ10010 | 9M | 4M | | |
16 | | | | | | . | . | |
|
| 30 | WINDOW CHILD PUSHED RANK
| | 9M | 4M | 3112 | +915 |
16 | 0 | | | 3M | 1TB | 9GB | 1TB | 0.06 |
Cpu (7) |
| 31 | HASH JOIN RIGHT OUTER
| | 9M | 3M | 3112 | +915 |
16 | 10G | | | | | 648MB | . | 0.08 |
Cpu (9) |
| 32 | BUFFER SORT
| | | | 8 | +908 |
16 | 4M | | | | | 350MB | . | 0.01 |
Cpu (1) |
********************* Execution which was running fine in 19.11 version
**********************
https://gist.github.com/databasetech0073/d123af1aa4c0c4a0716725103c3c57c9
SQL Plan Monitoring Details (Plan Hash Value=2056239125)
=================================================================================================================================================================================================================================================================================
| 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) |
=================================================================================================================================================================================================================================================================================
| 28 | PX RECEIVE
| | 9M | 3M | 86 | +1893 |
16 | 70M | | | | | | | |
0.11 | Cpu (19) |
| 29 | PX SEND HASH
| :TQ10010 | 9M | 3M | 77 | +1893 |
16 | 70M | | | | | | | |
0.25 | Cpu (43) |
| 30 | WINDOW CHILD PUSHED RANK
| | 9M | 3M | 1752 | +218 |
16 | 70M | 146K | 19GB | 101K | 19GB | | 4G | 10G |
41.97 | Cpu (6748) |
| |
| | | | | |
| | | | | | | | |
| direct path read temp (553) |
| |
| | | | | |
| | | | | | | | |
| direct path write temp (47) |
| 31 | HASH JOIN RIGHT OUTER
| | 9M | 2M | 1676 | +218 |
16 | 7G | | | | | | 217M | |
23.08 | Cpu (4041) |
| 32 | BUFFER SORT
| | | | 4 | +216 |
16 | 1M | | | | | | 97M | |
| |
The actual query is a big one. But I am just putting here the key section
of the query which is causing those window functions to be evaluated and
thus exploding the temp space.
WITH C
AS (SELECT CP.CPCL_NBR - X.RN AS CPCL_NBR, SUBSTR (CP.CP_NBR, 1, 4)
AS BI_NBR,
CASE CP.PANL_CNT
WHEN 0 THEN 16
ELSE CP.PANL_CNT
END AS PANL_CNT,
RPAD ( SUBSTR (CP.CP_NBR,1,CP.CPCL_NBR - X.RN),
CASE CP.PANL_CNT
WHEN 0 THEN 16
ELSE CP.PANL_CNT
END, '0') AS LOW_VALUE,
RPAD ( SUBSTR (CP.CP_NBR, 1,CP.CPCL_NBR - X.RN),
CASE CP.PANL_CNT
WHEN 0 THEN 16
ELSE CP.PANL_CNT
END, '9') AS HIGH_VALUE
FROM USER1.CP CP
INNER JOIN ( SELECT ROWNUM - 1 AS RN
FROM DUAL
CONNECT BY LEVEL <= 18) X
ON CP.CPCL_NBR - X.RN >= 4
WHERE CP.PN_CD IN ('XX', 'YY'))
SELECT
ROW_NUMBER () OVER (PARTITION BY T.DS_NO, T.C_NBR ORDER BY C.CPCL_NBR
DESC) AS CP_RANK,
COUNT (*) OVER ( PARTITION BY T.DS_NO, T.C_NBR,C.CPCL_NBR) AS
CL_MATCHES,
COUNT (*) OVER (PARTITION BY T.DS_NO, T.C_NBR) AS NO_MATCHES
FROM (select T.DS_NO, T.PM_FK, T.AC_FK, T.RT_FK, T.CT_FK.....
from USER1.T T
WHERE T.HS_DATE_TIME = to_date(:B1,'MM/DD/YYYY HH24:MI:SS')
) T
LEFT OUTER JOIN C
ON SUBSTR (T.C_NBR, 1, 4) = C.BI_NBR
AND LENGTH (T.C_NBR) = C.PANL_CNT
AND T.C_NBR BETWEEN C.LOW_VALUE AND C.HIGH_VALUE;