ORA-04031 caused by ParallelQuery

A friend encountered a problem with ORA-04031. Although this error is very common, the Case here is also a little shocking!

A friend encountered a problem with ORA-04031. Although this error is very common, the Case here is also a little shocking!

A friend encountered a problem with ORA-04031. Although this error is very common, the Case here is also a little shocking!

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: unable to allocate 32792 bytes of shared memory ("shared pool", "unknown object", "sga heap ()", "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

The above information is the information of Rac Node 1. For Node 2, the information is similar, so it will not be posted here. From the log, p621 has an exception.
We can judge that a parallel process has encountered an exception, and then we can confirm this from the following ORA-04031 error.
ORA-04031: unable to allocate 32792 bytes of shared memory ("shared pool", "unknown object", "sga heap ()", "PX msg pool ")

From this error, we can determine that P621 processes cannot have 32792 bytes of memory. This part of memory needs to be allocated to the Px msg pool process.

What is the PX msg pool?

Px msg pool is actually used for Parallel operations. Normally, this part of the memory area should be allocated from the large pool. However, in many business systems
If this parameter is not set, the parallel operation still uses the memory of the shared pool. We will discuss this later.

Next, we can see that the PMON processes cannot obtain latch, and the system will no longer work at this time. Obviously, the root cause of this problem is the previous one.
ORA-04031 error, here we need to analyze the trace to confirm why the process reports an error.

The content of this trace file (xx1_p485_28873.trc) is as follows:

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)

We can see that the PX msg pool request obtains the memory size of 32792 bytes. Where can I apply for it? Heap 2, that is, the 2nd subpools of the shared pool.

However, it cannot be allocated so much that an error is reported. Let's take a look at the actual situation of subpool 2:

Memory Utilization of Subpool 2
======================================
Allocation Name Size
_______________________________________
"Free memory" 1431685456 ++ the entire subpool 2 has around 1.4 GB 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
"GC 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 timestampss" 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
"Maid 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 ++ the current PX msg pool has been allocated around MB
......
"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: ksun fy" 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/waitqueues" 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
"Regiononed memory" 32768
"Name-service request" 0
"Latch recovery ures" 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 locks" 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 also" 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 parents" 204960
"Kgnfs authp" 16464
"Event statistics per server" 29840640
"Event statistics ptr arra" 36480
"Event list to post commit" 0
"Kfg so child" 0
"KGKP randnum" 40000
Library Cache Dump