How To Super-Size Work Area Memory Size Used By Sessions

Metalink note : 453540.1

How to super-size the work area memory size that can be used by any session in the database?
See also : http://dba-blog.blogspot.com/2005/08/pgamaxsize-hidden-parameter.html and  http://www.jlcomp.demon.co.uk/snark.html

Solution

With the automatic PGA management mode, you can control the size for the work area sizes (e.g. for a big sort).

You have to set the following parameters to proper values:

1. PGA_AGGREGATE TARGET
-> should be set to five times the desired work area size

2. _PGA_MAX_SIZE
-> should be set in minimum of twice the desired work area size

3. _SMM_MAX_SIZE
-> normally this parameter is not needed but maybe under certain circumstances
-> if set it should be equal to the desired work area size (in kb !)

Example:
If you like to use a sort area size of 2GB for a special operation (e.g for the
creating of an index on a large table) you could set the values as follows:

PGA_AGGREGATE TARGET = 10G
"_PGA_MAX_SIZE" = 2G
"_SMM_MAX_SIZE" = 2097152

Please note that there is an internal limitation that a single workarea size is limited to 2GB.
_PGA_MAX_SIZE is set up to 2GB (1GB limit for 32-bit) per bug 3946308

Also please be careful that the above changes are very dangerous because the values affect all sessions in the database. If you need to have granular control of the work area for a single session, then it is better to set at the session level WORKAREA_SIZE_POLICY=MANUAL and *_AREA_SIZE as high as needed.