Oracle Memory Architecture Detailed (v) Oracle PGA

The PGA (Program Global region area) is a memory area that contains data and control information for a service process. It is created by Oracle at the start of a service process and is unshared. An Oracle process has a PGA memory area. A PGA can only be accessed by the service process that owns it, and only the Oracle code in the process can read and write to it. Therefore, the structure of the PGA does not require latch protection.

We can set the total PGA memory for all service processes to be limited by the total PGA (aggregated PGA) allocated by the instance.

In proprietary server (dedicated server) mode, Oracle initiates an Oracle process for each session, while in multithreaded service (multi-thread server MTS) mode, multiple sessions share a single Oracle service process.

The PGA contains information about the operating system resources that the process uses, as well as some information about the state of the process. Information about the Oracle shared resources used by the process is in the SGA. This will enable the timely release and removal of these resources when they are discontinued outside the process.

Stack space is a storage area that is used to store user session variables and arrays;

User session data is an additional store that is used for user sessions.

|--session Information

|--sort Area

|--cursor Information

Note The session information (user sessions information) is different in the exclusive server from the area of memory that is in the shared server.

1, the composition of the PGA

The PGA consists of two sets of regions: fixed PGA and variable PGA (or PGA Heap, PGA Heap "Heap--heap is a managed memory area"). The fixed PGA is similar to the fixed SGA, which is fixed in size, contains a large number of atomic variables, a small data structure, and pointers to the variable PGA.

The variable PGA is a memory heap. Its memory segment can be found by View X$ksmpp (another view X$ksmsp the variable SGA memory segment information, their structure is the same). The PGA Heap contains memory (dependency and parameter settings, including Db_files, control_files) for storing x$ tables.

In general, the PGA variable area is mainly divided into the following three parts:

1) private SQL District;

2 Cursors and SQL area

3 Session Memory

(1) Private SQL area

The private SQL zone contains data such as binding variable values and RUN-TIME memory structure information. Each session that runs an SQL statement has a block private SQL area. All users who commit the same SQL statement have their own private SQL area, and they share a shared SQL area. Therefore, a shared SQL zone may be associated with more than one private shared area.

The private SQL area of a cursor is divided into two different areas of life cycle:

Persistent Zone: Contains the binding variable information. is disposed when the cursor is closed.

Run Area: Released when execution ends.

Creating a runtime is the first step in executing a request. For INSERT, UPDATE, and DELETE statements, Oracle releases the runtime at the end of the statement run. For query operations, Oracle releases the runtime only if all records are fetch or the query is canceled.

(2) Cursors and SQL areas (Cursors and SQL Areas)

An Oracle precompiled program or OCI application developer can explicitly open a cursor, or control a specific private SQL area, and use them as a named resource for the program to run. In addition, Oracle implicitly uses a shared SQL area for recursive calls that are generated by some SQL statements (previously described, read data dictionary information).

Private SQL zones are managed by the user process. How to allocate and release private SQL areas is greatly dependent on the utility you are using. The number of private SQL extents that a user process can allocate is controlled by the parameter open_cursors, and its default value is 50.

The private SQL area will always exist before the cursor is closed, or before the statement handle is released (but the runtime is released at the end of the statement execution and only the permanent area persists). Application developers can reduce the memory consumed by the user program by closing all open cursors that are no longer in use to release the permanent zone.

(3) Conversation memory (session Memory)

Session memory is a section of memory used to hold session variables, such as logon information, and other information related to the session. For shared server mode, session memory is shared, not private.

For complex queries, such as queries in decision support systems, a large portion of the runtime is assigned to the SQL workspace (SQL Work area) by operations with large memory requirements. These actions include:

Sort based operations (order by, GROUP by, ROLLUP, window functions);

Hash Join

Bitmap Merge

BITMAP Create

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

For example, a sort operation uses a workspace (which is also called the Sort area sort region) to sort some data rows in memory, while a hash join operation uses the workspace (which can also be called the hash area) to create a hash table. If the two operations handle a larger amount of data than the workspace, the input data is divided into smaller pieces so that some pieces of data can be processed in memory while others are processed later on the disk in the temporary tablespace. Although the workspace is too small, the bitmap operation does not put data on disk processing, but their complexity is inversely proportional to the size of the workspace. So, overall, the larger the workspace, the faster these operations run.

The size of the workspace can be adjusted. In general, a large workspace can make some specific operational performance better, but it also consumes more memory. The size of the workspace is adequate to accommodate the input data and the auxiliary memory required for the associated SQL operation. If not, the response time for the operation is increased because a portion of the data needs to be processed on a temporary table space disk.

2, PGA Memory automatic management

The SQL workspace can be automatic, global management. The DBA specifies the total size of the PGA memory for an instance, as long as the parameter is set Pga_aggregate_target. When this parameter is set, Oracle takes it as a total global limit, maximizing the total PGA memory of all Oracle service processes to this value.

Before this parameter occurs, the DBA adjusts the parameters sort_area_size, Hash_area_size, Bitmap_merge_area_size, and create_bitmap_area_size (for these parameters, We'll introduce them later) to maximize performance and PGA memory consumption. Adjustments to these parameters are cumbersome because all the relevant operations are considered so that the workspace is suitable for their input data size and the PGA memory is not consumed too much to degrade the overall performance of the system.

After 9i, the size of the workspace for all sessions is automatically allocated by setting the parameter pga_aggregate_target. At the same time, all *_area_size parameters will fail. At any time, the total number of PGA memory available in the instance for the workspace is based on parameter pga_aggregate_target. The total amount of workspace memory equals the value of the parameter Pga_aggregate_target minus the memory consumption of other components of the system, such as the PGA memory allocated to the session. The size of the PGA memory allocated to the Oracle process is based on their memory requirements.

Parameter Workarea_size_policy determines whether to use Pga_aggregate_target to manage PGA memory. It has two values: Auto and manual. The default is auto, which uses Pga_aggregate_target to manage PGA memory. In fact, it can be seen from the name of the parameter workarea_size_policy that Oracle's PGA Memory Auto Management will only adjust the workspace portion, not the workspace part (the fixed PGA area) will not be affected.

Also note: Before 10g, pga_aggregate_target only takes effect in dedicated service mode. After 10g, PGA Memory automatic Management is effective under the proprietary service mode (dedicated Server) and MTS. In addition, 9i does not support PGA memory automatic Management on the OpenVMS system, but 10g support.

After the Pga_aggregate_target is set, the size of the PGA memory for each process is also restricted:

In a serial operation, the PGA memory available for each process is min (Pga_aggregate_target * 5, _PGA_MAX_SIZE/2), where the default value of the implied parameter _pga_max_size is 200M, and it is also not recommended to modify it.

In parallel operations, the PGA memory available for parallel statements is pga_aggregate_target * 30%/DOP (Degree of parallelism parallelism).

3. Proprietary services (dedicated server) and sharing services (shared server)

The management and allocation of PGA memory is largely dependent on the service pattern. The following table shows the similarities and differences in the allocation of different parts of PGA memory in different modes:

Memory Area proprietary service sharing service

Session Memory Private Shared

Zone of the permanent zone PGA SGA

Region of the running area of the SELECT statement PGA PGA

The region in which the DML/DDL statement runs the PGA PGA