Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

 "alter system set pga_aggregate_limit=8000G SCOPE=BOTH;"

I wouldnt do that even if you have TBs of memory in your server!

On Tue, Oct 10, 2017 at 12:26 PM, Juan Carlos Reyes Pacheco < jcdrpllist_at_gmail.com> wrote:

> 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_freeli
>>> sts.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