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

1、问题信息

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;

-----------------------------------------
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

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

"modification " 607904