一个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且合适大小的值,如果触发这个值,会(有顺序的)杀掉数据库会话,保证数据库实例安全.