Oracle PGA Management approach

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.

Composition of the PGA of 2.1

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

2.1.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.1.2 Cursors and SQL extents (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.