Oracle's PGA Management overview

As a complex Oracle database system, the SQL statements submitted by different users are processed every moment, obtaining the data and returning the data to the user. As already mentioned, parsing an SQL statement is done in a shared pool in an Oracle instance. So, for each session, where are the binding variables passed in when the SQL statement is executed? And for those sessions that need to perform more complex SQL, such as ordering (sort) or hash joins (Hash-join), where does the memory space needed for these sessions come from? In addition, where are some of the management control information associated with each session? For some of the memory allocation issues associated with each session, Oracle solves it by introducing the PGA memory component.

1, the concept of PGA and its included memory structure

The PGA is interpreted as an Oracle official document, called the program global area, but there are also some sources that can be understood as the process global area. There is no essential difference between the two, it is first a memory area, and secondly, the zone contains data and control information related to a particular server process. Each process has its own private PGA area, which means that the area can only be accessed by the process it belongs to, not by other processes, so it is not necessary to latch such a memory structure in the PGA to protect the information.

Generally speaking, the PGA contains information about operating system resources used by the current process (such as open file handles, and so on) and some private state information related to the current process. Each PGA area contains the following two parts.

Fixed PGA: This section contains small fixed-size variables and pointers to the PGA portion of the change.

Changes in the PGA segment (Variable PGA): This part is organized according to the Heap (Heap), so this part is also called the PGA Heap. The memory structures included in the PGA heap include:

Permanent memory for some fixed tables.

If the session is using a private connection (dedicated server), it also contains the user global area (the username global Area,uga) child heap. If the session is using a shared connection method (share server), the UGA is located in the SGA. UGA is the most important part of the PGA.

Invokes the global zone (call Global AREA,CGA) child heap.

A UGA is an area of memory that contains information about a particular session, such as the logon information for the session and the session-private SQL area. Each UGA also contains the following two sections.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Fixed UGA: This section contains small fixed-size variables and pointers to the UGA part of the change.

Change UGA part (Variable UGA): This part is also organized according to the heap, and you can see the distribution of the UGA heap from the X$ksmup view. The distribution of UGA heap is related to the parameters of Open_cursors, open_links and so on. The so-called cursor (cursor) is placed here, and the cursor points to objects in the shared pool that contain SQL text and execution plans. The memory structure contained in the UGA heap is described below.

Private SQL Area: This section contains data such as binding variable information and the memory structure at run time. Each session that emits a SQL statement has its own private SQL region. This part of the region can be divided into the following two parts.

Permanent memory Area: Here are some cursor information that is required for the same SQL statement to execute multiple times, such as binding variable information, data type conversion information, and so on. This portion of memory is freed only when the cursor is closed.

Runtime Zone: The first step in working with SQL statements is to create a run-time zone that holds some of the information that is used when the SQL statement is run. For DML (INSERT, UPDATE, DELETE) statements, the region is freed when the SQL statement is executed, and for a query (SELECT), it is released after all rows of data are fetched and passed to the user, or after the query has been canceled.

Session-related information. This part of the information includes the following sections.

Status information for the package being used (package).

Use the trace information enabled by commands such as Alter session, or the modified session-level optimizer parameters (Optimizer_mode), sorting parameters (Sort_area_size, and so on), modified NLS parameters, and so on.

The DB links that are open.

The roles that can be used (roles), and so on.

Workspace (Work area): This area is primarily used to store intermediate data generated during the execution of SQL, such as sorting, where you need to store intermediate data in the sort process. This part occupies most of the space in the PGA. Its size depends on the complexity of the SQL statement to be processed. If an SQL statement contains operations such as group by, Hash-join, and so on, a large SQL work area is required. In fact, we adjust the PGA to adjust the area.

And the location of the UGA is determined entirely by the way the session is connected:

If the session is logged on to the database through a shared server, there is no doubt that UGA must be able to be accessed by all processes, so in this case the UGA is allocated from the SGA. Further, if the large pool is set in the SGA, the UGA is allocated from the large pool; otherwise, if large pool is not set, then UGA can only be allocated from the shared pool.

If the session is logged into the database through a dedicated server (dedicated server), the UGA is allocated from the PGA of the process.