Problem solve Get help with specific problems with your technologies, process and projects.

Sizing the PGA

Can you give specific sizes for the PGA in Oracle? Please explain.

Oracle uses many parameters to control the sizing of specific PGA structures. These parameters include SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE. Collectively, these are known as the *_AREA_SIZE parameters. Before Oracle 9i, the DBA would individually size these parameters.

Oracle 9i introduced a more automated way of dealing with sizing the PGA. The new PGA_AGGREGATE_TARGET parameter defines the total amount of PGA for all of your connected sessions. In addition, the WORKAREA_SIZE_POLICY parameter must be set to AUTO for this to work.

So the big question then is what value should PGA_AGGREGATE_TARGET be set at? Oracle does give a rule of thumb for this. For an OLTP system, Oracle recommends setting PGA_AGGREGATE_TARGET to 16% of your server's physical memory. For a DSS system, Oracle recommends 40% of your physical memory. This is just a starting place. You'll want to fine tune this parameter for you database's specific workload.

The first place to look is V$SYSTAT for the "work area" statistics. You'll want to size PGA_AGGREGATE_TARGET so that the 'workarea executions - multipass' is non-zero. Ideally, the 'workarea executions - onepass' should be non-zero as well. Also, you'll want to look at V$PGASTAT and pay close attention to the over allocation count. Increase PGA_AGGREGATE_TARGET if this value is non-zero. Finally, the V$PGA_TARGET_ADVICE view will give you advice on an optimal setting for this parameter.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.