一个朋友遇到ORA-04031问题。虽然这个错误是非常常见的，然而这里的Case 也有点让人为之震惊！
Tue Aug 26 11:51:13 2014
Errors in file /oracle/app/oracle/diag/rdbms/xx/xx1/trace/xx1_p485_28873.trc (incident=1589637)
ORA-04031: 无法分配 32792 字节的共享内存 ("shared pool","unknown object","sga heap(2,0)","PX msg pool")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Aug 26 11:51:15 2014
。。。。。。。。
DDE: Problem Key 'ORA 4031' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
Tue Aug 26 11:57:12 2014
PMON failed to acquire latch, see PMON dump
Tue Aug 26 11:57:15 2014
DIAG (ospid: 14345) has not called a wait for 49 secs.
Tue Aug 26 11:57:58 2014
Errors in file /oracle/app/oracle/diag/rdbms/xx/xx1/trace/xx1_qmnc_15686.trc (incident=1608420)
ORA-00445: background process "q00T" did not start after 120 seconds
Tue Aug 26 11:58:17 2014
。。。。。。。。
Tue Aug 26 12:07:31 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Aug 26 12:07:31 2014
WARNING: inbound connection timed out (ORA-3136)
Tue Aug 26 12:07:46 2014
PMON failed to acquire latch, see PMON dump |
上述信息为Rac 节点1的信息，对于节点2，信息也基本类似，这里就不贴了。从日志来看，是p621出异常，根据这一点
我们可以判断为是并行进程出现了异常，接着从下面的ORA-04031错误也可以证实这一点。
ORA-04031: 无法分配 32792 字节的共享内存 (“shared pool”,”unknown object”,”sga heap(2,0)”,”PX msg pool”)
从这个错误来看，可以判断P621进程无法分别32792 byte的内存了。这部分内存是需要为Px msg pool进程分配。
这里首先说说PX msg pool是干什么的？
Px msg pool其实是用于Parallel 操作的。通常情况下这部分内存区域应该是从large pool中分配。然而很多业务系统中
由于设置不到，导致parallel 操作仍然使用shared pool的memory。这一点，我们稍后讨论。
接着我们看后面的日子发现PMON进程都无法获得latch了，然后这个时候系统已经不行了。 很明显这个问题的根源就是前面
的ORA-04031错误，这里我们需要分析下trace，来确认下，为什么进程会报错。
该trace文件（xx1_p485_28873.trc）的内容如下：
Allocation Request Summary Informaton
=====================================
Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=08/26/2014 11:51:12
Dump Count=1
Allocation request for: PX msg pool
Heap: c00000012fe3ae08, size: 32792
******************************************************
HEAP DUMP heap name="sga heap(2,0)" desc=c00000012fe3ae08
extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x10000000 heap=0000000000000000
fl2=0x20, nex=0000000000000000, dsxvers=1, dsxflg=0x0
dsx first ext=0x30000000
latch set 2 of 2
durations disabled for this heap
reserved granules for root 0 (granule size 268435456) |
可以看到，PX msg pool请求获得32792 byte的内存大小，从什么地方申请呢？ heap 2，也就是shared pool的第2个subpool。
但是却无法分配这么多，以至于报错，下面我们来看下subpool 2的实际情况：
Memory Utilization of Subpool 2
================================
Allocation Name Size
___________________________ ____________
"free memory " 1431685456 ++++整个subpool 2 还有1.4G free memory左右
"miscellaneous " 1680
"vem_user_actlog1 " 0
"SWEEPERR " 0
"File Space Usage " 112144
"trace_knlasg " 0
"ges big msg buffers " 56801208
"dsktab_kfgsg " 57128
"hot latch diagnostics " 0
"gcs res hash bucket " 4194304
"Wait event pointers " 0
"gcs res latch table " 0
"file state object " 4698096
"vips_package_size " 0
"SEQ S.O. " 1057920
。。。。
"gcs resources " 223317648
"gc lock state " 0
"kglsim alloc latch area " 0
"gcs affinity " 0
"gcs opaque in " 49248
"gcs pnp histo " 0
"ges resource " 605005184
"knlp free small rcv buf " 0
"SQLA " 2801 |