如何解决ORA-04031错误

偏执的太偏执、 2022-05-30 05:40 432阅读 0赞

如何解决ORA-04031错误  
  
一）查找执行次数为一的语句  
select SQL\_FULLTEXT from v$sql where EXECUTIONS=1 order by sql\_text;  
此语句出来的结果按sql\_text排序  
如果某些sql没有共享的话  
在某一个区域你会发现有一堆的sql语句  
它执行一次而且它的静态部分是相同的动态部分不相同  
这时就能知道没有共享  
  
可以这么做  
SQL> spool 1.lst  
然后执行  
SQL> select SQL\_FULLTEXT from v$sql where EXECUTIONS=1 order by sql\_text;  
然后  
SQL> spool off  
  
1)spool命令  
SPOOL是SQLPLUS的命令，不是SQL语法里面的内容  
在sqlplus中用来保存或打印查询结果  
  
spool 1.lst  
表示将此后的命令及命令的输出结果保存到用户目录下的1.lst文件中  
spool off  
结束内容的输出  
举个小例子：  
SQL> spool example.lst  
SQL> set linesize 100  
SQL> spool off  
SQL> exit  
Disconnected from [Oracle][] Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
With the Partitioning, OLAP and Data Mining options  
\[oracle@redhat4 ~\]$ vi example.lst  
下面两行是spool保存到example.lst文件的内容  
SQL> set linesize 100  
SQL> spool off  
  
2)v$sql视图  
看一下查询用到的视图v$sql的结构  
SQL> DESC v$sql;  
可以发现  
SQL\_TEXT VARCHAR2(1000)  
SQL\_FULLTEXT CLOB  
SQL\_ID VARCHAR2(13)  
  
语句中使用的视图v$sql中有很多字段，目前只看这三个字段  
  
LOB (large object) datatypes(大数据类型)  
oracle中有四种大数据类型blob,clob,bfile,nclob  
  
SQL\_FULLTEXT的数据类型为CLOB  
CLOB: Character Large Object(字符型大数据类型)  
即字符型lob,可容纳单字节的字符,最长可以达到4GB，存贮在 [数据库][Link 1]中  
  
oracle中有多种方法来检索或操作lob数据，通常的处理方法是通过dbms\_lob包  
  
SQL\_FULLTEXT字段可以把sql语句所有内容保存起来  
是为了防止一些超大的sql语句使用一般数据类型保存时不够长度的情况。  
但使用select语句直接查询此字段时只显示了最前面的一部分字符(80个字符)，  
显示时内容被截断了，但实际内容没有被截断。  
简单查询时使用SQL\_TEXT字段1000个字符的显示，反而显示的内容多一些。  
  
3)看结果  
\[oracle@redhat4 ~\]$ ls  
1.lst Desktop  
\[oracle@redhat4 ~\]$ vi 1.lst  
查看命令及结果被存到了1.lst文件中  
  
可以把这个文件放到windows [系统][Link 2]里面  
放到excel里面排一下序，更好看一些。  
  
如何从系统里面找一些哪些sql语句没有共享  
查询执行次数为一的语句是一个小技巧是很好的一个方法  
  
二）命中率  
就是librarycache命中率和rowcache命中率  
  
命中率反应的是软解析成功的次数  
严格要求99%以上  
甚至接近100%  
如果是98%就说明命中率比较差了。  
  
应该在数据库跑了一段时间以后去判断命中率  
库刚起来就看命中率，那时刚刚执行，命中率肯定低  
跑了一段时间以后命中率肯定是很高的。  
  
软解析的命中率  
SQL> select sum(pinhits)/sum(pins)\*100 from v$librarycache;  
  
SUM(PINHITS)/SUM(PINS)\*100  
\--------------------------  
90.5713553  
  
本人的演示的数据库没有什么负载而且跑得比较少  
所以命中率比较低。  
  
rowcache的命中率  
SQL> select sum(gets),sum(getmisses),100\*sum(gets-getmisses)/sum(gets) from v$rowcache where gets>0;  
  
SUM(GETS) SUM(GETMISSES) 100\*SUM(GETS-GETMISSES)/SUM(GETS)  
\---------- -------------- ---------------------------------  
1234298 130366 89.4380449  
  
一般rowcache的命中率很高的  
因为实验环境没有运行多少时间，不能真实反应实际情况  
  
rowcache一般都非常高，很少出问题  
容易出问题的是librarycache  
  
三）如何解决oracle的4031错误  
  
简单的讲一下如何解决oracle的4031错误  
  
1）alter system flush shared\_pool;  
临时性的解决办法，执行上面语句  
这时sharedpool的librarycache里面所有的chunk会释放  
会有大量的大大小小的chunk回到free里面去  
这时4031错误暂时会缓解  
但这只是治标不治本的一个办法  
  
2）共享SQL  
最好还是要判断一下哪些语句没有共享sql  
然后把这个问题告诉开发人员  
让开发人员去解决这个问题，让他共享sql  
  
共享sql如果开发人员做不到的话  
我们可以改一个参数cursor\_sharing  
  
sql语句没有共享有很多种原因  
第一种原因是因为里面有字面值  
没有使用绑定变量  
第二种我们的sql语句里面加了空格、大小写、回车  
造成书写不规范  
  
对于第一种情况如果是字面值没有使用绑定变量  
把cursor\_sharing改成force以后主动可以解决这个问题  
就是你如果使用字面值oracle会强行绑定变量  
对于第二种改cursor\_sharing对书写不规范这个问题是没法解决的  
尽量的让开发人员去改，改不了的话  
我们把cursor\_sharing 改成force  
  
3）把执行计划keep起来  
oracle数据库中有一个包dbms\_shared\_pool  
它可以实现keep功能  
  
大量的硬解析出现，产生很多很小的free trunk  
接着出现一个比较大的sql  
这时候在sharedpool里面的free里面就找不到合适的truck使用  
于是出现了4031错误。  
  
也有可能数据库正常运行  
free少了是sharedpool分的比较小  
某个大sql产生4031不是因为硬解析过多造成的  
而确实是这个sql太大了  
这时可以使用dbms\_shared\_pool包里面的存储过程  
将这个sql语句强行缓存到sql里面去  
然后它永远不会被置换出来  
它不会因为sharedpool空间不够了被挤出来  
这时也可以避免一个大的sql语句  
在后面执行时出现问题  
  
举例讲  
数据库里面容易出现这种情况  
当oracle在free里面找不到大truck的时候  
它会想办法到librarycache里面找大truck  
找到以后把它释放了，把空间要回来  
如数据库里面librarycache里面有一个很大的truck长时间没有执行  
它被置换出去了到free里面去了  
被分为两半使用了  
如果后面在执行这个大truck的时候可能就没有空间了  
这时候就容易出现4031错误  
为了解决这个问题  
可以将最大的前几个truck（我们可以排序）  
强行keep起来  
避免4031错误出现  
  
要使用dbms\_shared\_pool包首先要创建它  
方法是执行ORACLE\_HOME目录下的/rdbms/admin/dbmspool.sql文件  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
SQL> @?/rdbms/admin/dbmspool.sql  
  
Package created.  
  
Grant succeeded.  
  
View created.  
  
Package body created.  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
包创建完成  
此包的创建只能以管理员身份运行才能正常完成。  
  
然后使用  
select \* from v$db\_object\_cache where sharable\_mem > 10000  
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')  
and kept = 'NO';  
查一下sql里面有哪些比较大的  
sharable\_mem > 10000  
占用sharedmemeory内存大于10K的哪些对象  
然后把对象的拥有者和名字找出来  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
SQL> select \* from v$db\_object\_cache where sharable\_mem > 10000  
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')  
and kept = 'NO'; 2 3  
  
OWNER  
\----------------------------------------------------------------  
NAME  
\----------------------------------------------------------------------------------------------------  
DB\_LINK NAMESPACE  
\---------------------------------------------------------------- ----------------------------  
TYPE SHARABLE\_MEM LOADS EXECUTIONS LOCKS PINS KEP  
\---------------------------- ------------ ---------- ---------- ---------- ---------- ---  
CHILD\_LATCH INVALIDATIONS  
\----------- -------------  
SYSMAN  
EMD\_COLLECTION  
BODY  
PACKAGE BODY 33217 1 5 0 0 NO  
3 0  
  
SYS  
DBMS\_APPLICATION\_INFO  
TABLE/PROCEDURE  
PACKAGE 16745 2 0 4 0 NO  
3 0  
  
SYSMAN  
EM\_PING  
TABLE/PROCEDURE  
PACKAGE 37238 2 0 2 0 NO  
2 0  
  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
结果较多就不全列了  
  
如其中的一行  
SYS  
DBMS\_APPLICATION\_INFO  
TABLE/PROCEDURE  
PACKAGE 16745 2 0 4 0 NO  
3 0  
包名DBMS\_APPLICATION\_INFO  
拥有者SYS  
KEP状态为NO，即没有被keep  
  
把它keep到内存里面去  
需要使用dbms\_shared\_pool.keep('对象名');  
  
包的执行可以使用如下形式  
SQL> execute dbms\_shared\_pool.keep('DBMS\_APPLICATION\_INFO');  
或  
SQL> begin  
dbms\_shared\_pool.keep('DBMS\_APPLICATION\_INFO');  
commit;  
end;  
  
执行一下  
SQL> execute dbms\_shared\_pool.keep('DBMS\_APPLICATION\_INFO');  
  
PL/SQL procedure successfully completed.  
  
查询一下一keep的对象  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
SQL> select \* from v$db\_object\_cache where sharable\_mem > 10000  
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')  
and kept = 'YES'; 2 3  
  
OWNER  
\----------------------------------------------------------------  
NAME  
\----------------------------------------------------------------------------------------------------  
DB\_LINK NAMESPACE  
\---------------------------------------------------------------- ----------------------------  
TYPE SHARABLE\_MEM LOADS EXECUTIONS LOCKS PINS KEP  
\---------------------------- ------------ ---------- ---------- ---------- ---------- ---  
CHILD\_LATCH INVALIDATIONS  
\----------- -------------  
SYS  
DBMS\_APPLICATION\_INFO  
TABLE/PROCEDURE  
PACKAGE 16745 2 0 6 0 YES  
3 0  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
我的结果只有一条就是上面keep的DBMS\_APPLICATION\_INFO对象  
因为以前没有做过keep工作  
  
在dbms\_shared\_pool包执行时默认操作的对象的所有者是SYS  
属于其它所有者的对象默认操作会报错  
如这个对象  
SYSMAN  
EMD\_COLLECTION  
BODY  
PACKAGE BODY 33217 1 5 0 0 NO  
3 0  
拥有者SYSMAN  
对象名EMD\_COLLECTION  
如这样执行  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
SQL> execute dbms\_shared\_pool.keep('EMD\_COLLECTION');  
BEGIN dbms\_shared\_pool.keep('EMD\_COLLECTION'); END;  
  
\*  
ERROR at line 1:  
ORA-06564: object EMD\_COLLECTION does not exist  
ORA-06512: at "SYS.DBMS\_UTILITY", line 114  
ORA-06512: at "SYS.DBMS\_SHARED\_POOL", line 45  
ORA-06512: at "SYS.DBMS\_SHARED\_POOL", line 53  
ORA-06512: at line 1  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
结果报错  
解决办法要在对象名前面标示拥有者  
SQL> execute dbms\_shared\_pool.keep('SYSMAN.EMD\_COLLECTION');  
  
PL/SQL procedure successfully completed.  
执行成功  
  
解除某个对象的keep状态可以使用dbms\_shared\_pool包提供的unkeep方法  
SQL> execute dbms\_shared\_pool.unkeep('DBMS\_APPLICATION\_INFO');  
  
PL/SQL procedure successfully completed.  
和  
SQL> execute dbms\_shared\_pool.unkeep('SYSMAN.EMD\_COLLECTION');  
  
PL/SQL procedure successfully completed.  
  
执行清理shared\_pool内存  
alter system flush shared\_pool;  
命令后keep状态的对象仍然保持keep状态  
而在oralce数据库重启后不再有处于keep状态的对象  
  
4）如何增加sharedpool  
使用命令  
alter system set shared\_pool\_size=150M scope=both;  
150M就是要设置的值  
  
1、sga\_target和sga\_max\_size的设置及关系  
  
oracle以前的版本10以前  
数据库里面重点有6个大的池子  
在oracle老的版本里面每个池子需要给它供应大小  
sharedpool，buffer cache,redolog buffer，stream，large，javapool  
都要固定的大小，一旦固定大小它就不能变  
当然我们可以改  
  
但是有可能这种情况  
sharedpool设了两个G大小但实际用了一个G  
但是buffercache设了九个G它可能需要9.5个G  
oralce里面有空闲空间，  
但2G给了sharedpool，还浪费着呢，可buffercache还不够  
  
所以oracle10开始做这么一件事情  
oracle对SGA来讲  
它统一设一个参数SGA target  
比如设sga\_target等于12个G  
这个时候oracle的  
sga\_target里面包括sharedpool和buffercache那六个池子  
把参数统一设了12个G以后  
oracle对各个内存块六个池子oracle根据需求动态的去分配  
我们从理论上看上去应该很好啊  
不浪费空间  
所以说oracle新的版本里面我们只设一个参数sga\_target  
这是个动态参数  
在数据库运行期间我们可以动态去设置  
SGA里面的六个池子空间可以动态分配  
即保证了空间又没有浪费了空间  
  
还有个参数sga\_max\_size  
是个静态参数  
一般的情况下  
sga\_target等于sga\_max\_size  
但sga\_max\_size改完以后数据库需要重启  
sga\_max\_size是用来约束sga\_target的  
  
举一个假设的例子：  
数据库使用物理内存比如64G  
我们给了oracle数据库50%，也就是给了SGA\_target 32G  
我们想这么做，也给了  
max参数32G  
target参数也是32G  
这时假设没有max这个东西  
target是32G  
我们知道sga\_target可以动态改  
想把它改成36个G  
结果多写了一个0，把它改成360个G了  
这有可能，因为是动态参数  
出现一个问题内存一共才64G  
这时oracle要360个G  
一下就会把内存所有都给了oracle  
而且swap空间也给了oracle  
这时系统会瞬间因为内存耗尽，操作系统挂起  
操作系统挂起了oracle也就挂起了  
会死机、导致数据文件损坏  
所以sga\_target可以动态设  
但是一旦设错了数据库可能会引起死机  
出现问题  
  
所以需要另外设一个参数  
oracle提供了一个参数max参数  
平时  
物理内存64G  
可以把max参数设为48G  
因为规定oracle target参数再怎么设置高  
target只能在max的48个G以下设置  
设40G或42G都没问题但不要超过max  
  
max它是静态参数要改必须重启数据库  
存在的目的就是为了约束设sga\_target时的随意性  
max设了个sga\_target可用的最大值  
  
现在看一下当前这两个参数的值  
SQL> show parameter sga  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
lock\_sga boolean FALSE  
pre\_page\_sga boolean FALSE  
sga\_max\_size big integer 272M  
sga\_target big integer 272M  
目前sga\_target和sga\_max\_size大小为272M  
  
2、shared\_pool大小的设置  
  
先看一下目前大小  
SQL> show parameter shared\_pool;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
shared\_pool\_reserved\_size big integer 8M  
shared\_pool\_size big integer 0  
  
shared\_pool\_size的默认值oracle设的是0M  
就是未指定由oracle自动分配大小  
  
给它分配个值  
SQL> alter system set shared\_pool\_size=20;  
  
System altered.  
  
再看大小  
SQL> show parameter shared\_pool;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
shared\_pool\_reserved\_size big integer 8M  
shared\_pool\_size big integer 4M  
  
这里设置时shared\_pool\_size=20，20后面没有带参数，20被理解为20个字节  
因为此参数设置时以4M为基本单位，  
最终的值都是4M的整数倍，所以系统给了它一个值4M。  
  
再去查一个参数  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
SQL> select COMPONENT,CURRENT\_SIZE from V$SGA\_DYNAMIC\_COMPONENTS;  
  
COMPONENT CURRENT\_SIZE  
\---------------------------------------------------------------- ------------  
shared pool 88080384  
large pool 4194304  
java pool 4194304  
streams pool 0  
DEFAULT buffer cache 180355072  
KEEP buffer cache 0  
RECYCLE buffer cache 0  
DEFAULT 2K buffer cache 0  
DEFAULT 4K buffer cache 0  
DEFAULT 8K buffer cache 0  
DEFAULT 16K buffer cache 0  
DEFAULT 32K buffer cache 0  
ASM Buffer Cache 0  
  
13 rows selected.  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
实际sharedpool的大小不是4M是88M  
参数值改成了4M实际大小88M  
并没有变化  
  
我们可以设sga\_target 1G或500M  
目前我使用的是272M  
然后orale根据  
sga\_target设置大小、shared\_pool\_size设置大小、系统的负载  
自动的把sharedpool设为88M  
我们也可以手工的把sharedpool设一下  
如果小于当前值88M，oracle不予理会还是88M  
  
如果这时shared\_pool\_size设为180M  
oracle就会用180M  
原理  
新设置的参数大小必须大于先前sga\_target总的自动给它分配的空间大小  
才会使shared pool实际的空间大小发生改变  
所以show parameter看的并不准  
需要查询  
  
这就是讲的增加sharedpool空间  
新设置的参数必须大于sga\_target目前总的自动给它分配的空间  
才能在设置新的大小值后立即使实际值增加  
  
例子：  
SQL> alter system set shared\_pool\_size=100M;  
  
System altered.  
  
参数设置值  
SQL> show parameter shared\_pool;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
shared\_pool\_reserved\_size big integer 8M  
shared\_pool\_size big integer 100M  
  
实际大小  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
SQL> select COMPONENT,CURRENT\_SIZE from V$SGA\_DYNAMIC\_COMPONENTS;  
  
COMPONENT CURRENT\_SIZE  
\---------------------------------------------------------------- ------------  
shared pool 104857600  
large pool 4194304  
java pool 4194304  
streams pool 0  
DEFAULT buffer cache 163577856  
KEEP buffer cache 0  
RECYCLE buffer cache 0  
DEFAULT 2K buffer cache 0  
DEFAULT 4K buffer cache 0  
DEFAULT 8K buffer cache 0  
DEFAULT 16K buffer cache 0  
DEFAULT 32K buffer cache 0  
ASM Buffer Cache 0  
  
13 rows selected.  
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！  
这时候shared pool的实际空间发生了变化，  
增大了，值为104M左右。  
  
即使  
alter system flush shared\_pool;  
此值也是sga自动分配给它的值，并且自动分配给它的值肯定大于自己设置的值。  
  
5）保留区  
  
我们知道shared\_pool里面有free、librarycache、rowcache  
  
随着硬解析的增加  
free里面的chunk很多变小将来可能出现4031错误  
我们也知道不管硬解析多少，随着时间的增长  
free里面都有可能出现空间不够的情况因为大量的小的chunk  
都有可能会出现数据库跑了一段时间以后出现一个大的sql  
  
有时候4031错误看上去不能避免  
  
oracle为了解决这个问题做了另一个事情  
  
shared\_pool里面单独的划出一块空间来，  
保留区  
叫shared\_pool\_reserved  
这个空间，就是只是用来缓存大对象  
当一个对象的尺寸超过一定的阈值的时候  
它就不会到free里面去找空间  
而是到保留区里面找空间  
如果我们把保留区设的足够大的话  
可以减少很多4031错误的产生  
  
我们看一个查询  
select REQUEST\_MISSES from v$shared\_pool\_reserved;  
就是用来查在保留区里请求空间失败的次数  
只要是有一次就肯定会发生4031错误  
因为既然到保留区里面找空间  
说明是大对象  
在保留区都找不到的话，它就会直接报错，报4031错误  
所以这个数值最好是零  
  
SQL> select REQUEST\_MISSES from v$shared\_pool\_reserved;  
  
REQUEST\_MISSES  
\--------------  
0  
值为0说明从来没有发生因为在保留区找不到空间而产生4031错误的情况。  
  
所以保留区我们要设的大一些。  
如果查询值大于0，我们要调整参数shared\_pool\_reserved\_size  
  
SQL> show parameter shared;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
hi\_shared\_memory\_address integer 0  
max\_shared\_servers integer  
shared\_memory\_address integer 0  
shared\_pool\_reserved\_size big integer 8M  
shared\_pool\_size big integer 4M  
shared\_server\_sessions integer  
shared\_servers integer 1  
  
其中  
shared\_pool\_reserved\_size big integer 8M  
  
8M即为值大小  
  
如果REQUEST\_MISSES大于0的话  
或者数值比较大的话  
我们要将reserved调大一些  
  
shared\_pool\_reserved\_size是一个静态参数所以修改要使用  
  
SQL> alter system set shared\_pool\_reserved\_size=10M scope=spfile;  
  
System altered.  
  
值修改后重启oracle才能生效。  
  
系统重启然后查询参数值：  
SQL> show parameter shared\_pool;  
  
NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
shared\_pool\_reserved\_size big integer 10M  
shared\_pool\_size big integer 4M  
  
设置保留区这也是4031错误的一个解决办法  
  
四）解决oracle的4031错误方法总结  
1、alter system flush shared\_pool;  
2、共享SQL  
3、select \* from v$db\_object\_cache where sharable\_mem > 10000  
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')  
and kept = 'NO';  
执行dbms\_shared\_pool.keep('对象名');  
DBMS\_SHARED\_POOL  
@?/rdbms/admin/dbmspool.sql  
4、保留区  
select REQUEST\_MISSES from v$shared\_pool\_reserved;  
5、增加shared pool空间  
select COMPONENT,CURRENT\_SIZE from V$SGA\_DYNAMIC\_COMPONENTS;  
show parameter sga\_target  
show parameter sga\_max\_size  
alter system set shared\_pool\_size=150M scope=both;  
上面的方法摘自老师的教案  
简单回顾一下  
1、flush一下 治标不治本  
2、共享sql 最好使用绑定变量  
绑定变量实现不了的话  
可以改cursor\_sharing只能解决一种办法，就是字面值没有使用绑定变量的问题。  
3、我们可以对一些大的对象进行keep  
4、单独的划出一块保留区

5、增加shared\_pool的大小

==================

上次测试同事发现系统报ORA-04031 错误，特意上网查了下，虽看不懂，但还是记下来，以备万一我水平提上去能看懂时，也可以回顾回顾。

**诊断并解决ORA-04031 错误**  
        当我们在共享池中试图分配大片的连续内存失败的时候，Oracle首先清除池中当前没使用的所有对象，使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031 错误。

当这个错误出现的时候你得到的错误解释信息类似如下：

Text代码   ![收藏代码][icon_star.png]

1.  04031, 00000, "unable to allocate %s bytes of shared memory (\\"%s\\",\\"%s\\",\\"%s\\",\\"%s\\")"   
2.  // \*Cause: More shared memory is needed than was allocated in the shared   
3.  // pool.   
4.  // \*Action: If the shared pool is out of memory, either use the   
5.  // dbms\_shared\_pool package to pin large packages,   
6.  // reduce your use of shared memory, or increase the amount of   
7.  // available shared memory by increasing the value of the   
8.  // INIT.ORA parameters "shared\_pool\_reserved\_size" and   
9.  // "shared\_pool\_size".   
10. // If the large pool is out of memory, increase the INIT.ORA   
11. // parameter "large\_pool\_size".

**1.共享池相关的实例参数**  
        在继续之前，有必要理解下面的实例参数:

**  **      a.SHARED\_POOL\_SIZE   
        这个参数指定了共享池的大小，单位是字节。可以接受数字值或者数字后面跟上后缀"K" 或 "M" 。"K"代表千字节, "M"代表兆字节。   
        b.SHARED\_POOL\_RESERVED\_SIZE   
        指定了为共享池内存保留的用于大的连续请求的共享池空间。当共享池碎片强制使 Oracle 查找并释放大块未使用的池来满足当前的请求的时候，这个参数和SHARED\_POOL\_RESERVED\_MIN\_ALLOC 参数一起可以用来避免性能下降。   
        c.这个参数理想的值应该大到足以满足任何对保留列表中内存的请求扫描而无需从共享池中刷新对象。既然操作系统内存可以限制共享池的大小，一般来说，你应该设定这个参数为 SHARED\_POOL\_SIZE 参数的 10% 大小。   
        d.SHARED\_POOL\_RESERVED\_MIN\_ALLOC 这个参数的值控制保留内存的分配。如果一个足够尺寸的大块内存在共享池空闲列表中没能找到，内存就从保留列表中分配一块比这个值大的空间。默认的值对于大多数系统来说都足够了。如果你加大这个值，那么Oracle 服务器将允许从这个保留列表中更少的分配并且将从共享池列表中请求更多的内存。这个参数在Oracle 8i 和更高的版本中是隐藏的。提交如下的语句查找这个参数值：

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT**   nam.ksppinm **NAME**, val.ksppstvl VALUE  
2.      **FROM** x$ksppi nam, x$ksppsv val  
3.     **WHERE** nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'  
4.  **ORDER** **BY** 1;

运行结果：  
![f5342356-2618-3053-b5a9-f86da4c98c3c.png][]

10g 注释：Oracle 10g 的一个新特性叫做 "自动内存管理" 允许DBA保留一个共享内存池来分shared pool,buffer cache, java pool 和large pool。一般来说，当数据库需要分配一个大的对象到共享池中并且不能找到连续的可用空间，将自动使用其他SGA结构的空闲空间来增加共享池的大小 。既然空间分配是Oracle自动管理的，ora-4031出错的可能性将大大降低。自动内存管理在初始化参数SGA\_TARGET大于0的时候被激活。当前设定可以通过查询v$sga\_dynamic\_components 视图获得。请参考10g管理手册以得到更多内容。

** [伦理电影][Link 3] http://www.dotdy.com/**

**2.诊断ORA-04031 错误 **  
        注：大多数的常见的 ORA-4031 的产生都和 SHARED POOL SIZE 有关，这篇文章中的诊断步骤大多都是关于共享池的。　对于其它方面如Large\_pool或是Java\_pool，内存分配算法都是相似的，一般来说都是因为结构不够大造成。

ORA-04031 可能是因为 SHARED POOL 不够大，或是因为碎片问题导致数据库不能找到足够大的内存块。

ORA-04031 错误通常是因为库高速缓冲中或共享池保留空间中的碎片。 在加大共享池大小的时 候考虑调整应用,使用共享的SQL 并且调整如下的参数：   
        SHARED\_POOL\_SIZE,  
        SHARED\_POOL\_RESERVED\_SIZE,  
        SHARED\_POOL\_RESERVED\_MIN\_ALLOC  
        首先判定是否ORA-04031 错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查询：

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT** free\_space, avg\_free\_size,used\_space, avg\_used\_size, request\_failures,  
2.         last\_failure\_size  
3.    **FROM** v$shared\_pool\_reserved;

如果：  
        REQUEST\_FAILURES > 0 并且 LAST\_FAILURE\_SIZE > SHARED\_POOL\_RESERVED\_MIN\_ALLOC   
那么ORA-04031 错误就是因为共享池保留空间缺少连续空间所致。要解决这个问题,可以考虑加大SHARED\_POOL\_RESERVED\_MIN\_ALLOC 来降低缓冲进共 享池保留空间的对象数目，并增大 SHARED\_POOL\_RESERVED\_SIZE 和 SHARED\_POOL\_SIZE 来加大共享池保留空间的可用内存。

如果：   
        REQUEST\_FAILURES > 0 并且 LAST\_FAILURE\_SIZE < SHARED\_POOL\_RESERVED\_MIN\_ALLOC   
        或者   
        REQUEST\_FAILURES 等于0 并且 LAST\_FAILURE\_SIZE < SHARED\_POOL\_RESERVED\_MIN\_ALLOC   
        那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。

第一步应该考虑降低SHARED\_POOL\_RESERVED\_MIN\_ALLOC 以放入更多的对象到共享池保留空间中并且加大SHARED\_POOL\_SIZE。

**3.解决ORA-04031 错误  
1).ORACLE BUG **  
        Oracle推荐对你的系统打上最新的PatchSet。大多数的ORA-04031错误都和BUG 相关，可以通过使用这些补丁来避免。

下面表中总结和和这个错误相关的最常见的BUG、可能的环境和修补这个问题的补丁。   
![点击查看原始大小图片][3e4378db-afac-3041-bd11-bb0940489703.png]  
**2).编译Java代码时出现的ORA-4031 **  
        在你编译Java代码的时候如果内存溢出，你会看到错误：

Text代码   ![收藏代码][icon_star.png]

1.  A SQL exception occurred while compiling: :   
2.  ORA-04031: unable to allocate bytes of shared memory   
3.  ("shared pool","unknown object","joxlod: init h", "JOX: ioc\_allocate\_pal")

解决办法是关闭数据库然后把参数 JAVA\_POOL\_SIZE 设定为一个较大的值。这里错误信息中提到的 "shared pool" 其实共享全局区(SGA)溢出的误导，并不表示你需要增加SHARED\_POOL\_SIZE，相反，你必须加大 JAVA\_POOL\_SIZE 参数的值，然后重启动系统，再试一下。参考: <Bug:2736601> 。

**3).小的共享池尺寸 **  
        很多情况下，共享池过小能够导致ORA-04031错误。下面信息有助于你调整共享池大小：

**        a.库高速缓冲命中率 **  
        命中率有助于你衡量共享池的使用，有多少语句需要被解析而不是重用。下面的SQL语句有助于你计算库高速缓冲的命中率：

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT** SUM(PINS) "EXECUTIONS",   
2.              SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"   
3.              **FROM** V$LIBRARYCACHE;

如果丢失超过1%,那么尝试通过加大共享池的大小来减少库高速缓冲丢失。

**        b.共享池大小计算 **  
        要计算最适合你工作负载的共享池大小，请参考：

**4).共享池碎片 **  
        每一次，需要被执行的SQL 或者PL/SQL 语句的解析形式载入共享池中都需要一块特定的连续的空间。数据库要扫描的第一个资源就是共享池中的空闲可用内存。一旦空闲内存耗尽，数据库要查找一块已经分配但还没使用的内存准备重用。如果这样的确切尺寸的大块内存不可用，就继续按照如下标准寻找：

i.大块(chunk)大小比请求的大小大  
        ii.空间是连续的  
        iii.大块内存是可用的(而不是正在使用的)  
        这样大块的内存被分开，剩余的添加到相应的空闲空间列表中。当数据库以这种方式操作一段时间之后，共享池结构就会出现碎片。

当共享池存在碎片的问题,分配一片空闲的空间就会花费更多的时间,数据库性能也会下降(整个操作的过程中，"chunk allocation"被一个叫做"shared pool latch" 的闩所控制) 或者是出现 ORA-04031 错误errors (在数据库不能找到一个连续的空闲内存块的时候)。

如果SHARED\_POOL\_SIZE 足够大，大多数的 ORA-04031 错误都是由共享池中的动态SQL 碎片导致的。可能的原因如下：   
        i.非共享的SQL   
        ii.生成不必要的解析调用 (软解析)  
        iii.没有使用绑定变量  
        要减少碎片的产生你需要确定是前面描叙的几种可能的因素。可以采取如下的一些方法，当然不只局限于这几种: 应用调整、数据库调整或者实例参数调整。

下面的视图有助于你标明共享池中非共享的SQL/PLSQL：   
**        a.V$SQLAREA 视图**   
        这个视图保存了在数据库中执行的SQL 语句和PL/SQL 块的信息。下面的SQL 语句可以显示给你带有literal 的语句或者是带有绑定变量的语句：

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT**   SUBSTR (sql\_text, 1, 40) "SQL", COUNT (\*),  
2.           SUM (executions) "TotExecs"  
3.      **FROM** v$sqlarea  
4.     **WHERE** executions < 5  
5.  **GROUP** **BY** SUBSTR (sql\_text, 1, 40)  
6.    **HAVING** COUNT (\*) > 30  
7.  **ORDER** **BY** 2;

注: Having 后的数值 "30" 可以根据需要调整以得到更为详细的信息。

**        b.X$KSMLRU 视图**   
        这个固定表x$ksmlru 跟踪共享池中导致其它对象换出(age out)的应用。这个固定表可以用来标记是什么导致了大的应用。

如果很多对象在共享池中都被阶段性的刷新可能导致响应时间问题并且有可能在对象重载入共享池中的时候导致库高速缓冲闩竞争问题。

关于这个x$ksmlru 表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。监视这个固定表运行如下操作：

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT** \* **FROM** X$KSMLRU **WHERE** ksmlrsiz > 0;

这个表只可以用SYS用户登录进行查询。

**        c.X$KSMSP 视图 (类似堆Heapdump信息) **  
        使用这个视图能找出当前分配的空闲空间，有助于理解共享池碎片的程度。如我们在前面的描述，查找为游标分配的足够的大块内存的第一个地方是空闲列表( free list)。 下面的语句显示了空闲列表中的大块内存：

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT**   '0 (<140)' bucket, ksmchcls, 10 \* TRUNC (ksmchsiz / 10) "From",  
2.           COUNT (\*) "Count", **MAX** (ksmchsiz) "Biggest",  
3.           TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"  
4.      **FROM** x$ksmsp  
5.     **WHERE** ksmchsiz < 140 AND ksmchcls = 'free'  
6.  **GROUP** **BY** ksmchcls, 10 \* TRUNC (ksmchsiz / 10)  
7.  **UNION** ALL  
8.  **SELECT**   '1 (140-267)' bucket, ksmchcls, 20 \* TRUNC (ksmchsiz / 20),  
9.           COUNT (\*), **MAX** (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
10.          TRUNC (SUM (ksmchsiz)) "Total"  
11.     **FROM** x$ksmsp  
12.    **WHERE** ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'  
13. **GROUP** **BY** ksmchcls, 20 \* TRUNC (ksmchsiz / 20)  
14. **UNION** ALL  
15. **SELECT**   '2 (268-523)' bucket, ksmchcls, 50 \* TRUNC (ksmchsiz / 50),  
16.          COUNT (\*), **MAX** (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
17.          TRUNC (SUM (ksmchsiz)) "Total"  
18.     **FROM** x$ksmsp  
19.    **WHERE** ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'  
20. **GROUP** **BY** ksmchcls, 50 \* TRUNC (ksmchsiz / 50)  
21. **UNION** ALL  
22. **SELECT**   '3-5 (524-4107)' bucket, ksmchcls, 500 \* TRUNC (ksmchsiz / 500),  
23.          COUNT (\*), **MAX** (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
24.          TRUNC (SUM (ksmchsiz)) "Total"  
25.     **FROM** x$ksmsp  
26.    **WHERE** ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'  
27. **GROUP** **BY** ksmchcls, 500 \* TRUNC (ksmchsiz / 500)  
28. **UNION** ALL  
29. **SELECT**   '6+ (4108+)' bucket, ksmchcls, 1000 \* TRUNC (ksmchsiz / 1000),  
30.          COUNT (\*), **MAX** (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
31.          TRUNC (SUM (ksmchsiz)) "Total"  
32.     **FROM** x$ksmsp  
33.    **WHERE** ksmchsiz >= 4108 AND ksmchcls = 'free'  
34. **GROUP** **BY** ksmchcls, 1000 \* TRUNC (ksmchsiz / 1000);

**4.ORA-04031 错误与 Large Pool **  
        大池是个可选的内存区，为以下的操作提供大内存分配：

i.MTS会话内存和 Oracle XA 接口   
        ii.Oracle 备份与恢复操作和I/O服务器进程用的内存(缓冲)  
        iii.并行执行消息缓冲   
        大池没有LRU列表。这和共享池中的保留空间不同，保留空间和共享池中其他分配的内存使用同样的LRU列表。大块内存从不会换出大池中，内存必须是显式的被每个会话分配并释放。一个请求如果没有足够的内存，就会产生类似这样的一个ORA-4031错误：

Text代码   ![收藏代码][icon_star.png]

1.  ORA-04031: unable to allocate XXXX bytes of shared memory   
2.  ("large pool","unknown object","session heap","frame")

这个错误发生时候可以检查几件事情：   
        **a.使用如下语句检查 V$SGASTAT ，得知使用和空闲的内存：**

Sql代码   ![收藏代码][icon_star.png]

1.  **SELECT** pool,**name**,bytes **FROM** v$sgastat **where** pool = 'large pool';

**b.你还可以采用 heapdump level 32 来 dump 大池的堆并检查空闲的大块内存的大小**  
        从大池分配的内存如果是LARGE\_POOL\_MIN\_ALLOC 子节的整块数有助于避免碎片。任何请求分配小于LARGE\_POOL\_MIN\_ALLOC 大块尺寸都将分配LARGE\_POOL\_MIN\_ALLOC的大小。一般来说，你会看到使用大池的时候相对共享池来说要用到更多的内存。通常要解决大池中的ORA-4031错误必须增加 LARGE\_POOL\_SIZE 的大小。

**5. ORA-04031 和共享池刷新**  
        有一些技巧会提高游标的共享能力，从而共享池碎片和ORA-4031都会减少。最佳途径是调整应用使用绑定变量。另外在应用不能调整的时候考虑使用CURSOR\_SHARING参数和FORCE不同的值来做到 (要注意那会导致执行计划改变，所以建议先对应用进行测试)。当上述技巧都不可以用的时候，并且碎片问题在系统中比较严重，刷新共享持可能有助于减轻碎片问题。但是，必须加以如下考虑：   
        a.刷新将导致所有没被使用的游标从共享池删除。这样，在共享池刷新之后，大多数SQL和PL/SQL游标必须被硬解析。这将提高CPU的使用，也会加大Latch的活动。   
        b.当应用程序没有使用绑定变量并被许多用户进行类似的操作的时候(如在OLTP系统中) ，刷新之后很快还会出现碎片问题。所以共享池对设计糟糕的应用程序来说不是解决办法。   
        c.对一个大的共享池刷新可能会导致系统挂起，尤其是实例繁忙的时候，推荐在非高峰的时候刷新

**6. ORA-04031错误的高级分析**  
        如果前述的这些技术内容都不能解决ORA-04031 错误，可能需要额外的跟踪信息来得到问题发生的共享池的快照。   
        调整init.ora参数添加如下的事件得到该问题的跟踪信息：

Text代码   ![收藏代码][icon_star.png]

1.  event = "4031 trace name errorstack level 3"   
2.  event = "4031 trace name HEAPDUMP level 3"

如果问题可重现，该事件可设定在会话层，在执行问题语句之前使用如下的语句：

Text代码   ![收藏代码][icon_star.png]

1.  SQL> alter session set events '4031 trace name errorstack level 3';   
2.  SQL> alter session set events '4031 trace name HEAPDUMP level 3';

把这个跟踪文件发给Oracle支持人员进行排错。   
        重要标注: Oracle 9.2.0.5 和Oracle 10g 版本中，每次在发生ORA-4031 错误的时候会自动创建一个跟踪文件，可以在user\_dump\_dest 目录中找到。如果你的系统是上述的版本，你不需要再进行前面描述中的步骤。

============

# [解决0RA-04031故障][0RA-04031] #

1.客户反应报表数据很慢，简单查询5分钟都出不来。

2.登陆数据库服务器检查日志：  
Thu Mar 21 16:20:30 2013  
Errors in file /opt/oracle/diag/rdbms/rptdw/rptdw2/trace/rptdw2\_lms0\_9538.trc (incident=144231):  
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","gcs dynamic r")  
Incident details in: /opt/oracle/diag/rdbms/rptdw/rptdw2/incident/incdir\_144231/rptdw2\_lms0\_9538\_i144231.trc

3.了解一下shared pool的原理  
library cache包含 SQL语句,分析的代码,执行计划等  
data dictionary cache包含 table, column等对象的定义以及权限的信息

主要包含如下组件：  
Permanent Area  
Segmented Arrays（锁、事务、资源等）  
Library Cache  
Row Cache  
Reserved Area

查看共享池大小和共享池保留区大小  
select \* from v$sga\_dynamic\_components ;  
shared pool 3019898880

SQL> show parameter shared  
NAME TYPE  
\------------------------------------ --------------------------------  
VALUE  
\------------------------------  
shared\_pool\_reserved\_size big integer  
154350387

查看共享池隐含参数  
SELECT ksppinm, ksppstvl, ksppdesc  
FROM x$ksppi x, x$ksppcv y  
WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '\_', '\#') LIKE '\#share%';  
ksppinm ksppstvl ksppdesc  
\_shared\_pool\_reserved\_pct 5 percentage memory of the shared pool allocated for the reserved area  
\_shared\_pool\_reserved\_min\_alloc 4400 minimum allocation size in bytes for reserved area of shared pool  
\_shared\_pool\_max\_size 0 shared pool maximum size when auto SGA enabled  
\_shared\_pool\_minsize\_on FALSE shared pool minimum size when auto SGA enabled  
\_shared\_io\_pool\_size 0 Size of shared IO pool  
\_shared\_iop\_max\_size 536870912 maximum shared io pool size  
\_shared\_io\_pool\_buf\_size 1048576 Shared IO pool buffer size  
\_shared\_io\_pool\_debug\_trc 0 trace kcbi debug info to tracefile  
\_shared\_io\_set\_value FALSE shared io pool size set internal value - overwrite zero user size  
\_shared\_server\_load\_balance 0 shared server load balance  
\_shared\_server\_num\_queues 2 number of shared server common queues

转储shared pool 共享内存的内容  
SQL> alter session set events 'immediate trace name heapdump level 2' ;

Session altered.

直观查看shared pool结构：  
FREE LISTS:  
Bucket 0 size=32  
Bucket 1 size=40  
Bucket 2 size=48  
Bucket 3 size=56  
Bucket 4 size=64  
Chunk 09e3fffc0 sz= 64 free " "  
Bucket 5 size=72  
Bucket 6 size=80  
Chunk 09d3fffb0 sz= 80 free " "

RESERVED FREE LISTS:  
Reserved bucket 0 size=32  
Reserved bucket 1 size=4400  
Reserved bucket 2 size=8216  
Reserved bucket 3 size=8696  
Reserved bucket 4 size=8704  
Reserved bucket 5 size=8712  
Reserved bucket 6 size=8720  
Reserved bucket 7 size=9368  
Reserved bucket 8 size=9376  
Reserved bucket 9 size=12352  
Reserved bucket 10 size=12360  
Reserved bucket 11 size=16408  
Reserved bucket 12 size=32792  
Reserved bucket 13 size=65560  
Chunk 099c00088 sz= 212808 R-free " "  
Chunk 09a000088 sz= 212808 R-free " "  
Chunk 09a400088 sz= 212808 R-free " "  
Chunk 09a800088 sz= 212808 R-free " "

SQL> show parameter user\_

NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
license\_max\_users integer 0  
user\_dump\_dest string /opt/oracle/diag/rdbms/etldb2/  
etldb2/trace

了解 X$KSMSP视图（Kernal Storage Memoty Management SGA HeaP）  
  
1、其中每一行都代表shared pool中的一个chunk（内存块）  
2、主要字段的解释  
ksmchcom：是注释字段，每个内存块被分配以后，注释会添加在该字段中。  
ksmchsiz：代表块大小  
ksmchcls：代表类型，主要有4类  
1）free  
free chunks 不包含任何对象的chunk，可以不受限制的被自由分配。  
2）recreate  
recreatable chunks：包含可以被临时移出内存的对象，在需要的时候，这个对象可以被重新创建。例如，许多存储共享sql代码的内存都是可以重建的。  
3）freeable  
freeable chunks：包含session周期或调用的对象，随后可以释放。这部分内存有时候可以全部或部分提前释放，但是注意,由于某些对象是中间过程产生的，这些对象不能临时被移除内存（因为不可以重建）  
4）perm  
permanent memory chunks：包含永久对象，通常不能独立释放。  
  
4.处理ORA-04031  
当尝试在共享池分配大块的连续内存失败（很多时候是由于碎片过多，而并非真是内存不足）时，oracle首先清除共享池中当前没有使用的多有对象，  
使空闲内存合并。如果任然没有足够大德单块内存可以满足需要，就会差生ora-04031错误。  
  
解决思路：  
使用flush shared pool 缓解共享池问题  
1、 刷新共享池：  
alter system flush shared pool；刷新共享池可以帮助合并碎片，强制老化sql，释放共享池，但是这通常是不推荐的做法。因为  
1）flush shared pool 会导致当前未使用的cursor被清除共享池，如果这些sql随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的cpu争用，数据库将会产生激烈的latch竞争。  
2）如果应用没有使用绑定变量，大量类似的sql不停的执行，那么flush shared pool可能会带来短暂的改善，数据库很快会回到原来的状态。  
3）如果shared pool很大，并且系统非常的繁忙，刷新shared pool可能会导致系统挂起，对于类似系统尽量在系统空闲时进行。

shared\_pool\_reserved\_size参数的设置和作用  
1、shared\_pool\_reserved\_size参数指定了保留的共享池空间，用于满足大的连续的共享池空间请求。当共享池出现多碎片时，请求大块空间会导致oracle大范围地查找并释放共享池内存来满足请求，由此可能会带来较为严重的性能下降，设置合适的shared\_pool\_reserved\_size参数和\_shared\_pool\_reserved\_min\_alloc参数可以避免由此导致的性能下降。\_shared\_pool\_reserved\_min\_alloc这个参数控制这保留内存的控制和分配。缺省值为4400bytes，即当请求的内存大于这个值时就进入共享池保留空间分配内存。  
2、查看v$shared\_pool\_reserved视图可以判断共享池问题的引发原因。  
select free\_space,avg\_free\_size,used\_space,avg\_used\_size,request\_failures,last\_failure\_size from v$shared\_pool\_reserved；  
若request\_failures大于0且last\_failure\_size大于shared\_pool\_reserved\_min\_alloc，那么ora-04031错误就可能是因为共享池保留空间缺少连续空间所致。要解决这个问题，可以考虑加大shared\_pool\_reserved\_min\_alloc来降低缓冲进共享池保留空间的对象数目。并增大shared\_pool\_reserved\_size 和 shared\_pool\_size来加大共享池保留空间的可用内存。如果request\_failures > 0 并且 last\_failure\_size < \_shared\_pool\_reserved\_min\_alloc，那么是因为在库高速缓冲缺少连续空间导致ORA-04031错误。对于这一类情况应该考虑降低\_shared\_pool\_reserved\_min\_alloc，以放入更多的对象到共享池保留空间并加大shared\_pool\_size。  
  
表面看是共享池资源不足语句造成的。  
查询一下共享池参数配置  
SELECT free\_space, avg\_free\_size,used\_space, avg\_used\_size, request\_failures,last\_failure\_size FROM v$shared\_pool\_reserved;  
FREE\_SPACE AVG\_FREE\_SIZE USED\_SPACE AVG\_USED\_SIZE REQUEST\_FAILURES LAST\_FAILURE\_SIZE  
129027376 781984.097 100931920 611708.6061 1209 4000

SQL> SELECT nam.ksppinm NAME, v.ksppstvl VALUE  
FROM x$ksppi n, x$ksppsv v  
WHERE n.indx = v.indx AND n.ksppinm LIKE '%shared%'  
ORDER BY 1;  
\_shared\_pool\_reserved\_min\_alloc  
4400

通过REQUEST\_FAILURES > 0 并且 LAST\_FAILURE\_SIZE < SHARED\_POOL\_RESERVED\_MIN\_ALLOC ,可以知道是因为共享池库缓存不足造成，可以降低\_shared\_pool\_reserved\_min\_alloc  
并且增加share\_pool\_size。

5.我的处理方法  
因为我们使用了数据库内存自动管理，不方便修改share pool的大小，并且修改隐藏参数是有风险的，可能会引发其它的bug,我觉得可以从sql语句上控制产生ORA-04031发生。  
通过sql语句监控可以知道发生错误时候我们执行了清理历史分区的语句，类似下面的：  
alter table DW.TW\_RS\_I2000\_PLATFORM\_H drop partition P2012101415 ;  
alter table DW.TW\_RS\_I2000\_PLATFORM\_H drop partition P2012102223 ;  
alter table DW.TW\_RS\_I2000\_PLATFORM\_H drop partition P2012103107 ;  
alter table DW.TW\_RS\_I2000\_STORAGE\_H drop partition P2012100815 ;  
…  
清除这些分区之前，oracle都需要把这些块读入共享池，然后再处理，既然share pool不足以一次装这么多新的数据块，那我们可以让共享池分批处理这些数据，就可以有效的避免ORA-04031错误

通过一个简单的python脚本，把删除语句加工一下，每200行插入清理共享池的语句即可  
\#!/usr/bin/python  
\#Filename : droptbs.py  
import os  
num = 0  
file\_src = open('/home/etl/etl\_script/lisx/dropdw.txt')  
file\_dest = open('/home/etl/etl\_script/lisx/dropdw2.txt','a')  
for l in file\_src.xreadlines():  
num += 1  
file\_dest.write(l)  
if num % 200 == 0:  
file\_dest.write('alter system flush shared\_pool ;\\n')  
file\_src.close()  
file\_dest.close()  
再次执行删除清理表空间的脚本，数据库响应正常。

[Oracle]: https://www.2cto.com/database/Oracle/
[Link 1]: https://www.2cto.com/database/
[Link 2]: https://www.2cto.com/os/
[icon_star.png]: /images/20220530/15aba1a58264425bb6e9c5397eaf72e4.png
[f5342356-2618-3053-b5a9-f86da4c98c3c.png]: /images/20220530/ba559314b88f4fa29b9f585e4a83d799.png
[Link 3]: http://www.dotdy.com/
[3e4378db-afac-3041-bd11-bb0940489703.png]: /images/20220530/6714ebdef7564b72a0ef287c70ab070b.png
[0RA-04031]: http://www.cnblogs.com/caibird2005/p/3607241.html