ORA-04031
--系统平台是：redhat 5.5--数据库平台是2个node rac：
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
--在node2上出现ora--04031出现，node1上正常；
--处理过程
1、开始出现ORA-04031时，因报出是shared pool，只调整了shared_pool从2G到4G；
2、这个值缓解一段时间，又开始报错，根据mos文档就调整了利用
ALTER SYSTEM SET _kghdsidx_count=3 SCOPE=SPFILE SID='gpsdb2';调整了subpool的个数，今天又出现ORA-04031，并且报错之后，利用sql*plus来正常连接上去后，查询视图时报了ORA-04031内存不够.
3、今天报错之后，调整了node2上的某些参数大小，重启了实例：
sga_max_size 17012097024 == > 20 GB
shared_pool_size 4294967296 ==> 7 GB
large_pool_size 16777216 = 16 M
java_pool_size 33554432 = 32 M
streams_pool_size 0 ==> 32 M
sga_target 17012097024 ==> 20GB
db_cache_size 12582912000 ==> 12GB
--最近的相关alert日志信息在位于附件
--更重要的是这个node2上没有应用，所有应用在node1上。
--这到底是什么原因导致？
Memory Utilization of Subpool 1
================================
Allocation Name Size
_________________________ __________
"free memory " 269689864
"miscellaneous " 0
"trace buffer " 425984
"ges big msg buffers " 14420008
"gcs res hash bucket " 786432
"gcs res latch table " 61440
"sql area " 669801184
"UNDO INFO HASH " 19520
"UNDO STAT INFO " 59904
"txncallback " 59616
"transaction " 460240
"branch " 199920
"sessions " 1016760
"KJCTS implicit batching s" 480
"KJCTS process batching st" 640
"row cache " 4245368
"KCB incremental ckpt entr" 512
"KCL instance cache transf" 131072
"temporary tabl " 0
没应用 的情况下一个subpool里的sql area用了600多M?
做几个AWR和ADDM我看看 服务是启在node1上，sql area的语句应该是通过remote listener
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
GPSDB.YTO.COM.CN
SQL> show parameter remote_l
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
remote_listener string LISTENERS_GPSDB
$ more listener.ora
# listener.ora.gps02 Network Configuration File: /opt/app/oracle/product/10.2.0 _1/network/admin st
ener.ora.gps02
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_GPS02 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/product/10.2.0 _1)
(PROGRAM = extproc)
)
)
LISTENER_GPS02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gps02-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.198.52)(PORT = 1521)(IP = FIRST))
)
)
shared sql area 2,469.91 2,505.75 1.45
shared free memory 3,239.87 3,196.08 -1.35
就AWR看 sql area也在增长 ，shared pool free memory也是在减少的
虽然负载是比较低
db_cache约为12g，在RAC中实际shared pool要为大的buffer cache有一些内存损耗的
这个案例 一种workaround你可以考虑定期在2节点flush shared pool
或者关掉"_enable_shared_pool_durations"也会有一些帮助
的确是这样.通过X$KSMSP查询
SQL> SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
2 TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k' "AVG SIZE"
3 FROM X$KSMSP GROUP BY ksmchcls;
CLASS NUM SIZ AVG SIZE
---------------- ---------- ---------- ------------------------
freeabl 916604 2399153368 2.56k==>整体趋势此值在增加
recr 321198 425995512 1.30k
R-freea 1573 3799912 2.36k
perm 116 926640104 7,801.05k
R-free 459 373180640 793.97k
R-perm 2 1674480 817.62k
free 86039 3503149224 39.76k==>整体趋势此值在减少
我做过flush shared pool过后 free值增加。
sql area在shared pool不足时，应该能释放内存，为什么?
“在RAC中实际shared pool要为大的buffer cache有一些内存损耗的”==>为什么有一些内存损耗？ 做过flush shared pool之后的效果
SQL> alter system flush shared_pool;
System altered.
SQL> SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
2 TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k' "AVG SIZE"
3 FROM X$KSMSP GROUP BY ksmchcls;
CLASS NUM SIZ AVG SIZE
---------------- ---------- ---------- ------------------------
recr 50338 70733392 1.37k
freeabl 59576 110067856 1.80k
R-freea 1389 2822504 1.98k
perm 116 926640104 7,801.05k
R-free 496 374158048 736.67k
R-perm 2 1674480 817.62k
free 67386 6147496856 89.09k==>此值明显上升 sql area在shared pool不足时，应该能释放其内存，为什么在node2不能自动释放，反而占着内存?
页:
[1]