Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

  • From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 10 Oct 2017 08:56:25 +0100

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.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2017