Oracle Architecture (III) PGA,UGA,CGA

1. Concept

PGA: The program global area, the memory area used by the server process, including data and control information for a particular server process, such as private information and operating system resources used.

UGA: The user global area, the memory area used by a specific session, such as the SQL workspace for the session, login authentication information, and so on. The impact of the session's SQL workspace size on query performance is critical, and UGA is allocated from the large pool of the SGA (if any) or shared pool (not set large pool) in Shared server mode dedicated In server mode, UGA is allocated from the PGA.

CGA: Calls the global zone, storing the data needed during the call, such as parse calls, executive calls, fetch calls, and so on. The data that is actually needed during the call, such as SQL area, the Sort area, and so on, is located in UGA, where the CGA stores only temporary processing data during the call, such as I/O cache, temporary stack space, and so on. CGA is located in the PGA (where the PGA is centrally managing these resources), created at the start of the call, dynamically allocated during invocation execution, and released after the call has ended.

2. Parameter setting

Workarea_size_policy = {AUTO | MANUAL}

{ALTER SYSTEM | ALTER session} SET Workarea_size_policy = {AUTO | MANUAL}

When set to auto, memory uses more SQL operations, such as sort, group-by, hash-join, bitmap merge, and bitmap create, which are automatically assigned by Oracle, and when set to manual, these actions will use the corresponding *_ The memory value set by the Area_size parameter.

*_area_size function in each session, the allocation of too small, many sessions may not have enough memory and inefficient, excessive allocation, a large number of sessions may waste a lot of memory space, 9i began the PGA Automatic management function to improve the situation. Using Pga_aggregate_target to set the entire PGA size, Oracle assigns the PGA to each session according to the actual needs, and tries to maintain the PGA total to no more than Pga_aggregate_target value.

Pga_aggregate_target = integer [K | M | G

ALTER SYSTEM

Sets the total PGA memory size. Oracle will try to ensure that the total PGA memory does not exceed this value, but is not absolute, and may sometimes exceed this value in order to avoid disk operations. Can be set to the total amount of memory minus the residual value of the SGA.

The initialization parameter is set to a value greater than 0, and Oracle automatically sets Workarea_size_policy to auto, setting the initialization parameter to 0,oracle to automatically set Workarea_size_policy to manual. After the Pga_aggregate_target is set, all *_area_size settings are ignored.

Hash_area_size = Integer

ALTER session

The unit is the number of bytes.

Sort_area_size = Integer

Alter session, ALTER SYSTEM ... DEFERRED

The unit is the number of bytes. If Sort_area_retained_size is not set, the allocated memory is released before the sort ends, before the return record begins, and if Sort_area_retained_size is set, the allocated memory is released after the last record returns. Inserts and updates on bitmap indexes are also used for sort_area_size (because each bitmap must be sorted after the index is scanned and then merged into a single bitmap).

Sort_area_retained_size = Integer

Alter session, ALTER SYSTEM ... DEFERRED

The unit is the number of bytes. Refers to the amount of sorted memory that is reserved in UGA, the sort operation ends, and the sorted memory is released after the last record is returned, but is released to UGA instead of the operating system.

Bitmap_merge_area_size,create_bitmap_area_size, the memory configuration used when bitmap indexing is created and updated.

_pga_max_size:maximum size of the PGA memory for one process. ALTER SYSTEM. The default value is 200M.

To view the _pga_max_size value method:

Select Ksppinm "Name", ksppstvl/1024/1024 | | M ' "Value", Ksppdesc "Desc"

From X$ksppi x, X$KSPPCV y

where x.indx = y.indx and ksppinm = ' _pga_max_size ';

_smm_max_size:maximum work area size in Auto mode (serial). {ALTER Session | ALTER SYSTEM}.