Skip to content
Home » Oracle Database » Alter System Set PGA_AGGREGATE_LIMIT

Alter System Set PGA_AGGREGATE_LIMIT

Increase PGA_AGGREGATE_LIMIT

Whenever you need more PGA to service clients or saw error ORA-04030, you may consider to raise PGA size online.

Let's see current settings.

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 40G
pga_aggregate_target                 big integer 20G

I know you might consider to change PGA_AGGREGATE_TARGET, but the problem is that it's only an initial limit, it can be soon lifted whenever clients need more. That is, it's not the hard limit of PGA.

Set PGA_AGGREGATE_LIMIT

The real hard limit is PGA_AGGREGATE_LIMIT. Directly increasing PGA_AGGREGATE_LIMIT is a better way in such urgent situations. Even better, we can do it dynamically.

SQL> alter system set pga_aggregate_limit=50G scope=memory sid='*';

System altered.

In this case, we expanded it to 50GB online. For resource sufficient environments, you may set it to 0 which is unlimited. To persist the value, you may set SCOPE=BOTH.

Let's check the result.

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 50G
pga_aggregate_target                 big integer 20G

Strictly speaking, PGA_AGGREGATE_LIMIT is not the hard limit, either. The hard limit is the physical memory of database server. So it's meaningless to overcommit PGA_AGGREGATE_LIMIT to a value where it can't reach.

Leave a Reply

Your email address will not be published. Required fields are marked *