大家好，这里是公众号 DBA学习之路，致力于分享数据库领域相关知识。

社群交流

为了给大家提供一些技术交流的平台，目前已成立的技术交流群：

  • Oracle 数据库交流群
  • 国产数据库交流群
  • Linux 技术交流群
  • ChatGPT 4o 免费体验群

需要进群可以添加微信：Lucifer-0622，备注对应的群名即可。

@TOC

前言

最近有一个客户的数据库（12CR2）报错：ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT。经过分析之后，发现是因为一条 SQL 在执行解析时消耗了大量的 PGA，导致 PGA 超出了参数 PGA_AGGREGATE_LIMIT 的值上限。

针对这个问题，我在分析过程中研究了一下这个参数，并且整理了一些如何避免 ORA-4036 报错的建议。

PGA

什么是 PGA？

PGA（Program Global Area）是用于存储服务器进程数据和控制信息的内存区域。它是 Oracle 数据库在启动服务器进程时分配的非共享内存，仅供相应的服务器进程访问。每个服务器进程都有自己的 PGA，同时，后台进程也会分配独立的 PGA。

与 Oracle 数据库实例相关的所有服务器进程和后台进程的 PGA 内存总和被称为总实例 PGA 内存，即所有单独 PGA 的集合，简称为实例 PGA。

PGA（进程全局区）是特定于进程的一段内存。换句话说，PGA 是操作系统中某个进程或线程专用的内存，不允许系统中的其他进程或线程访问。PGA 一般是通过 C 语言运行时调用 malloc() 或 memmap() 来分配，而且可以在运行时动态扩大（或收缩）。PGA 绝对不会在 Oracle 的 SGA 中分配，而总是由进程或线程为自身分配。PGA 中的 P 代表 Process（进程）或 Program（程序），是不共享的。

PGA 内存中的其他区域通常用于完成内存中的排序、位图合并以及散列。

PGA_AGGREGATE_LIMIT

为什么会出现 PGA_AGGREGATE_LIMIT 参数？

在 Oracle 12C 版本之前，我们通常是通过设置参数 PGA_AGGREGATE_TARGET 来确定一个 PGA 的使用量。但是，PGA_AGGREGATE_TARGET 不会限制 PGA 内存使用量，它只是一个目标，用于动态调整进程工作区的大小。它不会影响允许超出此限制的其他 PGA 区域，也就是说这不是硬限制，有时候会超出上限，导致 PGA 内存使用无法准确掌控。

所以从 Oracle 12C 开始引入了一个参数 PGA_AGGREGATE_LIMIT，允许数据库管理员设置 PGA（Program Global Area）内存的使用上限。通过配置该参数，可以对 PGA 的使用量进行硬性限制。如果 PGA 使用超出设置的值，Oracle 数据库会中止或终止占用最多、不可调整 PGA 内存的会话或进程。

在 Oracle 18C 及更高版本中，MGA（Managed Global Area） 被包含在 PGA 中。这意味着在这些版本中，PGA 的大小需要适当增加，以容纳 MGA。PGA 内存分配要求 PGA_AGGREGATE_LIMIT 足够大，才能满足 MGA 和其他 PGA 的需求。

📢注意：连接的进程在其生命周期内不会将分配的内存释放回操作系统，除非进程断开连接。

这是预期行为，因为 PGA 在进程整个生命周期内保持分配状态，并根据运行时需求动态增长。如果操作系统需要，可能会为实例分配额外内存以处理大型排序等操作。然而，PGA 的内存一旦分配并释放用于实例操作（例如完成大型排序后），这部分内存将保留在进程中以供将来使用，而不会缩减或归还给操作系统。

ORA-4036

通过 oerr 可以查看 ORA-4036 的详细解释：

1
2
3
4
5
6
7
8
[oracle@oracle19c:/home/oracle]$ oerr ora 4036
04036, 00000, "PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT"
// *Cause:  Private memory across the instance exceeded the limit specified
//          in the PGA_AGGREGATE_LIMIT initialization parameter.  The largest
//          sessions using Program Global Area (PGA) memory were interrupted
//          to get under the limit.
// *Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce
//          memory usage.

当实例中的私有内存超出 PGA_AGGREGATE_LIMIT 初始化参数中指定的限制时，会发生 ORA-04036 错误。使用Program Global Area (PGA) 内存的最大会话被中断以低于限制。

此错误通常是由于使用、应用程序或配置问题引起的，但在某些情况下，它们可能是由 bug 问题引起的。

解决方案

其实避免 ORA-04036 错误的方法很简单，也就是避免内存使用超过 PGA_AGGREGATE_LIMIT 硬限制。

1、设置 PGA_AGGREGATE_LIMIT = 0

将参数 PGA_AGGREGATE_LIMIT 设置为 0 表示不限制 PGA 的增长（即无限制），相当于 Oracle 12C 之前的版本，此时 PGA 的增长将不受控制：

1
SQL> alter system set pga_aggregate_limit=0;

自然也就不会报错 ORA-04036。

2、增加初始化参数 PGA_AGGREGATE_LIMIT

这个就更简单了，不够我就加：

1
SQL> alter system set PGA_AGGREGATE_LIMIT = <value>;

PGA_AGGREGATE_LIMIT 的经验法则是：

PGA_AGGREGATE_LIMIT =(original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

具体可以参考以下文档：

3、减少内存使用

关于减少内存使用的情况比较复杂，这里我列举几种可能情况。

关闭 Optimizer Statistics Advisor 功能

从 Oracle 12CR2 开始，存在很多与 Optimizer Statistics Advisor 这个功能有关的问题：

📢注意：这里的 Optimizer Statistics Advisor 不是自动统计信息收集的 JOB，关闭这个功能完全不影响自动统计信息收集，不会对数据库造成任何影响。

在 Oracle 12CR2 ~ 19C 版本中想要关闭这个功能，需要打一个补丁 Bug 26749785 : PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK 后才能实现，21C 开始默认支持。

打补丁之后新增了 “AUTO_STATS_ADVISOR_TASK” 的属性设置，可以手动启用/禁用 AUTO_STATS_ADVISOR_TASK。

检查当前属性：

1
2
3
4
5
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
TRUE

禁用统计顾问：

1
2
3
4
5
6
7
8
9
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
FALSE

不建议直接禁用统计信息收集任务！除非有定时手动收集统计信息的计划。

具体可以参考以下文档：

使用 temp LOBs

例如，Oracle Text 应用程序可能会在 kolarsCreateCtx 调用时分配大量 PGA。如果未显式释放，可能导致内存占用居高不下。

因此，应用程序需要确保释放创建的临时 LOB 和其他资源，以避免内存泄漏和不必要的资源消耗。检查应用程序代码并确保正确调用内存释放操作。

当连接进程的内存不断增长时

PGA 中的某些区域无法通过初始化参数直接控制，例如 PL/SQL 内存集合（如 PL/SQL 表和 VARRAY）以及本地 PL/SQL 变量。这些区域的大小取决于编程代码和处理的数据量，可能会变得非常大（内部限制为 10G，最高可达 20G），从而消耗大量内存。此外，编程错误也会导致内存使用量过大。例如，递归或无限循环可能会耗尽内存。

这种内存增长可以通过采用良好的编程实践进行控制。例如，结合使用 LIMIT 子句和 BULK COLLECT，可以有效减少一次性加载到内存的数据量，从而优化内存使用并避免不必要的资源消耗。

因此，应采用良好的编码实践来避免 PL/SQL 过度使用 PGA。

这里分享一些可以查看当前进程消耗内存量的 SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
COLUMN alme     HEADING "Allocated MB" FORMAT 99999D9
COLUMN usme     HEADING "Used MB"      FORMAT 99999D9
COLUMN frme     HEADING "Freeable MB"  FORMAT 99999D9
COLUMN mame     HEADING "Max MB"       FORMAT 99999D9
COLUMN username                        FORMAT a15
COLUMN program                         FORMAT a22
COLUMN sid                             FORMAT a5
COLUMN spid                            FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
       SUBSTR(s.program,1,22) program , s.process pid_remote,
       s.status,
       ROUND(pga_used_mem/1024/1024) usme,
       ROUND(pga_alloc_mem/1024/1024) alme,
       ROUND(pga_freeable_mem/1024/1024) frme,
       ROUND(pga_max_mem/1024/1024) mame
FROM  v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem,logon_time;

COLUMN category      HEADING "Category"
COLUMN allocated     HEADING "Allocated bytes"
COLUMN used          HEADING "Used bytes"
COLUMN max_allocated HEADING "Max allocated bytes"
SELECT pid, category, allocated, used, max_allocated
FROM   v$process_memory
WHERE  pid = (SELECT pid
              FROM   v$process
              WHERE  addr= (select paddr
                            FROM   v$session
                            WHERE  sid = 141));

通过上述执行结果可以快速追踪到具体是哪个进程的组件在消耗大量的内存。

具体可参考：

BUG 问题

当然，不可避免的还有一些 BUG 会导致 PGA 内存使用的问题。这里就不一一介绍了，具体可以参考：OERR: ORA-4036 “PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT” Reference Note (Doc ID 2010590.1)

选择对应的数据库版本进行检索，通常可以很快的找到需要的 BUG 文档。

写在最后

本文通过介绍参数 PGA_AGGREGATE_LIMIT 以及 ORA-4036 错误，引出了如何避免 ORA-4036 问题的一系列解决方案，与君共勉！

参考官方文档：

往期精彩文章

眼见不一定为实：一条 SQL 背后隐藏的 BUG
第 1 天：VirtualBox 虚拟机安装 Linux 系统
第 2 天：RHEL 6 安装 Oracle 11GR2 数据库
第 3 天：RHEL 7 安装 Oracle 11GR2 数据库

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」