oracle：Per-Process PGA memory limit

前几日，东区某客户的19c rac出现了ORA-04030，从报错的trace来看，使用了32g的PGA，对于单进程内存上限众说纷纭，有很多不同意见，有些说2g、有些说4g。。。，本篇文章就深入分析一下oracle进程内存上限。

说到PGA不得不关联到UGA和CGA，下面参考个人觉得写得很好的blog，对PGA、UGA、CGA进行了详细的概述。

https://blog.titanwolf.in/a?ID=01450-1639cbbd-7123-4e8c-afe0-4d8899dd8951

The Process Global Area (PGA) can be understood as either Process Global Area or Program Global Area. Its memory segment is in the Process Private Memory instead of Shared Memory. It is a global area It means that it contains all the global variables and data structures that the code may enter, but it is not shared by all processes. Each Oracle server process contains its own PGA, which only contains the specific related to the process Information. The structure in the PGA does not need to be protected by latches, because other processes cannot enter it to access it.

The PGA contains information about the operating system resources that the process is using and the state of the process, while the Oracle shared resources used by other processes are in the SGA. PGA is private rather than shared, this mechanism is necessary , Because these resources can be cleared and released when the process dies.

PGA contains two main areas: Fixed PGA and Variable PGA or PGA Heap.The role of Fixed PGA is similar to Fixed SGA, and both contain atomic variables (inseparable), small data structures and pointers to Variable PGA.

Variable PGA is a heap. Its Chunks can be viewed from the Fixed Table X$KSMPP. The structure of this table is the same as the X$KSMSP mentioned earlier. PGA HEAP contains some permanent memory related to the Fixed Table. It It is dependent on the settings of some parameters. These parameters include DB_FILES, LOG_FILES, CONTROL_FILES.

UGA (User Global Area) contains information about a specific session, including the following:

• The duration and runtime area of ​​the opened cursor
• Package status information, specific variables
• Java session state
• Available ROLES
• Tracking events that are ENABLE
• Effective NLS parameter settings
• Open DBLINK
• Conversation entry control

Like PGA, UGA is also composed of two areas: Fixed UGA and Variable UGA, also known as UGA HEAP.Fixed UGA contains about 70 atomic variables, small data structures and pointers to Variable UGA.

Chunks in UGA HEAP can obtain relevant information from their own sessions by looking at the table X$KSMUP. The structure of this table is the same as X$KSMSP. UGA HEAP contains some permanent memory segments related to fixed tables, and some The settings of the parameters are dependent. These parameters are OPEN_CURSORS, OPEN_LINKS, and MAX_ENABLE_ROLES.

The location of UGA in memory depends on the configuration mode of the session. If the configuration mode of the session connection is dedicated server mode (DDS), that is, a session corresponds to a process, UGA is placed in the PGA. In the PGA, Fixed UGA is One of them is a Chunk, and UGA HEAP is a subheap of PGA. If the session connection is configured as shared server mode (MTS), Fixed UGA is a Chunk in the SHARED POOL, and UGA HEAP is in the SHARED POOL Subheap

Unlike other global areas, the Call Global Area exists temporarily. It only exists during the call to the data, generally when the lowest-level call to the instance requires CGA, as follows:

• Analyze a SQL statement
• Execute a SQL statement
• To take out the output of a SELECT statement,

a single CGA is needed for recursive calls. During the analysis of the SQL statement, the recursive call to the data dictionary information is needed, because the SQL statement is grammatically analyzed, and the statement During the optimization period, the execution plan must be calculated. When the PL/SQL block is executed, the execution of the SQL statement is also required to be recursively called, and the trigger execution is also required to be processed when the DML statement is executed.

Regardless of whether UGA is placed in the PGA or in the SGA, CGA is a subheap of the PGA. An important inference of this fact is that the session must be a process during a call. For an Oracle database process in an MTS Understanding this point during application development is very important. If there are more calls, the number of processes must be increased to accommodate the increase in calls.

Without the data structure in CGA, CALLS cannot work. In fact, the data structure related to a CALL is generally placed in UGA, such as SQL AREA, PL/SQL AREA and SORT AREA, they must all be in UGA. Because they must always exist and be available between the CALLS. The data structure contained in the CGA must be released after a CALL. For example, the CGA contains information about recursive calls, direct I/O BUFFER, etc. There are other temporary data structures.

对于PGA上限值，首先需要说明的是“_smm_max_size”和“_pga_max_size”非常具有迷惑性，它们仅仅与PGA自动管理时的workarea部分相关。同时 _pga_max_size和 _smm_max_size 的值与pga_aggregate_target也关系密切。

对于UGA和CGA的内存分配，oracle在9.2开始，使用Real-Free Memory Allocator，这样的好处是使用完成之后可以立即释放给操作系统，在11g中， Real-Free Memory Allocator 的分配机制是每次分配参数“_realfree_heap_pagesize_hint”的page size，默认为65536。在12c以上版本参数名字改为了“ _realfree_heap_pagesize ”

通过strace也可以验证每次分配内存都是使用 “_realfree_heap_pagesize_hint” 大小

每次分配进程都会持有一个虚拟内存的map，而每个进程能持有的最大map数量由vm. max_map_count 控制，默认为65530

所以在11g当中，所有参数默认的情况下，进程能持有的最大内存是 max_map_count * _realfree_heap_pagesize_hint =4G。

但是如果不使用 Real-Free Memory Allocator 去分配内存的话，即“_use_realfree_heap”为false时，就与“ _realfree_heap_pagesize_hint ”无关了，此时每次分配的内存依然一致，是以“ _uga_cga_large_extent_size ”为大小去分配，默认为 262144 。

同样通过strace跟踪可以验证，这样的话理论上的内存上限为 max_map_count * _uga_cga_large_extent_size =16G，但是这一点我不敢确认，因为我的测试环境内存太小无法验证。

回到案例，那么为什么在19c中，所有参数都是默认的情况下，PGA内存达到了32G之后才报出ORA-04030呢？我们看看报错的trace文件

可以看到 blksz还是65536，但是PGA确实达到了32G之后报出了ORA-04030，搜索Process Map Dump可以看到仅仅使用了四百多个map就达到了32G的内存，充分说明每次分配的大小并不是65536。

查看具体的map信息发现，有些map映射的虚拟内存空间非常大达到了 256M。这是怎么回事呢？

通过strace跟踪发现，19c的Real-Free Memory Allocator采用了一种动态分配的方式，有点类似data extent的auto管理方式。不确定12c是否是这种方式没有去验证。

可以看到初始分配大小仍然采用“ _realfree_heap_pagesize”进行分配，分配到大概50次之后开始进行调整，调整粒度是乘以2，一直递增下去，最大应该是能达到256M，这也是为什么19c的进程使用了如此少的map内存就能达到32g的原因，那么19c的内存上限与vm.map_max_count的设置就没有关系了，因为根本不需要65530那么多的map，进程内存就可以达到32g的进程内存上限。

那么19c PGA的内存上限由什么决定呢？从trace里面看个人猜测应该是 blksz * maxblk ，blksz就是参数” _realfree_heap_pagesize”,而 maxblk 并没有找到出处，从11g-19c的maxblk都是524288，应该是代码层面写死的，11g之所以只能达到4G的上限是因为统一分配的内存大小受到了vm.map_max_count(65530)的限制。而19c是动态分配的大小，并且都是” _realfree_heap_pagesize ”的整数倍，当 iniblk= maxblk 时，就会达到内存上限。

那么19c中，如何避免PGA使用如此32G大内存呢？

• resource manager 设置session_pga_limit
• event 10261