Oracle內存分配不足的過程解析(業(yè)務干掛數(shù)據(jù)庫)

更新時間：2025年02月15日 10:25:22 作者：IT邦德

本文介紹了Oracle數(shù)據(jù)庫內存分配不足的問題,原因主要是業(yè)務劇增導致的內存不足,通過分析AAS負載、等待事件、transactions和阻塞情況,發(fā)現(xiàn)PGA內存出現(xiàn)了嚴重抖動,感興趣的朋友一起看看吧

1.故障現(xiàn)象

應用反饋數(shù)據(jù)庫已經(jīng)夯住，通過監(jiān)控平臺看到很多高警如下，初步判定跟Log file sync有關系，但是別經(jīng)驗主義，詳細的分析還要看具體報告！

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，這明顯的出現(xiàn)了很嚴重的性能瓶頸。

2.2 等待事件

等待事件是衡量數(shù)據(jù)庫優(yōu)化情況的重要指標，明顯出現(xiàn)了異常。

acknowledge over PGA limit的解釋為：如果實例接近PGA_AGGREGATE_LIMIT限制，它將迫使需要更多PGA的進程等待一段時間，同時發(fā)現(xiàn)了PGA的內存在故障期間嚴重出現(xiàn)了內存抖動。

再次確認數(shù)據(jù)庫參數(shù)的設置，PGA_AGGREGATE_LIMIT為20G，sga_target為55G，processes為5120的設置，按照官方的經(jīng)驗其實這是是合理的。

（1）OLTP系統(tǒng)：
SGA_TARGET = (total_mem * 0.8) * 0.8
PGA_AGGREGATE_TARGET=(total_mem * 0.8) * 0.2
（2）OLAP（DSS）系統(tǒng)：
SGA_TARGET= (total_mem * 0.8) * 0.5
PGA_AGGREGATE_TARGET =(total_mem * 0.8) * 0.5
（3）PGA_AGGREGATE_LIMIT=3MB*processes
RAC環(huán)境為：PGA_AGGREGATE_LIMIT=5MB*processes

那么這次怎么會導致over PGA limit呢？最大可能為業(yè)務劇增，那么繼續(xù)排查。

那么如果想盡快恢復業(yè)務，可以臨時設置PGA_AGGREGATE_LIMIT=0處理，但這不是長久之計。

2.3 transactions分析

ADDM的報告中也給出了這個結論，明顯出現(xiàn)了劇增的業(yè)務。

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

user calls/(user commits+user rollbacks) 本次平均值為4.84= 4.84/(0.33+0.67) ，平均每4.84 次 user calls 就會有一次 commit，業(yè)務提交特別的頻繁。

2.4 阻塞分析

比較’log file sync’和’log file parallel write’的平均等待時間，此時IO存在嚴重的阻塞。

大量的SQL出現(xiàn)嚴重的library cache lock、latch: shared pool。

3.總結分析

上面的一切初始建議值，都是在上線前的最佳配置建議值，在上線執(zhí)行一段時間后，系統(tǒng)執(zhí)行特性真面目就慢慢的體現(xiàn)出來了，這時，就應該依據(jù)執(zhí)行實際需求及時的調整SGA_TARGET與PGA_AGGREGATE_TARGET的值了，但是業(yè)務也要做好評估，必須期間提升硬件性能，同時一些低效率低的SQL也要做好優(yōu)化！