How to manage Oracle PGA

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