PGA tour: Maximize workareas for ETL in Oracle 11.2.0.3

When massaging large volumes of data into your datawarehouse, it is necessary to give the database the maximum resources available to get the job done in time. Our setup consists of a 2s12c24t blade with 48G of memory running Oracle linux 5.7 with a 11.2.0.3 database. The partitioning option and Parallel eXecution (PX) are being used to maximize processing.

This case specifically revolved around PGA memory usage. It was necessary to be able to use the maximum amount of memory for our join and sort operations, so it wouldn’t spill to disk (TEMP). Sounds pretty easy to do, but it actually proved to be a bit more complex.

Some things to consider when optimizing PGA.

• The maximum size of a workarea (which is used for sort,hash and join operations) has a hard limit of 2GB.
• work_area_policy is default set to AUTO and let’s leave it just like that.
• pga_agregate_target determines the target amount of memory available to all server processes of a database instance. It is a target, not a limit and its value is used to determine al kinds of internal settings. This should be the parameter to optimize.
• Degree of Parallelism (DOP) determines how many slaves will work on an object and also indirectly determines how (much) PGA memory is allocated to processes.

Searching and experimenting led us to the following rules to work with:

The maximum amount of memory for a workarea is reflected in the hidden parameter _smm_max_size.
The value is derived as 50% of hidden parameter _pga_max_size.
_pga_max_size is then derived as 5% of the pga_aggregate_target.

So if we want to have the  maximum of 2GB per workarea we should apply the following formula:
_smm_max_size=2GB, is 50% of _pga_max_size=4GB, is 10% of pga_aggregate_target=40G

But when using Parallel eXecution things work slightly different. When using a DOP of more than 5 another hidden parameter controls the amount of available PGA memory: _smm_px_max_size

The formula to maximize our workareas with PX:
50% of pga_aggregate_target. This would give us 50% of 20GB=10GB of memory for our PX slaves to work with.

As the DOP is maximized at 8, we need to be able to max out memory at 8*2GB=16GB of memory.
So we actually need to set pga_aggregate_target to at least 16GB*2=32 GB to make sure the workareas are maxed out.

This should give us all the correct derived values, but surely we hit another limit somewhere in Oracle. The _pga_max_size value will never be larger than 2GB, which give us a maximum of 1GB per workarea (_smm_max_size). This is a bit strange as the hard limit per workarea is 2GB, not for the total amount of workareas per process. Fortunately we can force _pga_max_size to 4G.

So to actually have processes use the absolute maximum of 2GB per workarea, set two parameters:

1. pga_aggregate_target=40G (or higher depending on your DOP)
2. _pga_max_size=4G

One more thing!

During testing I ran into an ora-0060 [kcblin_3], as I initially misinterpreted some information as how to set some parameters. It was caused by explicitely setting _smm_max_size to 4G and forcing a session into using more than 2GB. I was hitting the hard limit of 2GB imposed by Oracle.

Some usefull links

MOS Note 453540.1: How To Super-Size Work Area Memory Size Used By Sessions?
Oracle Database Reference 11.2: pga_aggregate_target
Cristian Bilien: two useful hidden parameters _smm_max_size and _pga_max_size
MOS Note 1372904.1: ORA-600 [kcblin_3] when _smm_max_size is greater than 2GB
Joze Senegacnik: Advanced Management of work areas in Oracle