Program Global Areas (PGA) Overview

The Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle whenever a server process is started and access to it is exclusive to that server process. Oracle code acting on behalf of the process is the only thing that has read or write access to the PGA.

The PGA is allocated when the server process is started, and the total PGA memory allocated by each server process attached to an Oracle instance is referred to as the aggregated PGA memory allocated by the instance.

Although the content of the PGA memory varies, depending on whether the instance is running the shared server option, the PGA memory can be classified as containing the private SQL area and work areas.

Private SQL Area

The private SQL area contains bind data information and runtime memory structures. Each connected user session that issues a SQL statement has its own private SQL area in the PGA regardless of its ability to use a single shared SQL area.

The location of a private SQL area depends on the type of connection established for a session. A session connecting through a dedicated server has its private SQL areas located in the server process's PGA. A user session that connects through a shared server architecture is given a part of the private SQL area that is kept in the SGA.

Dedicated Server

In a dedicated server architecture, every user who connects to Oracle has a personal dedicated connection handling the data retrieval from disk into the buffer cache for that user's queries. If 10 users are connected, there will be 10 dedicated server processes in the instance; if 500 users are connected, there will be 500 dedicated server processes, each one dedicated to getting the data into the SGA for its user. This means that every request is acted upon immediately, but it also means that additional memory and CPU overhead are associated with each server process. Each dedicated server process will likely spend most of its time sitting idle. This is the architecture decision made by most DBAs in shops where performance is pressed as critical and sufficient hardware resources are available to support it.

Shared Server

In a shared server architecture, there exists a pool of server processes running to support all the data retrieval requests for many users. Several connected users are directed to each shared server process. Oracle manages the user-to-server process utilization through the mechanism called Dispatcher. User processes are each assigned to a dispatcher in a one Dispatcher to many user processes configuration. There can be many Dispatchers in an instance, each servicing many user processes. The Dispatcher puts all the user requests for data into one queue (first in, first out). The shared server processes process the requests for data one at a time.

The shared server configuration can help you conserve memory and CPU usage on the machine that you have hosting the Oracle Server. It also helps to limit the amount of time that each server process spends idle.

Shared server architecture is often preferred when many users connect and disconnect from the database as is often the case in Internet databases supporting online stores.

SQL Work Areas

For large, long-running, complex queries (data warehouses or decision-support queries, for example), a big portion of the runtime area that they use is dedicated to work areas allocated by memory-intensive operators such as sort-based operators, hash joins, and bitmap merges or bitmap creation.

A sort operator uses a work area (more commonly called the sort area) to perform the in-memory sort of a set of rows.

Next, we look at processes. An Oracle instance contains several different categories and several different types of processes, and it is important that you understand them.