Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

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_at_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_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
>
>
>

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