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

From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Fri, 11 Nov 2022 22:36:50 +0530
Message-ID: <CAEzWdqfoAHiRmbpifHQpuq=hX8VZyedENGzqQzbd7OvzF2+AqQ_at_mail.gmail.com>

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

Received on Fri Nov 11 2022 - 18:06:50 CET