From: "Mark W. Farnham" <mwf@rsiz.com>
To: <oracle@dunbar-it.co.uk>,
 <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> <da20acac-7cf5-3bab-e446-a68c9235ab06@dunbar-it.co.uk>
In-Reply-To: <da20acac-7cf5-3bab-e446-a68c9235ab06@dunbar-it.co.uk>
Subject: RE: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
Date: Tue, 10 Oct 2017 12:07:11 -0400
Message-ID: <01d001d341e1$d524eb90$7f6ec2b0$@rsiz.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Content-Language: en-us
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: mwf@rsiz.com

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@freelists.org =
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Norman Dunbar
Sent: Tuesday, October 10, 2017 3:56 AM
To: oracle-l@freelists.org
Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and =
WORKAREA_SIZE_POLICY

On 09/10/17 23:24, Tim Gorman wrote:
>=20
> In the case of idle connected sessions, almost certainly the "tunable" =

> structures such as sort area and hash area are at absolute minimums=20
> due to lack of use, if not completely deallocated, so any PGA detected =

> within those idle sessions most likely was due to "untunable" memory=20
> structures built in by application code, and even that was likely to=20
> be minimal due to lack of recent use.  So the active sessions ranked=20
> higher on that scale, and got whacked for it.
>=20
> Session leaks like this are eventually fatal, and while=20
> PGA_AGGREGATE_LIMIT was fatal for the application, consider how much=20
> 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.=20
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.

--=20
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


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