业务干挂数据库，Oracle内存分配不足

📢📢📢📣📣📣
作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
Oracle、PostgreSQL ACE
CSDN博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理

最近一套核心的19C业务库挂了，严重的影响了生产，经过全方位的排查，分析出来的结果业务剧增导致的内存不足，特将本次故障分析分享给大家！

1.故障现象

应用反馈数据库已经夯住，通过监控平台看到很多高警如下，初步判定跟Log file sync有关系，但是别经验主义，详细的分析还要看具体报告！

Warning: log write elapsed time 783ms, size 5KB
*** 2025-01-20T11:56:02.114674+08:00
Warning: log write elapsed time 2215ms, size 4KB
*** 2025-01-20T12:00:30.111624+08:00
Warning: log write elapsed time 657ms, size 146KB
*** 2025-01-20T12:02:39.413947+08:00
Warning: log write elapsed time 534ms, size 109KB
*** 2025-01-20T12:02:40.422849+08:00
Warning: log write elapsed time 578ms, size 86KB
*** 2025-01-20T12:02:40.981447+08:00
Warning: log write elapsed time 558ms, size 41KB

2. 分析过程

2.1 AAS负载

一看AAS,吓一跳啊，AAS>> # of CPUS，这明显的出现了很严重的性能瓶颈。

2.2 等待事件

等待事件是衡量数据库优化情况的重要指标，明显出现了异常。

acknowledge over PGA limit的解释为：如果实例接近PGA_AGGREGATE_LIMIT限制，它将迫使需要更多PGA的进程等待一段时间，同时发现了PGA的内存在故障期间严重出现了内存抖动。

再次确认数据库参数的设置，PGA_AGGREGATE_LIMIT为20G，sga_target为55G，processes为5120的设置，按照官方的经验其实这是是合理的。

（1）OLTP系统：
SGA_TARGET = (total_mem * 0.8) * 0.8
PGA_AGGREGATE_TARGET=(total_mem * 0.8) * 0.2
（2）OLAP（DSS）系统：
SGA_TARGET= (total_mem * 0.8) * 0.5
PGA_AGGREGATE_TARGET =(total_mem * 0.8) * 0.5
（3）PGA_AGGREGATE_LIMIT=3MB*processes
RAC环境为：PGA_AGGREGATE_LIMIT=5MB*processes

那么这次怎么会导致over PGA limit呢？最大可能为业务剧增，那么继续排查。

那么如果想尽快恢复业务，可以临时设置PGA_AGGREGATE_LIMIT=0处理，但这不是长久之计。

2.3 transactions分析

ADDM的报告中也给出了这个结论，明显出现了剧增的业务。

一般来说transactions不超过200都是正常的，或者200左右都是正常的，超多1000就是非常繁忙了！

user calls/(user commits+user rollbacks) 本次平均值为4.84= 4.84/(0.33+0.67) ，平均每4.84 次 user calls 就会有一次 commit，业务提交特别的频繁。

2.4 阻塞分析

比较’log file sync’和’log file parallel write’的平均等待时间，此时IO存在严重的阻塞。


大量的SQL出现严重的library cache lock、latch: shared pool。

3.总结分析

上面的一切初始建议值，都是在上线前的最佳配置建议值，在上线执行一段时间后，系统执行特性真面目就慢慢的体现出来了，这时，就应该依据执行实际需求及时的调整SGA_TARGET与PGA_AGGREGATE_TARGET的值了，但是业务也要做好评估，必须期间提升硬件性能，同时一些低效率低的SQL也要做好优化！