*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 136 bytes of shared memory ("sharedpool","select /*+ rule */
bucket_cn...","SQLA^337fc737","kccdef: qkxrMemAlloc")
Elapsed: 00:00:10.99
BYS@ bys3>show parameter shared_pool
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknown
object","KGLH0^92c529c4","kglHeapInitialize:temp")
警告日志中也可以看到大量的报错日志：
Mon Feb 03 20:20:44 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_mmon_10113.trc (incident=138413):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KGLH0^ca490471","kglHeapInitialize:temp")
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 /u01/diag/rdbms/bys3/bys3/trace/bys3_mmon_10113.trc (incident=138414):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KGLH0^ca490471","kglHeapInitialize:temp")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
[oracle@bys3 ~]$ tail -n 50 alert_bys3.log
Mon Feb 03 20:20:14 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_10679.trc (incident=134643):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KKSSP^37","kglseshtSegs")
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 /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_10679.trc:
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KKSSP^37","kglseshtSegs")
…………………………
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_cjq0_10175.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 528 bytes of shared memory ("sharedpool","PROPS$","PLDIA^191e0a8d","PAR.C:parapt:Page")
4031错误临时解决方案是：杀掉一部分会话或者flush shared_pool;
SYS@bys3>select sid,count(*) from v$open_cursor group by sid;
SID COUNT(*)
---------- ----------
30 7
1 2
28 1
13 3
31 4
14 2
35 3014
33 3
15 7
16 5
select sid,machine from v$session;
根据查出的机器来划分，KILL掉不太重要的会话
以SYS登陆，做flushshared_pool;
---不建议，刷新共享池后，所有的SQL语句都需要重新解析，对共享池中LATCH的争用，以及大量更解析会消耗大量CPU资源。
SYS@ bys3>alter system flush shared_pool;
System altered.
但是之后会面临大量正常SQL语句的第一次执行的硬解析。。
过后仍需要找出产生大量硬解析的SQL语句进行优化。