Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 10 Oct 2017 14:50:59 -0400

Hi Tim,

It isn't a hard limit, it can be exceeded. The manual says the following:

|PGA_AGGREGATE_LIMIT|specifies a limit on the aggregate PGA memory consumed by the instance.

There is no difference in behavior between |PGA_AGGREGATE_LIMIT|being explicitly set or being set to the default.

Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.

SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

If PGA_AGGREGATE_LIMIT was a hard limit, it wouldn't be possible to exceed it. PGA_AGGREGATE_LIMIT is like the speed limit on a highway: it is possible to exceed it, but there may be consequences.
Regards

On 10/10/2017 12:40 PM, Tim Gorman wrote:

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.

--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

Other related posts: