如何解决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，存贮在
数据库中
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
系统里面
放到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 错误。
当这个错误出现的时候你得到的错误解释信息类似如下：
- 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
- // *Cause: More shared memory is needed than was allocated in the shared
- // pool.
- // *Action: If the shared pool is out of memory, either use the
- // dbms_shared_pool package to pin large packages,
- // reduce your use of shared memory, or increase the amount of
- // available shared memory by increasing the value of the
- // INIT.ORA parameters "shared_pool_reserved_size" and
- // "shared_pool_size".
- // If the large pool is out of memory, increase the INIT.ORA
- // 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 和更高的版本中是隐藏的。提交如下的语句查找这个参数值：
- SELECT nam.ksppinm NAME, val.ksppstvl VALUE
- FROM x$ksppi nam, x$ksppsv val
- WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'
- ORDER BY 1;
运行结果：
10g 注释：Oracle 10g 的一个新特性叫做 "自动内存管理" 允许DBA保留一个共享内存池来分shared pool,buffer cache, java pool 和large pool。一般来说，当数据库需要分配一个大的对象到共享池中并且不能找到连续的可用空间，将自动使用其他SGA结构的空闲空间来增加共享池的大小 。既然空间分配是Oracle自动管理的，ora-4031出错的可能性将大大降低。自动内存管理在初始化参数SGA_TARGET大于0的时候被激活。当前设定可以通过查询v$sga_dynamic_components 视图获得。请参考10g管理手册以得到更多内容。
伦理电影 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 错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查询：
- 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
或者
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、可能的环境和修补这个问题的补丁。
2).编译Java代码时出现的ORA-4031
在你编译Java代码的时候如果内存溢出，你会看到错误：
- A SQL exception occurred while compiling: :
- ORA-04031: unable to allocate bytes of shared memory
- ("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语句有助于你计算库高速缓冲的命中率：
- SELECT SUM(PINS) "EXECUTIONS",
- SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
- 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 的语句或者是带有绑定变量的语句：
- SELECT SUBSTR (sql_text, 1, 40) "SQL", COUNT (*),
- SUM (executions) "TotExecs"
- FROM v$sqlarea
- WHERE executions < 5
- GROUP BY SUBSTR (sql_text, 1, 40)
- HAVING COUNT (*) > 30
- ORDER BY 2;
注: Having 后的数值 "30" 可以根据需要调整以得到更为详细的信息。
b.X$KSMLRU 视图
这个固定表x$ksmlru 跟踪共享池中导致其它对象换出(age out)的应用。这个固定表可以用来标记是什么导致了大的应用。
如果很多对象在共享池中都被阶段性的刷新可能导致响应时间问题并且有可能在对象重载入共享池中的时候导致库高速缓冲闩竞争问题。
关于这个x$ksmlru 表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。监视这个固定表运行如下操作：
- SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
这个表只可以用SYS用户登录进行查询。
c.X$KSMSP 视图 (类似堆Heapdump信息)
使用这个视图能找出当前分配的空闲空间，有助于理解共享池碎片的程度。如我们在前面的描述，查找为游标分配的足够的大块内存的第一个地方是空闲列表( free list)。 下面的语句显示了空闲列表中的大块内存：
- SELECT '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",
- COUNT (*) "Count", MAX (ksmchsiz) "Biggest",
- TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"
- FROM x$ksmsp
- WHERE ksmchsiz < 140 AND ksmchcls = 'free'
- GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)
- UNION ALL
- SELECT '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz / 20),
- COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
- TRUNC (SUM (ksmchsiz)) "Total"
- FROM x$ksmsp
- WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'
- GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)
- UNION ALL
- SELECT '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz / 50),
- COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
- TRUNC (SUM (ksmchsiz)) "Total"
- FROM x$ksmsp
- WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'
- GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)
- UNION ALL
- SELECT '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz / 500),
- COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
- TRUNC (SUM (ksmchsiz)) "Total"
- FROM x$ksmsp
- WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'
- GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)
- UNION ALL
- SELECT '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz / 1000),
- COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
- TRUNC (SUM (ksmchsiz)) "Total"
- FROM x$ksmsp
- WHERE ksmchsiz >= 4108 AND ksmchcls = 'free'
- 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错误：
- ORA-04031: unable to allocate XXXX bytes of shared memory
- ("large pool","unknown object","session heap","frame")
这个错误发生时候可以检查几件事情：
a.使用如下语句检查 V$SGASTAT ，得知使用和空闲的内存：
- 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参数添加如下的事件得到该问题的跟踪信息：
- event = "4031 trace name errorstack level 3"
- event = "4031 trace name HEAPDUMP level 3"
如果问题可重现，该事件可设定在会话层，在执行问题语句之前使用如下的语句：
- SQL> alter session set events '4031 trace name errorstack level 3';
- SQL> alter session set events '4031 trace name HEAPDUMP level 3';
把这个跟踪文件发给Oracle支持人员进行排错。
重要标注: Oracle 9.2.0.5 和Oracle 10g 版本中，每次在发生ORA-4031 错误的时候会自动创建一个跟踪文件，可以在user_dump_dest 目录中找到。如果你的系统是上述的版本，你不需要再进行前面描述中的步骤。
============
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()
再次执行删除清理表空间的脚本，数据库响应正常。
1041
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
