Oracle 19c数据库内存占用无法突破阈值如何排查优化？

1.1 Oracle数据库层面参数 #

• memory_target：你仅配置了静态上限memory_max_target，若未配置动态可用的memory_target，数据库实际可分配内存会继承扩容前的旧值，无法达到memory_max_target的上限
• sga_max_size：即使开启AMM自动内存管理，若遗留的sga_max_size仍为扩容前的48GB左右，会直接限制SGA的最大分配值，导致总内存无法突破原有上限
• pga_aggregate_limit：该参数是PGA的硬上限，你原有配置仅12GB左右，若未调整的话PGA无法分配更多内存，叠加SGA的限制自然总内存无法提升
• use_large_pages：Windows平台若未开启大页配置，Oracle无法分配大段连续物理内存，也会出现内存分配上限卡壳的问题

1.2 Windows系统层面配置 #

• 锁定内存页权限：Oracle运行所属的Windows用户若无「锁定内存页」的系统权限，会被系统限制可分配的物理内存上限，无法占用大内存
• 系统虚拟内存配置：若Windows虚拟内存设置过小，也会限制Oracle进程的总内存分配上限
• 内存压缩/内存完整性功能：Windows开启这两个功能后，会限制单进程的最大内存占用量

• 补全内存参数配置：执行alter system set memory_target=104G scope=both;，若sga_max_size、pga_aggregate_limit存在限制，同步调整到预期值（例如sga_max_size=90G、pga_aggregate_limit=20G）后重启数据库生效
• 预热数据库缓冲池：执行多个大表的全表扫描查询，将数据块加载到Buffer Cache中，也可通过exec dbms_cache.keep('大表名','KEEP');将大表钉在缓冲池，强制占用SGA内存
• 触发大量PGA占用：启动多组并行会话同时执行大排序、大哈希连接的查询，强制分配更多PGA内存
• 切换为ASMM手动内存管理：若AMM模式一直存在上限问题，可关闭AMM，改用手动分配模式：执行alter system set memory_max_target=0 scope=spfile;、alter system set memory_target=0 scope=spfile;，再分别配置sga_target=90G、sga_max_size=90G、pga_aggregate_target=16G、pga_aggregate_limit=20G，手动分配的模式在Windows平台兼容性更强，不易出现内存上限问题

• 第一步：导出所有内存参数当前值，执行show parameter memory、show parameter sga、show parameter pga，确认所有参数值是否符合预期，重点排查memory_target、sga_max_size、pga_aggregate_limit三个参数
• 第二步：查看Oracle告警日志，检查数据库启动过程中是否存在内存分配失败、权限不足类的报错，定位具体限制来源
• 第三步：核查Windows系统权限，进入本地安全策略的「用户权限分配」模块，确认Oracle运行用户已被添加到「锁定内存页」的权限列表中，若未添加需配置后重启服务器生效
• 第四步：查询内存动态视图确认分配情况：执行select * from v$memory_dynamic_components;查看SGA、PGA各组件的当前分配值和上限；执行select sum(pga_alloc_mem) from v$process;查看PGA当前总分配量；执行select * from v$sgastat where name='free memory';查看SGA空闲空间
• 第五步：测试手动内存分配可行性，尝试执行alter system set db_cache_size=80G scope=both;，看是否有报错，根据报错内容定位限制原因
• 第六步：排查Windows系统层面限制，查看任务管理器剩余可用内存是否充足，确认是否开启了内存压缩、内存完整性等会限制单进程内存的系统功能

内容的提问来源于stack exchange，提问作者Paul Stearns