Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

/>> This implies ... that the algorithm used by Oracle that selects the sessions to be killed is somewhat flawed perhaps? At least, on Windows./

In the case of idle connected sessions, almost certainly the "tunable" structures such as sort area and hash area are at absolute minimums due to lack of use, if not completely deallocated, so any PGA detected within those idle sessions most likely was due to "untunable" memory structures built in by application code, and even that was likely to be minimal due to lack of recent use.  So the active sessions ranked higher on that scale, and got whacked for it.

Session leaks like this are eventually fatal, and while PGA_AGGREGATE_LIMIT was fatal for the application, consider how much longer it might have taken to recover if the server crashed too due to virtual memory exhaustion?

On 10/9/17 13:59, Norman Dunbar wrote:
> On 09/10/17 20:41, Tim Gorman wrote:
> > ....
>> What the documentation is saying is that, when PGA_AGGREGATE_LIMIT is
>> exceeded, the RDBMS will first "attack" sessions using lots of
>> "untunable" PGA, because there is nothing else that the Oracle kernel
>> can do to control that. Tunable memory can be adjusted, but untunable
>> memory can only be killed, and heap memory is always released with
>> the calling operation or process is killed.
>
> What I found recently, with 12c on Windows (in the cloud) was that
> when the PGA_AGGREGATE_LIMIT was exceeded only runnable/running
> sessions wre getting killed by Oracle.
>
> There were a number of sessions, submitted by an application server,
> to run reports/calculations etc. When the reports were done, the
> sessions should have logged out, but didn't for some as yet, unknown
> reason - the vendor is unable to explain why. So there were lots of
> sessions sitting on event "SQL*NET: message from client" for hours and
> hours and days and weeks (occasionally) gradually building up the
> amount of PGA in use.
>
> I saw over 650 of these idle sessions on occasions and when the PGA
> ran out, there were "screams of horror" from the users as their
> running sessions were killed leaving these damned idle sessions
> untouched, and consuming PGA. The users were not happy! (And neither
> was the DBA!)
>
> In the end, and in the inability of the vendor to come up with (a) a
> reason and (b) a solution, I created a scheduled job to run every half
> hour, until the universe ends, checking for these sessions very
> carefully, and killing any that were previously running reports, that
> were now idle, that were idle on the above event, had been idle for
> over 30 minutes and were executing as a particular OS User while
> logged in as a specific database user. That "solved" our particular
> problem.
>
> I finished that contract before the vendor had worked out what the
> problem was, and as far as I know from recent conversations with my
> now ex colleagues, they have simply said that my solution is the fix!
> Go figure.
>
> This implies, to my ageing mind, that the algorithm used by Oracle
> that selects the sessions to be killed is somewhat flawed perhaps? At
> least, on Windows.
>
> Just my £0.02.
>
>
> Cheers,
> Norm.
>

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