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

This one went through fine :) and didn't bounced back from list. Perhaps because I replied to Ghassans email which was not having the excel sheet extract in it.

On Sat, 12 Nov, 2022, 8:36 am yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> 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