PGA size for Datawarehouse ETL loads

As we see in the figure above, having a large db_cache_size does not benefit parallel large-table-full-table scans, as this requires memory in the pga_aggregate_target region instead. With Oracle, you can use the multiple data buffer features to segregate and cache your dimension tables and indexes, all while providing sufficient RAM for the full scans. When your processing mode changes (evening ETL and rollups), Oracle AMM will automatically detect the change in data access and re-allocate the RAM regions to accommodate the current processing.

All 64-bit servers have a larger word size (two to the 64th power) that allows for up to 18 billion GB of addressable RAM. Hence, you may be tempted to create a super-large RAM data buffer. Data warehouse systems tend to bypass the data buffers because of parallel full-table scans, and maximizing disk I/O throughput is the single most critical bottleneck.

As we know, the choice between a hash join, star transformation join, and a nested loop join depends on several factors:

The relative number of rows in each table

The presence of indexes on the key values

The settings for static parameters such as index_caching and cpu_costing

The current setting and available memory in pga_aggregate_target

As we know, hash joins do not use indexes and perform full-table scans (often using parallel query). Hence, hash joins with parallel full-table scans tend to drive up CPU consumption.

Be aware that in Oracle9i, when you set pga_aggregate_target, no single hash join may consume more than five percent of the area. If you have specialized hash joins that require more hash area

The _PGA_MAX_SIZE does not controls the total used PGA memory for the whole system.

The _PGA_MAX_SIXE controls the max used PGA memory per process, which by default is 200 MB.

A work area is a large extent on the memory available in the Program Global Area (PGA).

Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.

One process can have many work areas.

The size that can be used from a single work area is limited to a max of 100 MB (_PGA_MAX_SIZE/2) by default for serial operations. This is in case that your PGA_AGGREGATE_TARGET > = 2 GB. Otherwise it will use no more than of (5% of PGA_AGGREGATE_TARGET), for values for PGA_AGGREGATE_TARGET <= 2GB. It is coming from that the min(5% of PGA_AGGREGATE_TARGET, 100MB) is taken.

The size for a single work area is limited to 30% of the PGA_AGGREGATE_TARGET/DOP for parallel operations, where DOP is Degree of Parallelism.

If you have set, for example the PGA_AGGREGATE_TARGET to 1 GB then Oracle cannot give more than 50 MB to a single work area (for serial operations).

If you want to use more than 100 MB per single work area then may be the _PGA_MAX_SIZE can help you to achieve better results for your sort tasks and to let you to perform the biggest ones only inside the memory.