Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
To: oracle-l@freelists.org
References: <CANRhLpCH1SNxY9AjmCXuLtKKC3oSN+TChDq0gocNXN9CskodJA@mail.gmail.com>
 <765f8523-7766-c607-331d-b3f1381bc1c8@gmail.com>
 <CAHSa0M0N2ZmNyfSapnGqH+j9Y-+gB7Ff3vizRtB1jxGR+Mc1sg@mail.gmail.com>
 <94dea893-f120-c96e-c8c3-1f7ed048b94c@gmail.com>
 <c599599e-5d6d-900d-c1f1-2a6d0d80660b@gmail.com>
 <1f527cb8-028e-64c8-3fc6-fedb8f78217a@dunbar-it.co.uk>
 <20f4f28e-c742-e234-d9fd-fa835c2c4661@gmail.com>
From: Norman Dunbar <oracle@dunbar-it.co.uk>
Message-ID: <da20acac-7cf5-3bab-e446-a68c9235ab06@dunbar-it.co.uk>
Date: Tue, 10 Oct 2017 08:56:25 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: <20f4f28e-c742-e234-d9fd-fa835c2c4661@gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-GB
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle@dunbar-it.co.uk

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
--
http://www.freelists.org/webpage/oracle-l