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

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