Configure and use OraclePGA memory

Welcome to the Oracle community forum and interact with 2 million technical staff to enter the PGA (ProcessGlobalArea), a private memory zone of serverprocess, which contains global variables, data structures, and some control information. In Oracle8i, PGA adjustment is very complex. You need to adjust SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_A

Welcome to the Oracle Community Forum, interact with 2 million technical staff> go to the PGA (Process Global Area), which is a private memory zone of server process, which contains Global variables, data structure and some control information. In Oracle8i, PGA adjustment is very complex. You need to adjust SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_A

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

PGA (Process Global Area) is a private memory Area of server process. It contains Global variables, data structures, and some control information. In Oracle8i, PGA adjustment is very complex. You need to adjust SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE,

PGA (Process Global Area) is a private memory Area of server process. It contains Global variables, data structures, and some control information. In Oracle8i, PGA adjustment is very complex. You need to adjust parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE. In ORACLE9I, you only need to adjust PGA_AGGREGATE_TARGET.

-- PGA_AGGREGATE_TARGET initialization settings

The PGA_AGGREGATE_TARGET value should be set based on the total amount of memory available for the Oracle instance. This parameter can be dynamically modified. Assume that the Oracle instance can allocate 4 GB of physical memory, and the remaining memory is allocated to the operating system and other applications. You may allocate 80% of the available memory to the Oracle instance, that is, 3.2 GB. Now you must divide the SGA and PGA regions in the memory.

In the OLTP system, the typical PGA memory settings should be a small part of the total memory (for example, 20%), and the remaining 80% is allocated to SGA.

OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

Copyright Sqlclub

In the DSS System, because some large queries are run, a typical PGA memory can allocate up to 70% of the memory.

DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

In this example, the total memory is 4 GB. For the DSS system, you can set PGA_AGGREGATE_TARGET to 1600 MB and OLTP to 655 MB.

-- Configure Automatic PGA Management

You do not need to restart the database and directly modify it online.

SQL> alter system set workarea_size_policy = auto scope = both;

System altered.

SQL> alter system set pga_aggregate_target = 512 m scope = both;

System altered.

SQL> show parameter workarea

Name type value Sqlclub

-----------------------------------------------------------------------------

Workarea_size_policy string AUTO -- set this to AUTO

SQL> show parameter pga

NAME TYPE VALUE

-----------------------------------------------------------------------------

Pga_aggregate_target big integer 536870912

SQL>

-- Monitor the performance of automatic PGA Memory Management

V $ PGASTAT: This view provides an instance-level PGA memory usage and automatic allocation statistics. Sqlclub Learning Community

SQL> set lines 256

SQL> set pages 42

SQL> SELECT * FROM V $ PGASTAT;

NAME VALUE UNIT

--------------------------------------------------------------------------------------

Aggregate PGA target parameter 536870912 bytes -- current PGA_AGGREGATE_TARGET Value

Aggregate PGA auto target 477379584 bytes -- the current PGA size that can be automatically allocated should not be smaller than PGA_AGGREGATE_TARGET

Content from Sqlclub.cn

Global memory bound 26843136 bytes -- the maximum size of the work area in auto mode. Oracle automatically adjusts the size according to the workload.

Total PGA inuse 6448128 bytes

Total PGA allocated 11598848 bytes -- maximum PGA allocation

Sqlclub

Maximum PGA allocated 166175744 bytes

Total freeable PGA memory 393216 bytes -- maximum idle size of PGA

PGA memory freed back to operating system 69074944 bytes

Total PGA used for auto workareas 0 bytes -- the size of the PGA allocated to auto workareas Sqlclub Learning Community

Maximum PGA used for auto workareas 1049600 bytes

Total PGA used for manual workareas 0 bytes

Maximum PGA used for manual work areas 530432 bytes

Over allocation count 1118 -- number of allocations after the instance is started. If the value is greater than 0, you need to increase the pga value copyright Sqlclub.

Bytes processed 114895872 bytes

Extra bytes read/written 4608000 bytes

Cache hit percentage 96.14 percent -- hit rate

16 rows selected. copyright Sqlclub

-- V $ PGA_TARGET_ADVICE

SQL> SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM v $ pga_target_advice;

The output of this query might look like the following:

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

--------------------------------------------

63 23 367

125 24 30

250 30 3

375 39 0

500 58 0

600 59 0

700 59 0 Sqlclub.cn

800 60 0

900 60 0

1000 61 0

1500 67 0

2000 76 0

3000 83 0

4000 85 0

It can be seen that when TARGET_MB is 375M, It is ESTD_OVERALLOC_COUNT = 0, so we can set PGA_AGGREGATE_TARGET to 375 M.