ORA-04031错误导致宕机案例分析(二)
8a26c68, flag: (51) USR/- BSY/-/-/-/-/-
DID: 0001-000A-00000003, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 0, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
last wait for 'SGA: allocation forcing component growth' blocking sess=0x(nil) seq=51324 wait_time=10714 seconds since wait started=0
=0, =0, =0
Dumping Session Wait History
for 'SGA: allocation forcing component growth' count=1 wait_time=10714
=0, =0, =0
for 'SGA: allocation forcing component growth' count=1 wait_time=10512
=0, =0, =0
for 'latch: shared pool' count=1 wait_time=892
address=600e7320, number=d6, tries=0
for 'latch: shared pool' count=1 wait_time=28
address=600e7320, number=d6, tries=0
for 'latch: shared pool' count=1 wait_time=51
address=600e7320, number=d6, tries=0
for 'latch: shared pool' count=1 wait_time=114
address=600e7320, number=d6, tries=0
for 'latch: shared pool' count=1 wait_time=120
address=600e7320, number=d6, tries=0
for 'latch: library cache' count=1 wait_time=33
address=a3fa46e8, number=d7, tries=1
结合上面的一些分析，可以断定SGA的不合理设置导致shared pool的内存被全部耗尽，SGA被撑爆了。于是调整SGA的参数才是解决问题的正确对策。另外考虑到这个
数据库也正常运行了较长一段时间，也分析了一下awr、addm报告，发现
系统的硬解析相当严重。另外通过下面脚本观察了一段时间shared pool的变化，发现其收缩、增长较频繁。
?
?
SELECT start_time,
component,
oper_type,
oper_mode,
initial_size / 1024 / 1024 "INITIAL",
final_size / 1024 / 1024 "FINAL",
end_time
FROM v$sga_resize_ops
WHERE component IN ( 'DEFAULT buffer cache', 'shared pool' )
AND status = 'COMPLETE'
ORDER BY start_time,
component;
?
?
这个可以通过设置数据库参数SHARED_POOL_SIZE，保证SHARED_POOL_SIZE大小不会由于内存紧张而低于这个大小，另外可以设置SGA resize的时间间隔
?
ALTER SYSTEM SET “_memory_broker_stat_interval”=n SCOPE=SPFILE;
问题虽然解决了，但是真正需要反思的是为什么这个SGA_MAX_SIZE设置为1168M大小的事情！而且没有在巡检当中被发现。