Re: high temp space usage for same analytic function on latest version

Hi Yudhi,

Could you also show execution plans with 'advanced' format, please? First of all, I would check their "projection" sections.

On Thu, Nov 10, 2022 at 8:06 PM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> 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;
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l