ORA-04031: unable to allocate 2048024 bytes of shared memory

数据库环境：AIX64+Oracle11.2.2
问题描述：ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

Thu Jul 25 15:01:41 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p046_23659006.trc  (incident=216835):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/diag/rdbms/wind/wind/incident/incdir_216835/wind_p046_23659006_i216835.trc
Thu Jul 25 15:01:41 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p009_24183044.trc  (incident=216539):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/diag/rdbms/wind/wind/incident/incdir_216539/wind_p009_24183044_i216539.trc
Thu Jul 25 15:01:41 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p024_22020402.trc  (incident=216675):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/diag/rdbms/wind/wind/incident/incdir_216675/wind_p024_22020402_i216675.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Jul 25 15:01:44 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p014_29360452.trc  (incident=216595):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/diag/rdbms/wind/wind/incident/incdir_216595/wind_p014_29360452_i216595.trc
Thu Jul 25 15:01:44 2013
Dumping diagnostic data in directory=[cdmp_20130725150144], requested by (instance=1, osid=23659006 (P046)), summary=[incident=216835].
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p024_22020402.trc  (incident=216676):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/diag/rdbms/wind/wind/incident/incdir_216676/wind_p024_22020402_i216676.trc
Thu Jul 25 15:01:44 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p001_25034806.trc  (incident=216339):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:01:45 2013
Sweep [inc][216835]: completed
Thu Jul 25 15:01:45 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p017_9437502.trc  (incident=216619):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Sweep [inc][216676]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sweep [inc][216675]: completed
Sweep [inc][216595]: completed
Sweep [inc][216539]: completed
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p017_9437502.trc  (incident=216620):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Sweep [inc][216339]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sweep [inc2][216835]: completed
Sweep [inc2][216675]: completed
Sweep [inc2][216539]: completed
Thu Jul 25 15:01:46 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p029_17629594.trc  (incident=216243):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p029_17629594.trc  (incident=216244):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:01:47 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p045_29491632.trc  (incident=216435):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p014_29360452.trc  (incident=216596):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:01:47 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p025_11403328.trc  (incident=216683):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:01:47 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p032_19202450.trc  (incident=216731):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:01:47 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p038_24313860.trc  (incident=216779):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:01:47 2013
Errors in file /oracle/diag/rdbms/wind/wind/trace/wind_p033_31719508.trc  (incident=216739):
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","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.
Thu Jul 25 15:02:45 2013
Sweep [inc][216779]: completed
Sweep [inc][216739]: completed
Sweep [inc][216731]: completed
Sweep [inc][216683]: completed
Sweep [inc][216620]: completed
Sweep [inc][216619]: completed
Sweep [inc][216596]: completed
Sweep [inc][216435]: completed
Sweep [inc][216244]: completed
Sweep [inc][216243]: completed
Sweep [inc2][216676]: completed
Sweep [inc2][216595]: completed

诊断分析几点：

1，trace日志查看，寻找触发ORA-04031的具体原因。

2，分析内存配置及视图。
show parameter sga
show parameter memory
show parameter pga
v$sgainfo
v$sga_resize_ops
v$memory_resize_ops

3，查看内存建议
v$sga_target_advice
v$memory_target_advice

4，获取出问题这个时间点前后的ASH，AWR，ADDM的报表进行比对分析。
一般通过这些报告可以发现是什么SQL语句导致，突出的等待事件，内存具体的变化数据，参考ADDM给出的建议，参考SGA建议，最后对SGA进行适当调整。