Contents of the Oracle PGA

When you execute an SQL statement, the source and the execution plan are stored in shared memory because all processes can use that information. Information that is process specific, such as bind variable information and PL/SQL variable values, is contained in the PGA. Under the standard configuration of Oracle, the Program Global Area contains the following two types of information:

1. Stack Space: The stack space is used to hold process variables, arrays, and other similar information.
2. Session information: Session information includes PL/SQL variables and the private SQL areas. Under the multi-threaded server configuration, the session information is contained in the SGA.

The Program Global Area is an area of memory allocated and private for one process. The configuration of the PGA depends on the connection configuration of the Oracle database: either shared server or dedicated. In a shared server configuration, multiple users share a connection to the database, minimizing memory usage on the server, but potentially affecting response time for user requests. In a shared server environment, the SGA holds the session information for a user instead of the PGA. Shared server environments are ideal for a large number of simultaneous connections to the database with infrequent or short-lived requests. In a dedicated server environment, each user process gets its own connection to the database; the PGA contains the session memory for this configuration. The PGA also includes a sort area. The sort area is used whenever a user request requires a sort, bitmap merge, or hash join operation. As of Oracle9i, the PGA_AGGREGATE_TARGET parameter, in conjunction with the WORKAREA_SIZE_POLICY initialization parameter, can ease system administration by allowing the DBA to choose a total size for all work areas and let Oracle manage and allocate the memory between all user processes. As I mentioned earlier in this chapter, the parameter MEMORY_TARGET manages the PGA and SGA memory as a whole to optimize performance.

• Server Process: When you run Oracle using the default, dedicated server configuration, a server process is created for each user connection, each server process has a PGA and Sort Areas are allocated from those PGAs. The retained size portion of the Sort Area is allocated from each user's private SQL area^[1]. With the dedicated server option, this exists in the PGA, but using the multi-threaded server option it exists in the SGA. The other portion of the Sort Area, the part above the retained size, is always stored in the PGA of the server process that is performing the sort. The reason that the retained size portion of the Sort Area must be in the SGA when the multi-threaded server option is used, is that once allocated on behalf of a user connection, it must remain available for further use by that same user connection. Keeping it in the PGA would tie it to the server process. Since a different server process may eventually process the next SQL statement for that user, the retained portion must be kept in shared memory. As of 2010, I see very few Oracle shops (well under 6%) who continue to use the MTS [unless they have Oracle Java connections, which require MTS Shared Servers], and it is obsolete for today's 64-bit servers with lots of inexpensive RAM resources.

MTS becomes downright dangerous when Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) is in place.
When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request. First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"
Oracle's Tom Kyte notes that the MTS should not be used without a "real reason" and he notes that shared server connections are slower than with dedicated database connections (Oracle's default behavior):

1. "Unless you have a real reason to use MTS, don't."
2. "a shared server connection is by design "slower" than a dedicated server (more stuff goes on, more complex) it is most likely only getting in the way."

Historical Note: (MTS) Multi-Threaded Server has been deprecated starting with Oracle 9i. The Multi-Threaded Server (MTS) is a legacy server that existed for Oracle 8i and has been replaced by the Oracle Shared Server beginning with Oracle 9i.

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA. The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA. An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done. Figure 5-10 shows an instance PGA (collection of all PGAs) for an instance that is not configured for shared servers. You can use an initialization parameter to set a target maximum size of the instance PGA. Individual PGAs can grow as needed up to this target size.

Instance PGA

In older Oracle versions, the Program Global Area (PGA) was indeed tied to individual server processes, limiting access. However, Oracle 19c (and newer versions) introduce a more flexible and efficient approach.
In Oracle 19c, there's no longer a strict limit on the number of processes that can access the PGA. Here's why:

• PGA Management: Oracle 19c utilizes a component called the "PGA Aggregator" to manage PGA memory allocation dynamically. This allows the database to adjust PGA size based on workload demands, optimizing memory usage across all processes.
• Shared Memory: While each process still has its own PGA, some components within the PGA can be shared among multiple processes, improving efficiency and reducing overall memory consumption.

Key takeaway: The concept of a fixed number of processes accessing the PGA is no longer relevant in Oracle 19c. The database intelligently manages PGA memory allocation, allowing for greater flexibility and scalability without imposing specific process limitations. To further clarify, here's how PGA access works in 19c:

1. Process Request: When a process (like a user session) requires PGA memory, it requests it from the PGA Aggregator.
2. Dynamic Allocation: The PGA Aggregator allocates the necessary memory from a shared pool, adjusting the size based on the process's needs and overall system resources.
3. Shared Components: Some parts of the PGA, like session memory for variables and control structures, remain private to the process. However, other components, such as SQL work areas, can be shared among multiple processes if they're executing the same SQL statements.

This dynamic and shared approach to PGA management in Oracle 19c ensures efficient memory utilization and eliminates the rigid process limitations of the past.

Click the Quiz link below to test your knowledge of the PGA.
Oracle PGA - Quiz

[1] private SQL area: An area in memory that holds a parsed statement and other information for processing. The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas.