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报告等待事件
3、问题解决
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之类的。
