出品丨TeacherWhat
题图:Glass plate facade edge@Madrid, Spain by Joel Filipe from unsplash
关键字:19c、MGA、Managed Global Area、PGA、数据库入门、Database
正文约1500字,建议阅读时间3分钟
目录结构:
1. 什么是MGA?
2. 关于MGA的一些知识要点
3. 19c注意事项
4. 其他参考
我们知道SGA(System Global Area)一般由所有服务进程和后台进程共享的内存区域,而PGA(Program Global Area)是由每个服务进程、后台进程专有的的内存,每个进程都有一个PGA。
那什么是MGA呢?
MGA是Managed Global Area的缩写,主要用于在一组进程之间共享内存,简单而言,可以理解为介于SGA和PGA的一种中间状态。
关于MGA (Managed Global Area) 的更为准确的定义,可参考Mike Dietrich's Blog中对Doc ID 2638904.1的引用。
▲引用自Mike Dietrich's Blog
MGA让“少量集合”的进程(甚至是所有进程)在它们的生命周期(通常是一个查询)内共享地址空间。例如,在并行查询中,参与QC进程操作的PQ进程可以共享来自QC进程的名称空间以共享结果。
从Oracle 12.2.0.1开始推出这个特性,但是仅在Exadata RAC环境中使用, 从Oracle 18c开始, Non-Exadata 的RAC环境也会使用这个特性。
目前主要应用为RAC环境的IPC通信,和并行(PX)处理。
新的latch: MGA用于控制和保护对这段内存的访问,因此Oralce新追加了如下等待事件用于诊断相关性能问题。
latch: MGA shared context root latch
latch: MGA shared context latch
latch: MGA heap latch
关于Latch的相关内容可参考【高级OWI之Latch(闩锁)】
由于PGA和MGA是不同的内存区域,因此会从常规页分配PGA,通过/dev/shm领域分配MGA。但是MGA同样会被统计到PGA的使用中去,所以可以通过v$pgastat确认MGA的大小。另外,PGA和MGA的合计大小也将受到PGA_AGGREGATE_LIMIT的限制。
例:
--查看/dev/shm
$ ls -alrh /dev/shm/MGA
-rw-r----- 1 oracle oinstall 256M Oct 4 00:02 /dev/shm/ora_ffffffffb06a0994_5cd96_1_KSIPC_MGA_NMSPC_1_0_0.dat--MGA的大小
SQL> set linesize 200
SQL> col VALUE format 99999999999
SQL> select * from v$pgastat where NAME like '%MGA%';
NAME VALUE UNIT CON_ID
----------------------------- ------------ ------------ ----------
MGA allocated (under PGA) 268435456 bytes 0
maximum MGA allocated 268435456 bytes 0
通过设置_use_large_pages_for_mga参数为True,可以让MGA使用HugePage。
例:
SQL> select a.ksppinm "PARAMETER",b.ksppstvl "VALUE",a.KSPPDESC "DESCRIPTION"
from xksppia,xksppi a, xksppia,xksppcv b
where a.indx = b.indx
and a.ksppinm like '%_use_large_pages_for_mga%';
PARAMETER VALUE DESCRIPTION
------------------------------ ---------- ------------------------------------------------------------
_use_large_pages_for_mga FALSE MGA largepage enabled
19c注意事项
目前关于MGA的一些公开参考资料并不多,主要可以参考以下内容。
MOS Note:
MGA (Managed Global Area) Reference Note (Doc ID 2638904.1)
官方在线文档:
Grid Infrastructure Installation and Upgrade Guide for Oracle Solaris https://docs.oracle.com/en/database/oracle/oracle-database/19/cwsol/configuring-additional-shared-memory-identifiers-limit.html#GUID-A655E968-61F2-441B-97B3-61BE6A6487C1
Configuring Additional Shared Memory Identifiers Limit Starting with 18c, on Oracle Solaris systems, Oracle Database uses a new method of sharing memory among a group of processes, also known as Managed Global Areas (MGA). ★ The operating system memory allocation mechanism for this new method is Optimized Shared Memory (OSM).
Blog:
https://mikedietrichde.com/2020/08/20/mga-issue-and-it-is-fixed-with-oracle-19-8-0-and-newer/
MGA Issue – and it is fixed with Oracle 19.8.0 and newer
https://chinaraliyev.wordpress.com/2019/04/29/parallel-shared-hash-join/
Parallel Shared Hash Join This is a new method which is used to share memory between processes. In addition new stats names appeared in v$pgastat (MGA allocated (under PGA),maximum MGA allocated). Also, to protect the new shared memory region new latches and appropriate wait events (like latch: MGA.. ) have been introduced.
https://www.doag.org/formes/pubfiles/10893399/2018-DB-Anil_Nair-Oracle_RAC_Performance_Internals-Praesentation.pdf
New memory pool: MGA (Managed Global Area) –IPC buffers are allocated in the MGA (used to be the PGA before Exafusion) –MGA allows a set of processes to efficiently share an address space –MGA is not SGA or PGA, but something in-between
——End——