Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

I see this more as confusion between PGA_AGGREGATE_TARGET and PGA_AGGEGATE_LIMIT.  The former (older) parameter is a suggestion, the latter (newer) is a hard limit.

As Mr Dunbar's story proves, the limit is a sheriff not to be challenged, because this sheriff shoots down bystanders as readily as perpetrators. Martial law is imposed, no warning tickets are issued.

On 10/10/17 10:07, Mark W. Farnham wrote:
> To me the worst part is that the parameter ...LIMIT is not a limit. It is a threshold at which Oracle begins a more aggressive shedding of PGA space. Simply having the language wrong sends folks off on the wrong thought process. What various op/sys's have called the various pieces have changed over the years. Apart from some mild sniping about exactly what to call the pieces, the thread has been a useful discussion. I believe if when you see LIMIT in this case you think "management threshold" the overall function will be better understood.
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar
> Sent: Tuesday, October 10, 2017 3:56 AM
> To: oracle-l_at_freelists.org
> Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
>
> On 09/10/17 23:24, Tim Gorman wrote:
>> 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?
> Morning Tim,
>
> while I agree which what you say above, and especially the bit about tunable PGA, my impression that the algorithm is perhaps faulty still stands. Here's why.
>
> Originally, the database was having to be restarted every week, sometimes even less, as our monitoring detected that the used PGA was getting close to the limit. That caused no end of problems for the user as you would understand.
>
> Then we implemented my workaround for the problem, later accepted by the vendor as the fix, which looks for, and kills off only idle sessions.
> Suddenly, we no longer have a problem with used PGA getting close to the limit and we don't restart the database every week any more - speaking with ex colleagues, we haven't restarted it for months now.
>
> So, given that killing idle sessions only actually prevents the problem (at least for us), perhaps Oracle's algorithm should consider idle sessions before killing active ones? The limit is PGA after all, and surely saving some GB of PGA from idle sessions is better that killing active sessions - at least, initially.
>
> I'd be happy (ish) if Oracle did have to kill active sessions but only after killing the idle ones first.
>
>
> Cheers,
> Norm.
>

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