SGA and PGA Sizes

The default si zes for the SGA and PGA are set upon installation, based on the total amount of physical memory in your system. Rather than changing the sizes of individual SGA components, you can change the overall size of the SGA by setting a parameter called SGA Target, and Oracle Database XE automatically adjusts the sizes of the individual SGA components, continuously tuning these sizes to optimize performance. Similarly, rather than changing the size of individual PGAs, you can change the total amount of memory allocated for the collection of PGAs, and Oracle Database XE adjusts individual PGA sizes as needed. The collection of PGAs is known as the PGA Aggregate. You change the PGA Aggregate maximum size by setting a parameter called PGA Aggregate Target.

Note:

Oracle Database XE always allocates the full amount of memory specified by the SGA Target parameter. That is, the current SGA size is always equal to SGA Target. In contrast, the current size of the PGA Aggregate may be less than the amount specified by the PGA Aggregate Target parameter. The database allocates more memory for the PGA Aggregate as needed, up to the maximum indicated by PGA Aggregate Target.

The maximum amount of memory that Oracle Database XE allows for the SGA and PGA Aggregate is 1 gigabyte (GB). If you attempt to change memory allocation so that the sum of the SGA size and PGA Aggregate size exceeds 1 GB, Oracle Database XE issues an error message. (For SGA changes, the error message does not appear until you restart the database.)

The only circumstanc es under which you should need to change SGA and PGA Aggregate sizes are the following:

You add physical memory to the computer running Oracle Database XE and want to allocate more to the database.

In this case, increase both the SGA and PGA Aggregate sizes, maintaining roughly the original ratio of SGA size to PGA Aggregate size.
You receive an error due to insufficient memory.

If the error message indicates insufficient memory for an SGA component, increase the SGA size. Examples of such errors include the following:
```
ORA-04031: unable to allocate n bytes of shared memory
ORA-00379: no free buffers available in buffer pool...
```
If the error message indicates insufficient memory for a process, increase the PGA Aggregate size. An example of such an error is the following:
```
ORA-04030: out of process memory when trying to allocate n bytes
```
If you are not sure whether the insufficient memory error involves the SGA or PGA, increase both SGA and PGA Aggregate sizes, maintaining roughly the original ratio of SGA size to PGA Aggregate size.

For SGA size changes, you must shut down and restart the database for the changes to take effect. For PGA Aggregate size changes, there is no need to restart the database.