Oracle PGA

Oracle instance consists of SGA and oracle processes. Oracle processes can be divided into server processes and background processes. Oracle database uses background processes to perform overall database operations, for example, database writer process writes dirty buffer from database buffer cache to datafiles. Oracle creates one or more Server Processes for users connected to database. Each oracle process is assigned a memory area to contain data and control information. This memory area is called Program Global Area (PGA).

Oracle database creates server processes to mange requests of user process. All user process issues SQL statements, it is the responsibility of Server process to execute these statements and return results to user process. Each server process has one PGA. PGA is memory where server process executes statements and stores information. PGA consists of Private SQL Area, session memory and SQL work Areas.

Each statement in oracle is associated with a Private SQL area. Private SQL area can be divided into persistent area and run-time area. Persistent area contains bind information, i.e. the values for bind variables. Runtime area is used to execute the statements. Private SQL area is created in PGA if the connection is established using dedicated server. In case of shard server connection, Private SQL area is created in SGA.

PGA also contains Session Memory which is used to store session information for example session variables and logon information.

Few queries require complex operation like Hash Join or sorting. These operations are performed in the memory. To perform these tasks, SQL work areas are created in runtime area. The size of SQL work areas should be large enough that it can contain data accessed by SQL statements. If size of work area is not large enough then response time may increase and hurt database performance. Oracle supports automatic memory management of work areas. Automatic management involve to steps, first you decide maximum memory available for PAG. Then use PGA_AGGREGATE_TARGET initialization parameter to set maximum memory available for PGA. This will only work for dedicated server mode. Because in shared servers, SQL work areas are created in SGA.

1 thought on “Oracle PGA”

Leave a Reply

Your email address will not be published. Required fields are marked *