一个SQL就让内存耗光了
一个SQL内存为什么就没了呢
最近遇到一个故障,研发新上线一个功能,成功把主机内存耗光,导致实例重启.复现一个SQL如何把数据库的内存耗光.
实验环境
Oracle Database 19c(故障发生在11G,12C增加了新参数,可以避免此类故障发生)
相关脚本
执行脚本,使用python执行,目的:一个会话一直执行dbms_xplan.display()
import cx_Oracle
from time import sleep
conn = cx_Oracle.connect("user01/user01@192.168.56.110:1521/pdb")
cur = conn.cursor()
cur.execute("explain plan for select * from t1")
i = input("explan 执行完成,输入Y:")
if i == 'Y':
for j in range(100):
cur.execute("select * from table(dbms_xplan.display())")
i = input("select已执行100次,输入Y进入死循环:")
if i == 'Y':
while True:
try:
cur.execute("select * from table(dbms_xplan.display())")
# sleep(1)
except cx_Oracle.DatabaseError as e:
print(e)
`
执行步骤及观察信息
-
拉起脚本
python -u "c:\Users\Li\Desktop\pga\pga1.py"
-
PGA参数信息
NAME TYPE VALUE ----------------------------------- ----------- ------------------------------ pga_aggregate_limit big integer 300M pga_aggregate_target big integer 164M
-
观察PGA的使用情况
OSpid Orapid Sess id Serial# PGA alloc PGA used oracleuser Program -------- ------- ------- ---------------------- --------------- ------------ -------------------- 9470 36 41 42514 3,243,093 2,249,949 USER01 python.exe <==explan执行完 9470 36 41 42514 17,988,693 14,451,645 USER01 python.exe <==100次select执行完 12318 36 41 42514 239,041,621 234,468,989 USER01 python.exe <==死循环后,限制了300M最大内存使用量,已经使用240M
结论
观察到pga的分配已经超过pga_aggregate_target值后还在一直分配内存,并最终在接近pga_aggregate_limit会话被KILL.日志如下:
2024-02-04T16:28:14.964620+08:00
PGA memory used by PDB 3 exceeds PGA_AGGREGATE_LIMIT of 300 MB
(3):KILL SESSION for sid=(41, 42514):
(3): Reason = pga_aggregate_limit
(3): Mode = KILL HARD SAFE -/-/-
(3): Requestor = DBRM (orapid = 14, ospid = 7699, inst = 1)
(3): Owner = Process: USER (orapid = 36, ospid = 12318)
(3): Result = ORA-31
如果PLAN_TABLE中有内容,一直执行dbms_xplan包去访问PLAN_TABLE,最终会导致操作系统内存被耗尽.
官方描述
Keep in mind that the PGA_AGGREGATE_TARGET does not limit the amount of PGA memory usage. It is only a target and is used to dynamically size the process work areas. It does not affect other areas of the PGA that are allowed to grow beyond this limit.
12c introduced the database parameter PGA_AGGREGATE_LIMIT to limit overall total process memory usage on a database instance.
规避措施
- 因为11G中没有硬限制,只能在11G的库中做好监控,如果有进程使用PGA超过某个阈值,断开会话时会释放PGA内存,避免内存被耗尽.
- 12C以上版本配置pga_aggregate_limit参数为非0且合适大小的值,如果触发这个值,会(有顺序的)杀掉数据库会话,保证数据库实例安全.
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了