MIME-Version: 1.0
In-Reply-To: <738cb4dc-c5e7-183c-ef2e-ac92f385724a@gmail.com>
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> <01d001d341e1$d524eb90$7f6ec2b0$@rsiz.com>
 <738cb4dc-c5e7-183c-ef2e-ac92f385724a@gmail.com>
From: Juan Carlos Reyes Pacheco <jcdrpllist@gmail.com>
Date: Tue, 10 Oct 2017 13:26:17 -0400
Message-ID: <CAGYrQyu9Jqw3-zy2hxzoti+BUSHx0itG83xVePWP7YLuXuBFJQ@mail.gmail.com>
Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY
To: tim.evdbt@gmail.com
Cc: "Mark W. Farnham" <mwf@rsiz.com>, Norman Dunbar <oracle@dunbar-it.co.uk>,
 ORACLE-L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="94eb2c065086e3a475055b349a82"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jcdrpllist@gmail.com
--94eb2c065086e3a475055b349a82
Content-Type: text/plain; charset="UTF-8"

Hello, simple set to the limity something almost infinite
alter system set pga_aggregate_limit=8000G SCOPE=BOTH;

then you set  the value pga_aggregate_target to set the minimum.
alter system set pga_aggregate_target=800m scope=spfile;

I set a minimum, because in some situations when it goes down some values,
the optimizer chooses unoptimal joins in some queries.

Greetings :)


2017-10-10 12:40 GMT-04:00 Tim Gorman <tim.evdbt@gmail.com>:

> 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@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:
>>
>>> 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
>
>
>

--94eb2c065086e3a475055b349a82
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello, simple set to the limity something almost infi=
nite</div><div>alter system set pga_aggregate_limit=3D8000G SCOPE=3DBOTH;</=
div><div><br></div><div>then you set=C2=A0 the value pga_aggregate_target t=
o set the minimum.</div><div>alter system set pga_aggregate_target=3D800m s=
cope=3Dspfile;</div><div><br></div><div>I set a minimum, because in some si=
tuations when it goes down some values, the optimizer chooses unoptimal joi=
ns in some queries.</div><div><br></div><div>Greetings :)</div><div><br></d=
iv></div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">2017-10-=
10 12:40 GMT-04:00 Tim Gorman <span dir=3D"ltr">&lt;<a href=3D"mailto:tim.e=
vdbt@gmail.com" target=3D"_blank">tim.evdbt@gmail.com</a>&gt;</span>:<br><b=
lockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px =
#ccc solid;padding-left:1ex">I see this more as confusion between PGA_AGGRE=
GATE_TARGET and PGA_AGGEGATE_LIMIT.=C2=A0 The former (older) parameter is a=
 suggestion, the latter (newer) is a hard limit.<br>
<br>
As Mr Dunbar&#39;s story proves, the limit is a sheriff not to be challenge=
d, because this sheriff shoots down bystanders as readily as perpetrators. =
Martial law is imposed, no warning tickets are issued.<div><div class=3D"h5=
"><br>
<br>
<br>
<br>
On 10/10/17 10:07, Mark W. Farnham wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
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 proce=
ss. What various op/sys&#39;s have called the various pieces have changed o=
ver 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 &quot;management threshold&quot; the overall f=
unction will be better understood.<br>
<br>
mwf<br>
<br>
-----Original Message-----<br>
From: <a href=3D"mailto:oracle-l-bounce@freelists.org" target=3D"_blank">or=
acle-l-bounce@freelists.org</a> [mailto:<a href=3D"mailto:oracle-l-bounce@f=
reelists.org" target=3D"_blank">oracle-l-bounce@freeli<wbr>sts.org</a>] On =
Behalf Of Norman Dunbar<br>
Sent: Tuesday, October 10, 2017 3:56 AM<br>
To: <a href=3D"mailto:oracle-l@freelists.org" target=3D"_blank">oracle-l@fr=
eelists.org</a><br>
Subject: Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY=
<br>
<br>
On 09/10/17 23:24, Tim Gorman wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
In the case of idle connected sessions, almost certainly the &quot;tunable&=
quot;<br>
structures such as sort area and hash area are at absolute minimums<br>
due to lack of use, if not completely deallocated, so any PGA detected<br>
within those idle sessions most likely was due to &quot;untunable&quot; mem=
ory<br>
structures built in by application code, and even that was likely to<br>
be minimal due to lack of recent use.=C2=A0 So the active sessions ranked<b=
r>
higher on that scale, and got whacked for it.<br>
<br>
Session leaks like this are eventually fatal, and while<br>
PGA_AGGREGATE_LIMIT was fatal for the application, consider how much<br>
longer it might have taken to recover if the server crashed too due to<br>
virtual memory exhaustion?<br>
</blockquote>
Morning Tim,<br>
<br>
while I agree which what you say above, and especially the bit about tunabl=
e PGA, my impression that the algorithm is perhaps faulty still stands. Her=
e&#39;s why.<br>
<br>
Originally, the database was having to be restarted every week, sometimes e=
ven 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 unders=
tand.<br>
<br>
Then we implemented my workaround for the problem, later accepted by the ve=
ndor as the fix, which looks for, and kills off only idle sessions.<br>
Suddenly, we no longer have a problem with used PGA getting close to the li=
mit and we don&#39;t restart the database every week any more - speaking wi=
th ex colleagues, we haven&#39;t restarted it for months now.<br>
<br>
So, given that killing idle sessions only actually prevents the problem (at=
 least for us), perhaps Oracle&#39;s algorithm should consider idle session=
s 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.<br>
<br>
I&#39;d be happy (ish) if Oracle did have to kill active sessions but only =
after killing the idle ones first.<br>
<br>
<br>
Cheers,<br>
Norm.<br>
<br>
</blockquote>
<br>
--<br>
</div></div><a href=3D"http://www.freelists.org/webpage/oracle-l" rel=3D"no=
referrer" target=3D"_blank">http://www.freelists.org/webpa<wbr>ge/oracle-l<=
/a><br>
<br>
<br>
</blockquote></div><br></div>

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