ORA-04031错误可能由于共享池尺寸过小或者严重的碎片导致数据库不能找到足够大的内存chunk。 总的来说，减少碎片你必须分析应用程序是如何使用共享池并且最大化游标的共享。 参考Note 62143.1以获得这方面更详细的信息及共享池是如何工作的。
http://echooooo.itpub.net/post/25716/229616
对于大多数应用来说，共享池的大小对于Oracle 性能来说都是很重要的。共享池中保存数据字典高速缓冲和完全解析或编译的的PL/SQL 块和SQL 语句。
当我们在共享池中试图分配大片的连续内存失败的时候，Oracle首先刷新池中当前没使用的所有对象，使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031 错误。
当这个错误出现的时候你得到的错误信息如下:
Error : ORA 4031
Text : unable to allocate %s bytes of shared memory (%s,%s,%s)
----------------------------------------------------------------------------------------------------------------
Cause : More shared memory is needed than was allocated in the shared pool.
Action : 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 parameter shared_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 是隐藏的。
2.诊断ORA-04031 错误
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 错误
? ORACLE BUG
要解决这个错误(如果可以称得上错误的话）,进行的诊断的第一步是在你的平台上使用最新的补丁集。大多数的ORA-04031错误都和BUG 相关，可以通过使用这些补丁来避免。
下面表中总结和和这个错误相关的最常见的BUG，可能的环境和修补这个问题的补丁。
BUG 描述 Workaround Fixed
<Bug:1397603> ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles _db_handles_cached = 0 901/ 8172
<Bug:1640583> ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access Not available 8171/901
<Bug:1318267> INSERT AS SELECT statements may
not be shared when they should be
if TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0
8171/8200
<Bug:1193003> Cursors may not be shared in 8.1
when they should be Not available 8162/8170/ 901
共享池结构中的一些BUG 会引起这个错误，不过通常大量的共享的SQL/PLSQL 语句也会引起这个错误。一旦打过了最新的补丁，在遇到这个问题的时候我们强烈推荐调整数据库和应用。
要得到已知的BUG 的完整信息，可以参考：
<Note:62143.1>: Main issues affecting the Shared Pool on Oracle 7 , Oracle8 and Oracle8i。
? 共享池碎片
每一次，需要被执行的SQL 或者PL/SQL 语句的解析形式载入共享池中都需要一块特定的连续的空间。数据库要扫描的第一个资源就是共享池中的空闲可用内存。一旦空闲内存耗尽，数据库要查找一块已经分配但还没使用的内存准备重用。如果这样的确切尺寸的大块内存不可用，就继续按照如下标准寻找：
◇ 大块(chunk)大小比请求的大小大
◇ 空间是连续的
◇ 大块内存是可用的(而不是正在使用的)
这样大块的内存被分开，剩余的添加到相应的空闲空间列表中。当数据库以这种方式操作一段时间之后，共享池结构就会出现碎片。
当共享池存在碎片的问题,分配一片空闲的空间就会花费更多的时间,数据库性能也会下降(整个操作的过程中，chunk allocation被一个叫做shared pool latch 的闩所控制) 或者是出现ORA-04031 错误errors (在数据库不能找到一个连续的空闲内存块的时候)。
如果SHARED_POOL_SIZE 足够大，大多数的 ORA-04031 错误都是由共享池中的动态SQL碎片导致的。可能的原因如下：
◇非共享的SQL
◇生成不必要的解析调用 (软解析)
◇没有使用绑定变量
要减少碎片的产生你需要确定是前面描叙的几种可能的因素。可以采取如下的一些方法，当然不只局限于这几种: 应用调整、数据库调整或者实例参数调整。
下面的视图有助于你标明共享池中非共享的SQL/PLSQL：
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数值可以根据需要调整以得到更为详细的信息。
X$KSMLRU 视图
有一个固定表x$ksmlru 跟踪共享池中导致其它对象换出(age out)的应用。这个固定表可以用来标记是什么导致了大的应用。
如果很多对象在共享池中都被阶段性的刷新可能导致响应时间问题并且有可能在对象重载入共享池中的时候导致库高速缓冲闩竞争问题。
关于这个x$ksmlru 表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。监视这个固定表运行如下操作：
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
在Oracle8i 中这个表不能被SYS用户之外的用户所选取。
? 小的共享池尺寸
最后,一个小的共享池可以导致ORA-04031 错误, 不过在碎片真正的是个问题的时候增大共享池的大小的时候要小心。在错误发现的时候通常有延迟现象，不过当在大的共享池的碎片中找到一片空闲的内存会加大对性能的影响。
下面的信息将有助于你调整共享池的大小：
库高速缓冲命中率
命中率有助于你衡量共享池的使用，基于多少次SQL/PLSQL 需要被解析而不是重用。下面的SQL 语句有助于你计算库高速缓冲的命中率：
SELECT SUM(PINS) EXECUTIONS,
SUM(RELOADS) CACHE MISSES WHILE EXECUTING
FROM V$LIBRARYCACHE;
如果misses 比上executions 大于1%, 那就应该尝试着通过加大共享池来减少库高速缓冲的丢失。
Shared Pool Size Calculation
要计算最适合当前工作负荷的共享池大小，参考：
.设置闪回恢复区
闪回恢复区主要通过3个初始化参数来设置和管理
- db_recovery_file_dest：指定闪回恢复区的位置
- db_recovery_file_dest_size：指定闪回恢复区的可用空间大小
- db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟，也就是一天。当然，实际上可回退的时间还决定于闪回恢复区的大小，因为里面保存了回退所需要的flash log。所以这个参数要和db_recovery_file_dest_size配合修改。
2.启动flashback database
设置了闪回恢复区后，可以启动闪回数据库功能。
首先，数据库必须已经处于归档模式
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 245
下一个存档日志序列 247
当前日志序列 247
然后，启动数据库到mount状态
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> alter database flashback on;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------------------------
YES
关于flashback database的功能，这里就不继续深入了。
3.取消闪回恢复区
将db_recovery_file_dest参数设置为空，可以停用闪回恢复区。
如果已经启用flashback database，则不能取消闪回恢复区。
SQL> alter system set db_recovery_file_dest='';
alter system set db_recovery_file_dest=''
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-38775: 无法禁用快速恢复区 - 闪回数据库已启用
所以，必须先禁用flashback database，才能取消闪回恢复区
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> alter database flashback off;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> alter system set db_recovery_file_dest='';
系统已更改。
4.闪回恢复区的内容
所有和恢复相关的文件都可以存放到闪回恢复区
SQL> select file_type from v$flash_recovery_area_usage;
FILE_TYPE
------------------------
CONTROLFILE
ONLINELOG
ARCHIVELOG
BACKUPPIECE
IMAGECOPY
FLASHBACKLOG
已选择6行。
上面的视图中可以看出，包括controfile,online redo logfile,archive logfile,rman backup
piece,rman image copy, flashback log等，都可以利用闪回恢复区来存放、管理。
5.闪回恢复区的一些限制
如果设置了闪回恢复区，则log_archive_dest和log_archive_duplex_dest将不可用
alter system set log_archive_dest='e:/'
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-16018: 无法将 LOG_ARCHIVE_DEST 与 LOG_ARCHIVE_DEST_n 或
DB_RECOVERY_FILE_DEST 一起使用
SQL> alter system set log_archive_duplex_dest='e:/';
alter system set log_archive_duplex_dest='e:/'
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-16018: 无法将 LOG_ARCHIVE_DUPLEX_DEST 与 LOG_ARCHIVE_DEST_n 或
DB_RECOVERY_FILE_DEST 一起使用
设置闪回恢复区后，如果没有设置过log_archive_dest_n参数，则归档日志默认是保存到该区域的
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 245
下一个存档日志序列 247
当前日志序列 247
实际上，oracle是通过隐式的设置log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST'来实现的。所以，如果之后你修改过log_archive_dest_n将归档日志保存到其他位置，也可以修改该参数继续使用闪回恢复区。
多个数据库的闪回恢复区可以指定到同一个位置，但是db_name不能一样，或者db_unique_name不一样。
RAC的闪回恢复区必须位于共享磁盘上，能被所有实例访问。
6.闪回恢复区的空间管理
当闪回恢复区空间不足时，alert中会有警告记录
Tue Dec 19 10:45:41 2006
Errors in file e:/oracle/ora10/admin/ning/bdump/ning_rvwr_31968.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 52428800 字节) 已使用 87.29%, 尚有6665216 字节可用。
同时，oracle在alert中还会给出解决该问题的建议
Tue Dec 19 10:45:41 2006
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
如果闪回恢复区空间耗尽，且归档路径设置到了闪回恢复区中，则由于日志无法归档，数据库会hang住。
Tue Dec 19 10:45:57 2006
Errors in file e:/oracle/ora10/admin/ning/bdump/ning_arc0_32372.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 52428800 字节) 已使用 100.00%, 尚有 0 字节可用。
Tue Dec 19 10:45:57 2006
Errors in file e:/oracle/ora10/admin/ning/bdump/ning_arc0_32372.trc:
ORA-19809: 超出了恢复文件数的限制
ORA-19804: 无法回收 6836224 字节磁盘空间 (从 52428800 限制中)
ARC0: Error 19809 Creating archive log file to
'E:/ORACLE/ORA10/FLASH_RECOVERY_AREA/NING/ARCHIVELOG/2006_12_19/O1_MF_1_250_U_.ARC'
ARC0: Failed to archive thread 1 sequence 250 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
Tue Dec 19 10:45:58 2006
Errors in file e:/oracle/ora10/admin/ning/bdump/ning_arc0_32372.trc:
ORA-16038: 日志 1 序列号 250 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 1 线程 1: 'E:/ORACLE/ORA10/ORADATA/NING/REDO01.LOG'
所以，对于生产库，如果将归档放到闪回恢复区中，需要密切关注闪回恢复区的空间使用情况，否则一旦闪回恢复区的空间用尽，将导致数据库无法提供服务。
通过查询视图v$flash_recovery_area_usage，可以获得当前闪回恢复区的空间使用情况，并且可以知道
是哪些文件占中了空间，据此可以做出相应的处理，或者加大闪回恢复区，或者移走相应的文件。
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 91 0 16
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 8 4 2
已选择6行。
另外，v$recovery_file_dest视图也提供了闪回恢复区的概要信息。
SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------------------- ----------- ---------- ----------------- ---------------
E:oracleora10flash_recovery_area 104857600 101835264 0 17
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
