high temp space usage for same analytic function on latest version
Date: Fri, 11 Nov 2022 01:36:13 +0530
Message-ID: <CAEzWdqdBOn4cL7Es5ur5qq5Hy1v7SiR6SpCbLPqey3=3e-GDgQ_at_mail.gmail.com>
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;
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 10 2022 - 21:06:13 CET