Oracle 19c MGA (Managed Global Area) 特性說明與計算方式 ORA-04030

關於 MGA (Managed Global Area) 說明 :

The MGA allows a "smaller set" of processes (or even all processes) to share an address space for the duration (typically a query). The MGA is made up of namespaces which contain segments and heaps with space management either directly or through a heap manager (KGH).

Processes can attach to their namespace for the duration they require. For e.g., in a parallel query environment, PQs participating with the QC can share the namespace from QC to share the results. Once done, they will detach from the same and the namespace can be tiered down.

The MGA is allocated dynamically which gives us more flexibility to create, control and share. The sharing is dynamic in the sense that processes coordinate before sharing. So in that sense, MGA sits between the SGA and PGA. In addition, since the MGA is a shared memory area, a latch (latch: MGA) is used to control access and protect it.

計算方式 :

PGA_AGGREGATE_LIMIT = ((maximum aggregate PGA in use for the life of the instance) + ((maximum number of attached processes for the life the instance) * 5M)) * 1.1

--MAX_PGA
col max_pga for 99999999.9
select value/1024/1024 max_pga_mb from v$pgastat where name='maximum PGA allocated' 
minus 
select value/1024/1024 max_pga_mb from v$pgastat where name='MGA allocated (under PGA)';

--MAX_UTILIZATION
select max_utilization from v$resource_limit where resource_name='processes';

PGA_AGGREGATE_LIMIT Example :

SQL> col max_pga for 99999999.9
SQL> select value/1024/1024/1024 max_pga_gb from v$pgastat where name='maximum PGA allocated' 
minus 
select value/1024/1024/1024 max_pga_gb from v$pgastat where name='MGA allocated (under PGA)';

MAX_PGA_GB
----------
19.6

SQL > select max_utilization from v$resource_limit where resource_name='processes';

MAX_UTILIZATION
---------------
2000

(19.6GB) + ((2000) * 5M)*1.1/1024 = 30.6GB

MGA System Configuration :

MGA requires /dev/shm to be mounted and configured to at least the size of the PGA aggregate target (PGA_AGGREGATE_TARGET). When multiple instances are running, the size of /dev/shm needs to be at least sum(PGA_AGGREGATE_LIMIT) across all of the instances.

A few other factors to consider on Linux:

• The MGA is counted under PGA aggregate limit and it uses /dev/shm pages. /dev/shm pages are not swappable unlike regular PGA memory.
• If /dev/shm is not mounted, an ORA-27779 error will be raised during segment creation.
• If pga aggregate limit is explicitly set to 0, issues such as unpublished Bug 30851951 - OPEN FDS TO /DEV/SHM/*KSIPC_MGA*.DAT CAUSES FILE-MAX LIMIT TO EXCEED AND CRASHES INSTANCE can occur.
• The MGA doesn't involve MEMORY_TARGET. It will allocate its own files in /dev/shm.
• On 18c and 19c, IPC uses MGA. It requires 5MB per process in /dev/shm. The total usage is capped under PGA_AGGRETATE_LIMIT.

Configure MGA Large Pages on Linux :

1. Add additional 'n' GB of large pages. (n * 1024 * 2 pages + existing number of pages. Echo the same to /proc/sys/vm/nr_hugepages).

NOTE: 'n' is number of pages 2048 Kbytes that we wish to configure for MGA. For example, for a single instance, MGA requires /dev/shm to be mounted and configured to at least the size of the PGA aggregate target. If PGA_AGGREGATE_TARGET is 10G (10,485,760 K) and pagesize is 2048 K then additional 10 GB of large pages is 5,120 pages (5,120 pages * 2048 K).

2. Mount huge tlb fs.

mount -t hugetlbfs -o size=<n>g,mode=0777 none /dev/hugepages

3. Oracle setting: Add init.ora parameter: _use_large_pages_for_mga = TRUE

參考來源:

MGA (Managed Global Area) Reference Note (Doc ID 2638904.1)

Sizing the PGA in Oracle 19c - How to Account for the MGA Size (Doc ID 2808761.1)