修正oracle错误,oracle数据库菜鸟教程 [数据库中一个常见错误信息诊断与修正]

摘要：本文简要的对Oracle数据库一个常见的错误信息ora-04031错误进行了分析与诊断，同时对其形成原因进行了初步探讨。对如何诊断该项错误进行了简单的阐述，并举实例分析了如何解决该错误。

关键词：数据库oracle；ora-04031；诊断；修正

中图分类号：TP311文献标识码：A文章编号：1009-3044(2007)18-31485-01

Diagnosis and Modification of a Common Error in Database

SU Tong-bo

(Qingdao Airport Frontier Inspection Station,Qingdao 266034,China)

Abstract:A brief analysis and diagnosis is given to a common error(ora-04031) in Database plus its reason.Further,it explains how to diagnose this error while citing examples to how to solve it.

Key words:Database oracle;ora-04031;Diagnosis;modification

在Oracle数据库的应用中,我们经常遇到如下这么一个问题既当在共享池中试图分配大片的连续内存失败的时候，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 共享池相关的实例参数

SHARED_POOL_SIZE

这个参数指定了共享池的大小，单位是字节。可以接受数字值或者数字后面跟上后缀"K" 或 "M" 。"K"代表千字节, "M"代表兆字节。

SHARED_POOL_RESERVED_SIZE

指定了为共享池内存保留的用于大的连续请求的共享池空间。当共享池碎片强制使 Oracle 查找并释放大块未使用的池来满足当前的请求的时候，这个参数和SHARED_POOL_RESERVED_MIN_ALLOC 参数一起可以用来避免性能下降。

这个参数理想的值应该大到足以满足任何对保留列表中内存的请求扫描而无需从共享池中刷新对象。既然操作系统内存可以限制共享池的大小，一般来说，你应该设定这个参数为 SHARED_POOL_SIZE 参数的 10% 大小。

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;

2 诊断ORA-04031 错误

注：大多数的常见的 ORA-04031 的产生都和 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_POO

L_SIZE 来加大共享池保留空间的可用内存。

如果：

REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE 　　那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。

3 解决ORA-04031 错误

ORACLE BUG

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

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

库高速缓冲命中率

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

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE;

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

共享池大小计算

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

: HOW TO CALCULATE YOUR SHARED POOL SIZE.

共享池碎片

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

大块(chunk)大小比请求的大小大

空间是连续的

大块内存是可用的(而不是正在使用的)

这样大块的内存被分开，剩余的添加到相应的空闲空间列表中。当数据库以这种方式操作一段时间之后，共享池结构就会出现碎片。

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

参考 : 可以得到关于共享池碎片的详细讨论。

如果SHARED_POOL_SIZE 足够大，大多数的 ORA-04031 错误都是由共享池中的动态SQL 碎片导致的。可能的原因如下：

非共享的SQL

生成不必要的解析调用 (软解析)

没有使用绑定变量

要减少碎片的产生你需要确定是前面描叙的几种可能的因素。可以采取如下的一些方法，当然不只局限于这几种: 应用调整、数据库调整或者实例参数调整。

4 ORA-04031 错误与 Large Pool

大池是个可选的内存区，为以下的操作提供大内存分配：

MTS会话内存和 Oracle XA 接口

Oracle 备份与恢复操作和I/O服务器进程用的内存(缓冲)

并行执行消息缓冲

大池没有LRU列表。这和共享池中的保留空间不同，保留空间和共享池中其他分配的内存使用同样的LRU列表。大块内存从不会换出大池中，内存必须是显式的被每个会话分配并释放。一个请求如果没有足够的内存，就会产生类似这样的一个ORA-4031错误：

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

("large pool","unknown object","session heap","frame")

这个错误发生时候可以检查几件事情：

(1)使用如下语句检查 V$SGASTAT ，得知使用和空闲的内存： 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 的大小。

参考文献：

[1](美)曼尼诺.著.唐常杰.等.译.数据库设计、应用开发与管理.7121007738,电子工业出版社.

[2]林慧.等. ORACLE 10G入门与实践.中国铁道出版社,2005.11 9787113066376 / 7113066372.