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

The below section you posted shows the input to 'Hash join outer' were ~1Million and 37milion respectively, but the result came from the outer join is ~7billion. So is there any oddity in the data in table 'T' and 'C' which is playing a role which is increasing the rows input to your analytical function recently?

SQL Plan Monitoring Details (Plan Hash Value=2056239125)

    |                                                                     |
                            |         |       |           |        |


|          |       |       |       |       |         |       |       |


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


| 33 |                     PX RECEIVE


 |                             |   10647 |    24 |         4 |   +216 |
 16 |       1M |       |       |       |       |         |       |       |
         |                                       |


| 34 |                      PX SEND HASH


| :TQ10005                    |   10647 |    24 |      1633 |   +216 |


1 |       1M |       |       |       |       |         |       |       |
  0.01 | Cpu (2)                               |


| 35 |                       VIEW


 |                             |   10647 |    24 |      1632 |   +217 |
1 |       1M |       |       |       |       |         |       |       |
       |                                       |


| 36 |                        TABLE ACCESS STORAGE FULL


 | SYS_TEMP_0FDA1E71E_D71F1BDE |   10647 |    24 |      1632 |   +217 |
1 |       1M |   106 | 104MB |       |       |         |       |       |
  0.01 | cell multiblock physical read (1)     |


| 37 |                    PX RECEIVE


|                             |      9M |    2M |      1676 |   +218 |


 16 |      37M |       |       |       |       |         |       |       |
    0.05 | Cpu (9)                               |


| 38 |                     PX SEND HASH


 | :TQ10009                    |      9M |    2M |      1677 |   +217 |
 16 |      37M |       |       |       |       |         |       |       |
    0.13 | Cpu (23)                              |


| 39 |                      VIEW


|                             |      9M |    2M |      1677 |   +217 |


 16 |      37M |       |       |       |       |         |       |       |
         |                                       |

On Sun, Nov 13, 2022 at 2:34 AM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Thank you Jonathan.
> Actually the original query was a long one so i was trying to reduce the
> complexity by just posting the particular section of the query which was
> causing the temp space spike. But I agree that just a partial query doesn't
> make much sense. Also my apology as the plan format was distorted.
>
> I have again posted the exact query below with the actual object names
> being replaced with dummy names. I have posted the plan in the github in
> the link below, so the sql execution plan format will be intact. And I am
> not having the "display cursor" plan for the current runs/failures for this
> INSERT query, so i have posted the sql monitors for them and i had the
> display cursor plan when it was running fine in 11.2.0.4 so i posted that
> just for reference. Also I have copied and pasted the outline section from
> the display_awr below each of the sqlmonitor plan.
>
> https://gist.github.com/databasetech0073/714263bce477f624763f757e457cb861
>
> As mentioned earlier, the section of the plan which is doing the outer
> join is below and it results in ~10billion rows. Which then is passed to
> the analytical function. Was trying to understand, Is there any possible
> way to minimize the temp space consumption in this scenario? because we
> kept on increasing the temp space assuming it would succeed somehow, but
> it just kept consuming 2.5TB+ space and then also it failed with "Ora-01652
> unable to extend temp segment .." as it saturates all allocated temp
> space?
> As far as I know the encryption/TDE was already there in this database
> before we moved from 19.11 to 19.15. I will double check with the infra
> team on this.
>
> "LEFT OUTER JOIN C
>             ON   SUBSTR (T.C_NBR, 1, 4) = C.BNK_ID_NB_4
>              AND LENGTH (T.C_NBR) = C.PANL_CNT
>              AND T.C_NBR BETWEEN C.LOW_VALUE AND C.HIGH_VALUE) S"
>
>
>
>
>
>
> On Sat, Nov 12, 2022 at 12:50 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> >>  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.
>>
>> The supplied outputs are virtually unreadable, for different execution
>> plans, for different versions (and youve changed from 19.11 for the "slow"
>> query to 11.2.0.4!) , and partial. I'm not going to look at them.
>>
>> >> However, the sql monitor shows status as 'error'  out after
>> sometime(~30minutes)) but the underlying sessions(~33 parallel sessions)
>> kept on running for 2hrs+
>>
>> That looks like the behaviour that Mohamed Houri has written about in the
>> past: https://hourim.wordpress.com/2020/03/07/sql-monitor-flaw/
>>
>> I'd forgotten it would be harder to collect the correct statistics from
>> all the processes when running a parallel query - finding sessions by
>> SQL_ID could work, but using views like v$px_sesstat etc. is safer.
>> Nothing stands out from the information you supplied about wait events, and
>> the only "unusual" thing in the session activity is that you are writing
>> encrypted blocks to the temporary tablespace - were you doing that in the
>> earlier version? It shouldn't explain a massive increase in space
>> requirements, but it might be relevant - it would help to explain an
>> increase in CPU.
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Fri, 11 Nov 2022 at 17: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.
>>>
>>>
>>
>

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