Automated SQL Execution Memory Management

Automatic PGA Memory Management

Besides SQL, various components in the database server make use of PGA memory. These other parts are known as the untunable parts of the PGA because they require an allocation of memory that cannot be adjusted. This is the case for:

• The context information of each session
• Each open cursor
• PL/SQL, OLAP, or Java memory

The tunable portion of the PGA represents the memory available to SQL work areas. This portion could represent 90% of the overall PGA memory for decision support systems, whereas it could be less than 10% in pure OLTP systems.

With automatic PGA memory management, the system attempts to keep the amount of private memory below the target specified by the PGA_AGGREGATE_TARGET initialization parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.



Note: Oracle Corporation does not recommend the use of static SQL memory management. For more information about SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, refer to the Oracle Database Reference guide.

SQL Memory Usage

Complex database queries require memory-intensive operators such as sort and hash-join. Those operators need what is called “work area memory” to process their input data. For example, a sort operator uses a work area to perform the in-memory sort of a set of rows. Similarly a hash-join operator uses a work area to build a hash table on one of the tables in the FROM clause.

The amount of memory allocated by these operators greatly affects their performance, and a larger work area can significantly improve the performance of a SQL operator. The optimal size of a work area is big enough to accommodate the input data and auxiliary memory structures.

Because there is only a finite amount of memory in the system shared by all concurrent operators, an operator cannot always allocate its optimal size. When the size of the work area is smaller than its ideal cache size, the response time increases because an extra pass is performed on all or some of the input data. This is referred to as the one-pass size of the work area.

When the work area size is less than the one-pass threshold, multiple passes over the input data are needed, causing dramatic increase in response time. This is referred to as the multi-pass size of the work area. For example, a sort operation that needs to sort 10 GB of data needs a little more than 10 GB of memory to run in cache, and 40 MB to run in one-pass mode. It will run in multiple passes with less than 40 MB. 

Setting PGA_AGGREGATE_TARGET Initially




Assume that an Oracle instance is configured to run on a system with 4 GB of physical memory. Part of that memory should be left for the operating system and other non-Oracle applications running on the same hardware system. You might decide to dedicate only 80% (3.2 GB) of the available memory to the Oracle instance.

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20% of the instance memory), leaving 80% for the SGA.

For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of the instance memory (up to 2.2 GB in this example).

Good initial values for the PGA_AGGREGATE_TARGET parameter might be:

• For OLTP: PGA_AGGREGATE_TARGET=(total_mem*80%)*20% 
• For DSS: PGA_AGGREGATE_TARGET=(total_mem*80%)*50% 

where total_mem is the total amount of physical memory available on the system.

In this example, with a value of total_mem equal to 4 GB, you can initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to 655 MB for an OLTP system.