PGA memory allocation principles in Oracle

Oracle evaluates the execution plan with the PGA_AGGREGATE_TARGET parameter, and selects the maximum or minimum memory that can be used in sort, HASH_JOIN, or Bitmap operations.

Oracle evaluates the execution plan with the PGA_AGGREGATE_TARGET parameter, and selects the maximum or minimum memory that can be used in sort, HASH_JOIN, or Bitmap operations.

Pga_aggregate_target is usually abbreviated as P_A_T. This parameter also limits global pga allocation and private Workspace Memory allocation.

In Oracle9i and 10gr1, the memory usage of a single SQL operation has the following restrictions:

For serial operations, the pga memory that can be used by a single SQL operation is allocated in the following principle:

MIN (5% * PGA_AGGREGATE_TARGET, 100 MB)

The 5% * PGA_AGGREGATE_TARGET is actually determined by _ smm_nax_size. This parameter limits the maximum workspace usage in automatic workspace mode.

For parallel operations:

30% PGA_AGGREGATE_TARGET/DOP (DOP = degree if parallelism, DEGREE of PARALLELISM)

In oracle10gr2 and oracle11g, the memory usage has the following restrictions:

The pga memory that can be used for serial operations is allocated according to the following principles:

If P_A_T <500 MB, _ smm_max_size = 20% P_A_T

If P_A_T is between 100 MB and MB, _ smm_max_size = MB

If P_A_T is between 10% MB and MB, _ smm_max_size = * P_A_T

If P_A_T> 2560MB, _ smm_max_size = 262060 MB

For parallel operations, the available pga memory is allocated according to the following principles:

50% PGA_AGGREGATE_TARGET/DOP (DOP = Degree of parallelism, Degree of parallelism)

However, note that when dop is <5, the _ smm_max_size limit takes effect. When the degree of parallelism exceeds 5, the parameter _ smm_px_max_size that restricts parallelism takes effect.

Starting from oralce10g, the pga algorithm is affected by a new implicit parameter _ newsort_abled. If this parameter is set to false, the database uses the algorithm rules in oracle9i:

Sys @ ORCL> @ gethidpar. SQL

Enter the value of name: newsort_enabled

4: and x. ksppinm like '% & name %'

New Value 4: and x. ksppinm like '% newsort_enabled %'

KSPPINM KSPPSTVL

_ Newsort_enabled TRUE

The above @ gethidpar. SQL content:

Select x. ksppinm, y. ksppstvl, x. ksppdesc from x $ ksppi x, x $ ksppcv y wherex. indx = y. indx

And y. inst_id = userenv ('instance ')

And x. inst_id = userenv ('instance ')

And x. ksppinm like '% & name % ';

To understand the automatic adjustment of pga, you also need to distinguish between the tunable memory size and the untunable memory size. The adjustable MEMORY is used by the SQL workspace, the remaining part is the memory that cannot be adjusted

Oracle evaluates the execution plan with the PGA_AGGREGATE_TARGET parameter to the maximum or minimum memory that can be used in sort, HASH_JOIN, or Bitmap operations, so as to select the optimal execution plan.

For a pure database server, we usually need to save 20% of the physical memory for the operating system, and the remaining 80% can be allocated to oracle, while the oracle memory is composed of pga and sga, pga can occupy 20% of oracle's memory consumption (OLTP system) to 50% (DSS System)

You can use v $ process to query the usage of pga:

V $ PROCESS displays information about the currently active processes.

Column

Datatype

Description

ADDR

RAW (4 | 8)

Address of the process state object

PID

NUMBER

Oracle process identifier

SPID

VARCHAR2 (24)

Operating system process identifier

PNAME

VARCHAR2 (5)

Name of this process

USERNAME

VARCHAR2 (15)

Operating system process username

Note: Any two-task user coming operation ss the network has "-T" appended to the username.

SERIAL #

NUMBER

Process serial number

TERMINAL

VARCHAR2 (30)

Operating system terminal identifier

PROGRAM

VARCHAR2 (48)

Program in progress

TRACEID

VARCHAR2 (255)

Trace file identifier

TRACEFILE

VARCHAR2 (513)

Trace file name of the process

BACKGROUND

VARCHAR2 (1)

1 for a background process; NULL for a normal process

LATCHWAIT

VARCHAR2 (8)

Address of the latch the process is waiting for; NULL if none

LATCHSPIN

VARCHAR2 (8)

This column is obsolete

PGA_USED_MEM

NUMBER

PGA memory currently used by the process

PGA_ALLOC_MEM

NUMBER

PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)

PGA_FREEABLE_MEM

NUMBER

Allocated PGA memory which can be freed

PGA_MAX_MEM

NUMBER

Maximum PGA memory ever allocated by the process

For more details, please continue to read the highlights on the next page:

Related reading:

Modify the size of the Oracle database SGA and PGA

Oracle memory structure-PGA

Differences between SGA and PGA in dedicated and shared Oracle Mode

Oracle memory management PGA

Comprehensive Analysis of Oracle memory PGA

Oracle memory allocation-memory allocation (SGA and PGA) structure of SGA and PGA