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

It seems, as I have attached the output of v$sesstat and v$session_event as an extract from excel sheet to the email, so somehow list is not letting me to reply/forward with that content to the list group distribution. It's getting bounced back each time. Not sure if it's going to personal email also or not.

By the way I tried settings below parameters in session level and tried the query but still no relax in temp usage.

As we dont really want to rollback the change and go back to 19.11, so we are looking if any way to make this query work with lesser temp space consumption and make it finish. It seems the hash outer join which is yielding billion of rows doing some sort of Cartesian.

alter session set "_shrunk_aggs_enabled" = true; alter session set "_disable_adaptive_shrunk_aggregation" = true;

On Fri, 11 Nov, 2022, 10:42 pm Ghassan Salem, <salem.ghassan_at_gmail.com> wrote:

> Hi
> Can you try these settings:
> alter session set "_shrunk_aggs_enabled" = true;
> alter session set "_disable_adaptive_shrunk_aggregation" = true;
>
> And rerun your query ?
>
> Regards
>
> On Fri 11 Nov 2022 at 18:07, yudhi s <learnerdatabase99_at_gmail.com> wrote:
>
>> Thank You Sayan, Jonathan.
>>
>> We reran the query and it took a different plan hash value , however the
>> path is almost similar wrt the line which does "Outer join" and the
>> "analytical function" evaluation. I have posted the specific section of sql
>> monitor below with predicate and column projection for that run. However,
>> the sql monitor shows status as 'error' out after sometime(~30minutes))
>> but the underlying sessions(~33 parallel sessions) kept on running for
>> 2hrs+, until then I saw ~2TB of temp space already consumed and later it
>> failed with insufficient temp space error.
>>
>> I got the display cursor plan from the older time (when we had the DB
>> version 11.2.0.4) which I captured in the past. But yes the particular
>> section of the plan i.e. "HASH JOIN RIGHT OUTER" and the "WINDOW CHILD
>> PUSHED RANK" are the same and I am not seeing any difference in the
>> predicate or column projection section either, if we compare with today's
>> plan. The column projection and predicate section for these two lines as
>> below.
>>
>> Here the table "T" is declared as DEGREE- 16 and thus the query is
>> running in parallel-16 threads. Also i understand, the volume of the rows
>> to be passed from the 'outer join' to the Analytical function seems bumped
>> up from ~7billion to 10billion+ but even with that increase in volume, how
>> come the temp space consumption can go from ~<50GB to ~2TB+? And also i am
>> looking for any other way to make this query finish here with minimal temp
>> space consumption?
>>
>> ******************* Display_cursor output and the predicate and column
>> projection section from the 11.2.0.4 time plan **************
>>
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> | Id | Operation | Name | Rows | Bytes
>> |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
>>
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> |* 20 | VIEW | | 47904 | 11M|
>> | 1823K (14)| 06:04:37 | | | Q1,05 | PCWP | |
>> |* 21 | WINDOW SORT PUSHED RANK | | 47904
>> | 200M| 374M| 1823K (14)| 06:04:37 | | | Q1,05 | PCWP | |
>> | 22 | PX RECEIVE | | 47904 | 200M|
>> | 1823K (14)| 06:04:37 | | | Q1,05 | PCWP | |
>> | 23 | PX SEND HASH | :TQ10004 | 47904 |
>> 200M| | 1823K (14)| 06:04:37 | | | Q1,04 | P->P | HASH |
>> |* 24 | WINDOW CHILD PUSHED RANK | | 47904
>> | 200M| | 1823K (14)| 06:04:37 | | | Q1,04 | PCWP | |
>> |* 25 | HASH JOIN OUTER | | 47904 |
>> 200M| | 1823K (14)| 06:04:37 | | | Q1,04 | PCWP | |
>> | 26 | PX RECEIVE | | 47904 | 15M|
>> | 1823K (14)| 06:04:37 | | | Q1,04 | PCWP | |
>> | 27 | PX SEND HASH | :TQ10003 | 47904 |
>> 15M| | 1823K (14)| 06:04:37 | | | Q1,03 | P->P | HASH |
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>> 20 - filter("S"."CP_RANK"=1)
>> 21 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DS_NO","T"."C_NBR"
>> ORDER BY INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )<=1)
>> 24 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DS_NO","T"."C_NBR"
>> ORDER BY INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )<=1)
>> 25 - 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"))
>>
>> Column Projection Information (identified by operation id):
>> -----------------------------------------------------------
>> 20 - "S"."PM_FK"[RAW,100], "S"."BI_NBR"[VARCHAR2,6],
>> "S"."PE_FK"[RAW,100], "S"."CT_FK"[RAW,100], "S"."RT_CD"[VARCHAR2,100],
>> "S"."C2L_CD"[VARCHAR2,2], "S"."DAY_PK"[DATE,7],
>> "S"."TRANS_AMT"[NUMBER,22], "S"."CP_RANK"[NUMBER,22]
>> 21 - (#keys=3) "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> INTERNAL_FUNCTION("C"."CPCL_NBR")[22], LENGTH("T"."C_NBR")[22],
>> "C"."PANL_CNT"[NUMBER,22], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."BI_NBR"[VARCHAR2,4], "C"."DC_CNT"[NUMBER,22], "T"."PM_FK"[RAW,100],
>> "T"."CT_FK"[RAW,100], "T"."PE_FK"[RAW,100],
>> "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7],
>> "T"."CT_CD"[VARCHAR2,100], "T"."RT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."LOW_VALUE"[VARCHAR2,4000],
>> "C"."IC2L_CD"[VARCHAR2,2], ROW_NUMBER() OVER ( PARTITION BY
>> "T"."DS_NO","T"."C_NBR" ORDER BY
>> INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )[22]
>> 22 - "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> INTERNAL_FUNCTION("C"."CPCL_NBR")[22], LENGTH("T"."C_NBR")[22],
>> "C"."PANL_CNT"[NUMBER,22],
>> SUBSTR("T"."C_NBR",1,4)[4], "C"."BI_NBR"[VARCHAR2,4],
>> "C"."DC_CNT"[NUMBER,22], "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100],
>> "T"."PE_FK"[RAW,100], "T"."TRANS_AMT"[NUMBER,22],
>> "T"."DAY_PK"[DATE,7], "T"."CT_CD"[VARCHAR2,100], "T"."RT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2]
>> 23 - (#keys=2) "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> INTERNAL_FUNCTION("C"."CPCL_NBR")[22], LENGTH("T"."C_NBR")[22],
>> "C"."PANL_CNT"[NUMBER,22], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."BI_NBR"[VARCHAR2,4], "C"."DC_CNT"[NUMBER,22], "T"."PM_FK"[RAW,100],
>> "T"."CT_FK"[RAW,100], "T"."PE_FK"[RAW,100],
>> "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7],
>> "T"."CT_CD"[VARCHAR2,100], "T"."RT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."LOW_VALUE"[VARCHAR2,4000],
>> "C"."IC2L_CD"[VARCHAR2,2]
>> 24 - (#keys=3) "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> INTERNAL_FUNCTION("C"."CPCL_NBR")[22], LENGTH("T"."C_NBR")[22],
>> "C"."PANL_CNT"[NUMBER,22], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."BI_NBR"[VARCHAR2,4], "C"."DC_CNT"[NUMBER,22], "T"."PM_FK"[RAW,100],
>> "T"."CT_FK"[RAW,100], "T"."PE_FK"[RAW,100],
>> "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7],
>> "T"."CT_CD"[VARCHAR2,100], "T"."RT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."LOW_VALUE"[VARCHAR2,4000],
>> "C"."IC2L_CD"[VARCHAR2,2], ROW_NUMBER() OVER ( PARTITION BY
>> "T"."DS_NO","T"."C_NBR" ORDER BY
>> INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )[22]
>> 25 - (#keys=2) LENGTH("T"."C_NBR")[22], "C"."PANL_CNT"[NUMBER,22],
>> SUBSTR("T"."C_NBR",1,4)[4], "C"."BI_NBR"[VARCHAR2,4],
>> "T"."DS_NO"[NUMBER,22],
>> "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100], "T"."PE_FK"[RAW,100],
>> "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7],
>> "T"."CT_CD"[VARCHAR2,100],
>> "T"."RT_CD"[VARCHAR2,100], "T"."C_NBR"[VARCHAR2,100],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "C"."CPCL_NBR"[NUMBER,22],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."DC_CNT"[NUMBER,22]
>>
>>
>>
>> ******************* Sql monitor output and the predicate and column
>> projection section from the current version i.e. 19.5 time **************
>>
>> SQL Plan Monitoring Details (Plan Hash Value=2917514310)
>>
>> ==================================================================================================================================================================================================================================================================================
>> | 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) |
>>
>> =================================================================================================================================================================================================================================================================================
>> | 23 | VIEW | | 5M | 44M |
>> | | | | | | | | . | . | |
>> |
>> | 24 | WINDOW SORT PUSHED RANK | |
>> 5M | 44M | | | | | | | | | . | . | |
>> |
>> | 25 | PX RECEIVE | | 5M |
>> 44M | | | | | | | | | . | . | |
>> |
>> | 26 | PX SEND HASH | :TQ10009 | 5M
>> | 44M | | | 16 | | | | | | . | . | |
>> |
>> | 27 | WINDOW CHILD PUSHED RANK | |
>> 5M | 44M | 2681 | +932 | 16 | 0 | | | 2M | 1TB | 6GB |
>> 1TB | | |
>> | 28 | HASH JOIN RIGHT OUTER | | 5M
>> | 39M | 2681 | +932 | 16 | 7G | | | | | 718MB | . |
>> | |
>> | 29 | BUFFER SORT | | |
>> | 7 | +926 | 16 | 4M | | | | | 356MB | . | 0.02 |
>> Cpu (2) |
>> | 30 | PX RECEIVE | | 37989 |
>> 62 | 7 | +926 | 16 | 4M | | | | | . | . | |
>> |
>> | 31 | PX SEND HASH | :TQ10002 |
>> 37989 | 62 | 5 | +926 | 1 | 3M | | | | | . | .
>> | | |
>> | 32 | VIEW | | 37989 | 62
>> | 5 | +926 | 1 | 3M | | | | | . | . | |
>> |
>> | 33 | TABLE ACCESS STORAGE FULL |
>> SYS_TEMP_0FD9D7FC5_38C7BCF3 | 37989 | 62 | 7 | +925 | 1 | 3M |
>> 299 | 298MB | | | . | . | 0.05 | Cpu (1) |
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>> 23 - filter("S"."CP_RANK"=1)
>> 24 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DS_NO","T"."C_NBR"
>> ORDER BY INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )<=1)
>> 27 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DS_NO","T"."C_NBR"
>> ORDER BY INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )<=1)
>> 28 - 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"))
>>
>> Column Projection Information (identified by operation id):
>> -----------------------------------------------------------
>> 23 - "S"."PM_FK"[RAW,100], "S"."BI_NBR"[VARCHAR2,6],
>> "S"."PE_FK"[RAW,100], "S"."CT_FK"[RAW,100], "S"."RT_CD"[VARCHAR2,100],
>> "S"."C2L_CD"[VARCHAR2,2], "S"."DAY_PK"[DATE,7],
>> "S"."TRANS_AMT"[NUMBER,22], "S"."CP_RANK"[NUMBER,22]
>> 24 - (#keys=3) "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."PANL_CNT"[NUMBER,22],
>> LENGTH("T"."C_NBR")[22],
>> "C"."BI_NBR"[VARCHAR2,4], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "T"."CT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."DC_CNT"[NUMBER,22],
>> "T"."RT_CD"[VARCHAR2,100], "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100],
>> "T"."PE_FK"[RAW,100], "T"."TRANS_AMT"[NUMBER,22],
>> "T"."DAY_PK"[DATE,7], ROW_NUMBER() OVER ( PARTITION BY
>> "T"."DS_NO","T"."C_NBR" ORDER BY
>> INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )[22]
>> 25 - "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."PANL_CNT"[NUMBER,22],
>> LENGTH("T"."C_NBR")[22],
>> "C"."BI_NBR"[VARCHAR2,4], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "T"."CT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."DC_CNT"[NUMBER,22],
>> "T"."RT_CD"[VARCHAR2,100], "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100],
>> "T"."PE_FK"[RAW,100], "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7]
>> 26 - (#keys=2) "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."PANL_CNT"[NUMBER,22],
>> LENGTH("T"."C_NBR")[22],
>> "C"."BI_NBR"[VARCHAR2,4], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "T"."CT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."DC_CNT"[NUMBER,22],
>> "T"."RT_CD"[VARCHAR2,100], "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100],
>> "T"."PE_FK"[RAW,100], "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7]
>> 27 - (#keys=3) "T"."DS_NO"[NUMBER,22], "T"."C_NBR"[VARCHAR2,100],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."PANL_CNT"[NUMBER,22],
>> LENGTH("T"."C_NBR")[22],
>> "C"."BI_NBR"[VARCHAR2,4], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "T"."CT_CD"[VARCHAR2,100],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."DC_CNT"[NUMBER,22],
>> "T"."RT_CD"[VARCHAR2,100], "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100],
>> "T"."PE_FK"[RAW,100], "T"."TRANS_AMT"[NUMBER,22],
>> "T"."DAY_PK"[DATE,7], ROW_NUMBER() OVER ( PARTITION BY
>> "T"."DS_NO","T"."C_NBR" ORDER BY
>> INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )[22]
>> 28 - (#keys=2) "C"."PANL_CNT"[NUMBER,22], LENGTH("T"."C_NBR")[22],
>> "C"."BI_NBR"[VARCHAR2,4], SUBSTR("T"."C_NBR",1,4)[4],
>> "C"."LOW_VALUE"[VARCHAR2,4000],
>> "C"."IC2L_CD"[VARCHAR2,2], "C"."CPCL_NBR"[NUMBER,22],
>> "C"."HIGH_VALUE"[VARCHAR2,4000], "C"."DC_CNT"[NUMBER,22],
>> "T"."DS_NO"[NUMBER,22],
>> "T"."PM_FK"[RAW,100], "T"."CT_FK"[RAW,100], "T"."PE_FK"[RAW,100],
>> "T"."TRANS_AMT"[NUMBER,22], "T"."DAY_PK"[DATE,7],
>> "T"."CT_CD"[VARCHAR2,100],
>> "T"."RT_CD"[VARCHAR2,100], "T"."C_NBR"[VARCHAR2,100]
>> 29 - (#keys=0) "C"."PANL_CNT"[NUMBER,22], "C"."BI_NBR"[VARCHAR2,4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."HIGH_VALUE"[VARCHAR2,4000],
>> "C"."DC_CNT"[NUMBER,22]
>> 30 - "C"."PANL_CNT"[NUMBER,22], "C"."BI_NBR"[VARCHAR2,4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."HIGH_VALUE"[VARCHAR2,4000],
>> "C"."DC_CNT"[NUMBER,22]
>> 31 - (#keys=2) "C"."PANL_CNT"[NUMBER,22], "C"."BI_NBR"[VARCHAR2,4],
>> "C"."LOW_VALUE"[VARCHAR2,4000], "C"."IC2L_CD"[VARCHAR2,2],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."HIGH_VALUE"[VARCHAR2,4000],
>> "C"."DC_CNT"[NUMBER,22]
>> 32 - "C"."BI_NBR"[VARCHAR2,4], "C"."IC2L_CD"[VARCHAR2,2],
>> "C"."CPCL_NBR"[NUMBER,22], "C"."PANL_CNT"[NUMBER,22],
>> "C"."DC_CNT"[NUMBER,22], "C"."LOW_VALUE"[VARCHAR2,4000],
>> "C"."HIGH_VALUE"[VARCHAR2,4000]
>> 33 - "C0"[VARCHAR2,6], "C1"[VARCHAR2,4], "C2"[VARCHAR2,19],
>> "C3"[VARCHAR2,2], "C4"[NUMBER,22], "C5"[VARCHAR2,2], "C6"[NUMBER,22],
>> "C7"[CHARACTER,1], "C8"[NUMBER,22],
>> "C9"[VARCHAR2,4000], "C10"[VARCHAR2,4000]
>>
>>
>>
>> On Fri, Nov 11, 2022 at 3:37 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>> Like Sayan, I'd want to look at a report of the actual execution plan
>>> that included the projection information (in case more columns had been
>>> projected in the second case) and in the predicate information (in case
>>> some filtering had been applied later in the slow plan that the fast one).
>>>
>>> If the problem is easily reproducible I'd also repeat the slow query but
>>> capture the session events summary (v$session_event) and the non-zero
>>> session activity stats (v$sesstat for the session - with stat names) to see
>>> more of where the time went and what type of activity was going on.
>>>
>>> The second run shows more data coming through various rowsources, so
>>> it's not surprising that various steps of the plan take more time; and if
>>> there's any competition for resources (or resource limits being reached)
>>> then even a slightly larger volume of data could cause a significant change
>>> in performance (e.g. a hash join or sort spilling to disc). Having said
>>> that, I think you need to look at operation 79 and ask:
>>>
>>> "Why does a "smart table scan" record 2,156 seconds for I/O and 17
>>> seconds of CPU in the fast case, but only 138 seconds of I/O with 9,743
>>> seconds of CPU in the other?"
>>>
>>> I think the answer MIGHT be that the cell servers were overloaded and
>>> Oracle decided to pass all the data to the database servers rather than
>>> applying predicates at storage. (Is there any compression going on, by
>>> the way?) This is where the event and activity stats (the latter more
>>> likely than the former) could give us the answers.
>>>
>>> If it's overload, why would that happen ? Maybe because the fast query
>>> was run at 6:00 am and the slow one around midday when lots of other
>>> activity was going on.
>>>
>>> Looking around operation 30 where the query crashed - the hash join at
>>> operation 31 seems to have completed in memory and the row (actual) and max
>>> mem are not unreasonable when you compare the two inputs. The oddity is
>>> that the hash join reports 4,041 CPU seconds for the FAST query and only 9
>>> CPU seconds for the SLOW query! I think a key question is why the
>>> difference in CPU. It may be an oddity of the join predicate, it may be a
>>> difference in placement of a function call, it may be an accounting error.
>>> That's why the projection and predicate information could be most
>>> informative.
>>>
>>> I can't see any obvious reason why operation 30 should then fail, since
>>> the available memory was large and the sort runs (blocks per write) seem
>>> (on average to be larger for the slow run than the fast one). Did you get
>>> any error messages from the query, what about trace files?
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>> On Thu, 10 Nov 2022 at 20:06, 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;
>>>>
>>>

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