负载极低的库却触发ORA-04031

2024年 6月 12日 76.3k 0

1、问题信息

负载极低的库却触发ORA-04031-1

2、问题定位

2.1、数据库alert报警日志

[oracle@ trace]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@ trace]$ tail -3000f alert_orcl.log

Tue Jan 23 17:31:07 2024
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1764.trc (incident=295009):

ORA-04031: Ϟ·¨·ׅ➴160 ؖ½ڵĹ²ЭŚ´䞨"shared pool","unknown object","sga heap(1,0)","modification ")

Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_295009/orcl_ora_1764_i295009.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jan 23 17:31:10 2024
Dumping diagnostic data in directory=[cdmp_20240123173110], requested by (instance=1, osid=1764), summary=[incident=295009].
Tue Jan 23 17:31:11 2024
Sweep [inc][295009]: completed
Sweep [inc2][295009]: completed

注意:ORA-04031显示的是 shared pool

2.2、根据alert定位到trc日志

Starting SQL statement dump
SQL Information
user_id=86 user_name=BASE module= action=
sql_id=gphw6gmd7bbur plan_hash_value=-498135542 problem_type=0
----- Current SQL Statement for this session (sql_id=gphw6gmd7bbur) -----
具体语句
sql_text_length=915
具体语句

注意:这里代替的是一个INSERT语句,但是涉及业务,就不放在这了。

select distinct sql_id, count(*), sql_text
from V$SQL_SHARED_MEMORY
where sql_text not like '%SQL Analyze%'
and sql_text not like '%SGA_DYNAMIC_COMPONENTS%'
group by sql_id, sql_text
order by 2 desc;

这里也可以用这个语句来确认一下,互相验证。

2.3查看trc日志的内存使用情况

==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"free memory "                   2295 MB 60%
"KGLH0 "                               336 MB 9%
"SQLA "                                 216 MB 6%
"KQR L PO "                          151 MB 4%
"KGLS "                                 142 MB 4%
"kglsim object batch "            80 MB 2%
"KGLHD "                                66 MB 2%
"FileOpenBlock "                    53 MB 1%
"kglsim heap "                        51 MB 1%
"private strands "                   48 MB 1%

-----------------------------------------
free memory                      2295 MB
memory alloc.                   1545 MB
Sub total                           3840 MB

==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"free memory "                               1932 MB 53%
"KGLH0 "                                           516 MB 14%
"SQLA "                                             403 MB 11%
"KGLS "                                              199 MB 5%
"KGLHD "                                             95 MB 3%
"KQR M PO "                                       67 MB 2%
"private strands "                                48 MB 1%
"event statistics per sess "                  42 MB 1%
"ksunfy : SSO free list "                       40 MB 1%
"dbktb: trace buffer "                          35 MB 1%

-----------------------------------------
free memory                                   1932 MB
memory alloc.                                 1716 MB
Sub total                                         3648 MB

TOTALS ---------------------------------------
Total free memory                          4227 MB
Total memory alloc.                        3261 MB
Grand total                                     7488 MB

注意:free memory这行里现实的空闲内存很足,足够接着使用,但是还是爆发了ORA-0403,这个很像是内存碎片。

SUB是为了提供并发能力,有两个SUB,每个SUB都还有2GB的空闲内存(Free memory)

2.4、查看分配摘要请求信息

Allocation Request Summary Informaton
=====================================
Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=01/23/2024 17:51:15
Dump Count=1

Allocation request for: modification
Heap: 0x6005bb20, size: 4160

size是bytes,大小是 4160 bytes

这个是需要分配 4160 bytes,数据库请求的内存空间。

2.5、查询内存关键词

Memory Utilization of Subpool 1 (SGA HEAP)
==============================================
Allocation Name Size

"modification " 607904

这里当时没懂啥意思,现在也没懂,好像是共享池子池的空闲大小。

2.6、AWR报告等待事件

负载极低的库却触发ORA-04031-23、问题解决

3.1、短期解决

alter system flush shared_pool;

或者重启数据库

如果中途Hang死,直接sqlplus登陆不进去
sqlplus -prelim / as sysdba
sql> shutdown immediate;
sql> startup;

3.2、长期解决

2.2.1、修改隐藏参数

alter system set "_enable_shared_pool_durations"=false scope=spfile;
alter system set "_shared_pool_reserved_min_alloc"=4000 scope=spfile;

注意:

_enable_shared_pool_duration:共享池Shared Pool只能扩展,不能再次被收缩。(怎么感觉只是把报错触发的时间延长了呢)

_shared_pool_reserved_min_alloc:控制保留池中最小的分配大小,默认大小为4400,其取值范围为4000bytes到60M之间(当然这是基于当前共享池大小)

SELECT a.ksppinm "Parameter",
b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/' and a.ksppinm in ('_shared_pool_reserved_min_alloc','_enable_shared_pool_durations');

这个是检查隐藏参数的具体信息

3.2.2、关闭ASMM

alter system set sga_target = 0 scope = both;
alter system set shared_pool_size = 待定 scope = both;
alter system set db_cache_size = 待定 scope = both;

注意:ASMM(Automatic Shared Memory Management,自动共享内存管理)

设置ASMM,他会自动扩展各个共享池所需的内存,不低于你所设置的共享池参数。

这个是创建的pfile文件,这行代表的是shared_pool_size当前正在用的内存大小 - 3G
orcl.__shared_pool_size=3321888768

这个代表的是你所设置的shared_pool_size内存参数大小 - 2G
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 2G

也可以查动态参数
select component,current_size/1024/1024/1024,min_size/1024/1024/1024,max_size/1024/1024/1024 from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE/1024/1024/1024 MIN_SIZE/1024/1024/1024 MAX_SIZE/1024/1024/1024
------------------------------ --------------------------- ----------------------- -----------------------
shared pool 3.09375 3.078125 3.09375
DEFAULT buffer cache 4.03125 4.03125 4.046875

这个是查询比对,设置参数和真实参数的比对

3.2.3资源扩容

扩容服务器资源顺带扩容sga、share pool、buffer cache之类的。

4、借鉴文档https://www.cnblogs.com/missyou-shiyh/p/13602213.html
https://mp.weixin.qq.com/s/8wHGzccLKAk9jSGemyufTQ
https://www.modb.pro/db/393664

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论