当我们在共享池中试图分配大片的连续内存失败的时候，Oracle首先清除池中当前没使用的所有对象，使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031错误。
查询共享池相关参数：
SELECT nam.ksppinm NAME, val.ksppstvl VALUE
FROM xksppsv val
WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'
ORDER BY 1;
1、共享池相关的实例参数
当我们使用下面的命令时，往往看到结果如下：
1.1、SQL> show parameter shared_pool
NAME TYPE VALUE
shared_pool_reserved_size big integer 20971520
shared_pool_size big integer 419430400
在Oracle中，shared_pool包含着两部分，其中一个参数是：shared_pool_reserved_size。此参数是用来指定保留的共享池空间大小，用于满足将来的大的连续的共享池空间请求。当共享池出现过多碎片，请求大块空间会导致Oracle大范围的查找并释放共享池内存来满足请求，由此可能会带来较为严重的性能下降，通过设置合适的shared_pool_reserved_size参数，结合shared_pool_reserved_min_alloc参数可以用来避免由此导致的性能下降。
这个参数理想值应该大到足以满足任何对RESERVED LIST的内存请求，而无需数据库从共享池中刷新对象。这个参数的缺省值是shared_pool_size的5％，通常这个参数的建议值为shared_pool_size参数的10％～20％大小，最大不得超过shared_pool_size的50％。
同样地，在trace文件中，可以找到关于保留列表（RESERVED LIST）的内存信息。
_shared_pool_reserved_min_alloc这个参数的值控制保留内存的使用和分配。如果一个足够尺寸的大块内存请求在共享池空闲列表（FREE LIST）中没能找到，内存就从保留列表（RESERVED LIST）中分配一块比这个值大的空间。
在不同的版本中，该参数的缺省值一直都是4400。
这个参数默认值对于大多数系统来说都足够了。如果系统经常出现ORA-04031错误都是请求大于4400的内存块，那么就可能需要增加shared_pool_reserved_size参数设置。
而如果主要的引发LRU合并、老化并出现ORA-04031错误的内存请求在4100～4400bytes之间，那么降低_shared_pool_reserved_min_alloc同时适当增大SHARED_POOL_RESERVED_SIZE参数值通常会有所帮助。设置_shared_pool_reserved_min_alloc=4100可以增加Shared Pool成功满足请求的概率。需要注意的是，这个参数的修改应对结合Shared Pool Size 和 Shared Pool Reserved Size的修改。设置_shared_pool_reserved_min_alloc=4100是经过证明的可靠方式，不建议设置更低。
查询vshared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
44406648 727977.836 86640 1420.32787 2 3896
1.2、V$SHARED_POOL_RESERVED参数详解：
FREE_SPACE：保留区中自由空间的总和
AVG_FREE_SIZE：每个自由内存块的平均大小
FREE_COUNT：自由的内存块数量
MAX_FREE_SIZE：最大的自由内存块大小
USED_SPACE：保留区中已使用的空间大小
AVG_USED_SIZE：每个已经使用的内存块的平均大小
USED_COUNT：已使用的内存块数量
MAX_USED_SIZE：已使用的最大的内存块大小
REQUESTS：在保留区中搜索自由内存块的次数
REQUEST_MISSES：保留区中没有可以满足要求的内存块并开始在保留区中LRU链中刷新对象的次数
LAST_MISS_SIZE：最后一次出现REQUEST_MISSES时，所请求的空间大小
MAX_MISS_SIZE：最大一次出现REQUEST_MISSES时，所请求的空间大小
下面的列即使SHARED_POOL_RESERVED_SIZE没有被设置也是有效的：
REQUEST_FAILURES：无论保留区还是非保留区，没有可以满足要求的内存的次数（也就是ORA-4031错误发生的次数）
LAST_FAILURE_SIZE：最后一次REQUEST_FAILURES时所请求的内存大小
后三列相关DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD过程。
ABORTED_REQUEST_THRESHOLD：最小的超过DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD设置值的大小
ABORTED_REQUESTS：超过DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD的次数
LAST_ABORTED_SIZE：最后一次超过DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD时的大小。
如果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，此时不满足从reserved分配空间的最小值要求，而失败，说明是因为在库高速缓冲（shared_pool_size和open_cursors）缺少连续空间导致ORA-04031错误。此时可以考虑适当增加shared_pool_size或open_cursors来解决此错误。
1.3、VDB_OBJECT_CACHE显示在库缓存缓存（共享池）中被缓存的库对象。它比动态性能表 V$LIBRARYCACHE提供更多细节，并且在寻找共享池
中活动对象方面更加有用。这些对象包括表，索引，簇，PL/SQL过程和包装，并触发。在共享池对象级别的统计信息。
select * from VSQLAREA 视图
这个视图保存了在数据库中执行的SQL 语句和PL/SQL 块的信息。下面的SQL 语句可以显示给你带有literal 的语句或者是带有绑定变量的语句：
SELECT SUBSTR (sql_text, 1, 40) "SQL", COUNT (),
SUM (executions) "TotExecs"
FROM vKSMLRU 视图
这个固定表xksmlru 表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。监视这个固定表运行如下操作：
SELECT * FROM XKSMSP 视图 (类似堆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 xksmsp
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 xksmsp
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 xSGASTAT ，得知使用和空闲的内存：
• SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';
• 2- 你还可以采用 heapdump level 32 来 dump 大池的堆并检查空闲的大块内存的大小
从大池分配的内存如果是LARGE_POOL_MIN_ALLOC 子节的整块数有助于避免碎片。任何请求分配小于LARGE_POOL_MIN_ALLOC 大块尺寸都将分配LARGE_POOL_MIN_ALLOC的大小。一般来说，你会看到使用大池的时候相对共享池来说要用到更多的内存。通常要解决大池中的ORA-4031错误必须增加 LARGE_POOL_SIZE 的大小。
4、 ORA-04031 和共享池刷新
有一些技巧会提高游标的共享能力，从而共享池碎片和ORA-4031都会减少。最佳途径是调整应用使用绑定变量。另外在应用不能调整的时候考虑使用CURSOR_SHARING参数和FORCE不同的值来做到 (要注意那会导致执行计划改变，所以建议先对应用进行测试)。当上述技巧都不可以用的时候，并且碎片问题在系统中比较严重，刷新共享持可能有助于减轻碎片问题。但是，必须加以如下考虑：
• 刷新将导致所有没被使用的游标从共享池删除。这样，在共享池刷新之后，大多数SQL和PL/SQL游标必须被硬解析。这将提高CPU的使用，也会加大Latch的活动。
• 当应用程序没有使用绑定变量并被许多用户进行类似的操作的时候(如在OLTP系统中) ，刷新之后很快还会出现碎片问题。所以共享池对设计糟糕的应用程序来说不是解决办法。
• 对一个大的共享池刷新可能会导致系统挂起，尤其是实例繁忙的时候，推荐在非高峰的时候刷新
5、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 目录中找到。如果你的系统是上述的版本，你不需要再进行前面描述中的步骤。
手动调整SGA的大小，然后重新分配四大内存区域的大小。主要增加共享内存和缓冲高速缓存。
sql> show sga; //查看SGA的具体大小信息。
sql>show parameter sga_max_size //查看SGA最大值
sql> show parameter shared_pool //查看共享内存
sql>show parameter db_cache //查看数据缓存
sql> alter system set sga_max_size = 500M scope=spfile;//修改SGA最大值
sql> alter system set shared_pool_size =200M scope=spfile; //修改共享内存
sql> alter system set db_cache_size =250M scope=spfile; //修改数据缓存
shared_pool_reserved_min_alloc控制保留池中最小的分配大小，默认大小为4400，其取值范围为4000bytes到60M之间
select name,
sgasize/1024/1024 "Allocated(M)",
bytes/1024 "空间(K)",
round(bytes/sgasize100, 2) "*空间百分比(%)"
from (select sum(bytes) sgasize from sys.v_sgastat f
where f.name = 'free memory';
网友评论