Program Global Area: This is a private memory area for a process.
Server Process: Server processes are created when users connect to Oracle.
System Global Area: This shared memory area used by all processes.
DBWR, LGWR, and SMON: Even the background processes get private memory.
Database User: Users communicate with server processes over the LAN.
Contents of PGA
The PGA is subdivided into different areas, each with a different purpose.
Figure 5-9.1 shows the possible contents of the PGA for a dedicated server session. Not all of the PGA areas will exist in every case.
Figure 5–9.1 PGA Contents
Private SQL Area
A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.
Do not confuse a private SQL area, which is in the UGA, with the shared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of SELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.
A cursor is a name or handle to a specific private SQL area. As shown in Figure 5-9.2, you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
Figure 5-9.2: Cursor
A private SQL area is divided into the following areas:
run-time area: This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan. Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.
persistent area: This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.