About 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 torn 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.
Difference between MGA/PGA/SGA:
The MGA is not the SGA or PGA. The PGA is private and the SGA is completely shared by all processes and is not elastic. The MGA is elastic (processes can create and drop their MGA segments dynamically), shared between a set of processes or all processes, and counted under the PGA target/limit values (in v$PGASTAT). The SGA is typically created once while the MGA is created on demand.
There can be multiple MGAs in an instance while we have just one SGA.
MGA Clients and Use Cases:
The MGA is a general feature which has multiple consumers and the benefit is based on the consumers. MGA consumers are: IPC, MGA hash join, IMCDT and PQ in current releases. PQ is not a client by default. When a query uses features like MGA hash join or IMCDT, PQs internally attach to the namespace. (In other words, MGA hash join and IMCDT are actually executed by PQs with MGA being shared.)
- IPC:
IPC is enable by default on Exadata. It's mainly used for shared PD, where big IPC segments (heap based) are allocated and shared across all the processes.
Shared PD meaning is that, sharing "protection domain". Sharing of PD : This helps a daemon process register/de-register memory regions and allow other processes to share those memory regions instead of having to register the memory regions again in each process.
These segments are registered with HA for RDBMS operations.
The primary consumers of IPC0 MGA namespace are Buffer Cache/DLM (kcl.c uses this through ksmsq APIs).
Since IPC0 alone performs memory registration on behalf of all other processes in the instance, it is normal for IPC0 to have high RSS in the OS.
- MGA Hash Join:
MGA hash join is used by parallel query execution. It's used by Hash join shared operations in the execution plan. It improves the performance of large joins.
It also:
- avoids/limits PQ distribution for hash-hash joins
- avoids the cost of PQ redistribution
- IMCDT
IMCDT is used by cursor duration tables. It's also used by parallel slaves to load into memory cursor duration tables.
IMCDT points to: In memory cursor duration tables. This can be controlled with hidden parameter "_in_memory_cdt"
See NOTE:2388236.1 What is _in_memory_cdt Parameter?
MGA Usage:
From 19c onward, we can now get MGA usage from database instance by querying the v$pgastat view. For example:
SQL> select * from v$pgastat;
NAME VALUE UNIT CON_ID
...
MGA allocated (under PGA) bytes 0
maximum MGA allocated bytes 0
...
Note: In 18c, the values associated with the MGA entries in v$pgastat are not displayed. (The values show as 0.) Reference:
Enhancement Bug:30596770 ADD VALUES FOR MGA IN 18C V$PGASTAT
New Wait Events Related to MGA:
latch: MGA shared context root latch
latch: MGA shared context latch
latch: MGA heap latch
