Re: PGA limits and PL/SQL Arrays

I haven't kicked the tyres on this one, but the paragraph preceding the bit you've highlighted says that:

*Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.*

Your PL/SQL sinners are not sys or background sessions, and PL/SQL arrays are "untunable memory" - so your sessions will have calls aborted and get killed if there are too many of them and they are using too much memory. If the backgrounds manage to run away with too much untunable memory it ought to be a bug.

Regards
Jonathan Lewis

On Tue, 21 Feb 2023 at 08:45, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> a very basic question this time:
> Suppose I have a large number of sessions and each of those are running a
> PL/SQL code that fills a PL/SQL array,
> such that PGA_LIMIT is jeopardized.
> Can the database under such circumstance prevent PGA from overshooting the
> LIMIT?
> I suppose no, when I read: *SYS processes and background processes other
> than job queue processes will not be subjected to any of the actions
> described in this section. Instead, if they are using the most untunable
> memory, they will periodically write a brief summary of their PGA usage to
> a trace file.* (
> https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3
> )
> I happy to learn more on this topic though.
>
> Thanks
>
> Lothar
>

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