一个朋友遇到ora-04031问题。虽然这个错误是非常常见的，然而这里的case 也有点让人为之震惊！
一个朋友遇到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 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 280160
prti 0
sqlp 53608
sqlk 28336
gcs dynamic r 2556288
work area tab 236640
plwpil:wa 0
kebm run-once actions 0
grptab_kfgsg 0
aw sga 0
dbktb: trace buffer 53673984
v_swperrcount 0
ksunfy : sso free list 27834240
ksxp ipc state object 346448
ksdhng: cache history 0
dfw_config_capture 0
enqueue resources 862232
kzekm heap descriptor 0
kelt translation table 0
ips_package_unpack_histor 0
kglsim hash table bkts 2097152
ksxr global channels 0
ges enqueues 344980144
trace buf hdr xtend 2080
ksxr receive buffers 1061888
kglsim java timestamps 131072
vm osd context 0
kcb table scan buffer 4194816
knlp receive buffers 0
pls chunk 0
knlu_init_btree:init 0
kews aggregation objs 0
dde_user_action_def 0
keswxnotify:tabplans 131072
em_user_activity 0
ksxr large reply queue 168296
event-class map 0
ksq event description 0
ksxp conn stats buffer 728432
ksxp ctx stats buffer 578128
ksxp port stats buffer 142768
kglsim alloc latches 0
kglsim used list arr 0
kglsim free obj list 0
ktcn global operators 0
kestb existence bitvec 131072
kfd extent enqueue obj 42792
ksdhng: el wtr cache 401280
ksb cic process list 0
gcs shadows 130592800
value block f 43832
ksdhng: blkers cache 72960
fileopenblock 255011680
qmtb_init_xxta 0
sort segment handle 54936
latchnum to latch map 0
listen address entry 0
vips_pkg_file 0
px msg pool 47109312 +++++ 当前px msg pool已经分配了47m左右
。。。。。。
checkpoint queue 116765184
sim segment hits 72960
sim lru segments 72960
pesom.c:global 0
peshm.c:global 0
paralltp txn r 22476896
ash buffers 119537664
asm kffd so 0
statement queuing 0
v_actinc 0
message pool freequeue 523240
adr_control 0
vshowincb 0
vproblem2 0
pmon blockers 0
procs: ksunfy 6408000
sga listelement 0
primem_kfmdsg 0
agent name 0
system parameters 232944
online xxtafile move sess 0
sched job queue 0
vproblem 0
object queue hash buckets 8404992
object queue hash table d 229824
obj stats allocation chun 1290240
qmnsf_heap 0
sim cache nbufs 0
qmu subheap 53976
mttr advisory context 0
lgwr-network server info 34032
lgwr debug module memory 0
logout storm management 0
log_simultaneous_copies 49248
sys event stats 552960
parameter handle 4273688
parameter handle 0
name-service recovery 80776
channel handle 48360
parameter blocks 22016
qmrcinitsga:qmrcsga 0
parameter entry 0
asm map operations 30552
security class 33280
gcr 0
post/wait queues 55872
bloom filter 0
auto tune undo info 0
pesldt03_instance 33848
cp pool array 0
cp cmon array 0
undo info 0
log archive config contex 0
state objects 107440104
archlog dest array 22784
parallel kcbibr 0
archive_lag_target 0
poisoned memory 32768
name-service request 0
latch recovery structures 0
latches for protecting fs 0
latches for protecting ls 0
label cache heap descrptr 0
cp connection 0
sched job slv 0
bt_subentry 0
saved job ids 0
object level stat table 0
pso tbs: ksunfy 780000
dummy 0
qm_init_sga:qmdpsg 0
analytic workspace 0
sim trace buf 0
qm_init_sga:oidctx 0
sort segment 16852160
presentation entry 0
cp srv array 0
recov_kgqbtctx 26512
dbkea sga 0
pesom.c:latch array 0
banner storage 0
qmps connections 65280
quiescing session 0
qm_init_uga:qmsg 0
enqueue 0
buffer handles 5472000
process group array 69776
dml lock 7066304
property service so 0
broker globals 0
sga node map 0
prmtzdini tz region 801416
view 0
savepoints 0
monitor modification hash 0
resumable name 0
obj stats hash table 0
max allowable # log files 506400
os proc request holder 293424
message pool context area 22400
media recovery state obje 0
db_files 1783968
base-kglhdusr 1218016
constraints 0
asm scan context 0
px server msg stats 0
px server deq stats 0
cinfo_kfnsg 0
cco_subnode 0
cco_pubnode 0
kqr x po 50414728
kqr l po 5977688
parameter table block 31158248
kqr m so 192808
kqr l so 128104
kqr s so 90928
ksfv sga 0
ksir sga 0
asm rollback operations 0
kccsgehtt 0
kglsgivl2 28160
plugin xxtafile array 36016
plwxx:plw_str_new_rval 0
plwspv:plw_str_new_val 0
lm heartbeat 0
kgsk sga 0
branch table segmented ar 105288
ksv slave 0
rvwr post requested array 0
parameter value memory 0
rta bounxxry info thread 71808
rta read control file ind 0
k2q_lock 4606648
asm extent pointer array 318197080
。。。。。。。
kglsim object batch 338733696
ktlbk state objects 5417280
。。。。。
kglsim heap 197796864
ksz parent 204960
kgnfs authp 16464
event statistics per sess 29840640
event statistics ptr arra 36480
event list to post commit 0
kfg so child 0
kgkp randnum 40000
library cache dump