@@@客户ORA-04031错误分析与建议

关键字：
ORA-04031

作者：
jlandzpa

一、问题描述：

数据库版本：Oracle9.2.0.8 双机
操作系统版本：AIX 5300-09

Error 500:初始化ID为***_list的多记录表格发生异常！
java.sql.SQLException:ORA-04031:unable to allocate 4096 bytes of shared memory("shared pool","select count(*) from v_view1_...","sql area","BAMIMA:Bam Buffer")

[@more@]

二、处理分析过程：

1、检查相关告警日志文件。

分析与结论：数据库警告文件中近期没有ORA-04031及其相关报错。

2、查看数据库内存参数：
SQL> select count(1) from v$open_cursor;
COUNT(1)
----------
11044
SQL> select * from v$sysstat where name='opened cursors current';
STATISTIC# NAME CLASS VALUE
---------- -------- ---------- ----------
3 opened cursors current 1 7506

SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 800
session_cached_cursors integer 200

SQL> show sga
Total System Global Area 3948908928 bytes Fixed Size 745856 bytes Variable Size 1241513984 bytes Database Buffers 2701131776 bytes Redo Buffers 5517312 bytes

SQL> show parameter pool
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM ---------------------------- ----------- -------------------------- buffer_pool_keep string buffer_pool_recycle string global_context_pool_size string java_pool_size big integer 134217728 large_pool_size big integer 67108864 olap_page_pool_size integer 33554432 shared_pool_reserved_size big integer 41943040 shared_pool_size big integer 838860800

SQL> show parameter sga
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
---------------------------- ----------- --------------------------
lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 3948908928

3、实例效率分析：
NODE1：
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.32 In-memory Sort %: 100.00
Library Hit %: 99.70 Soft Parse %: 87.75
Execute to Parse %: 98.75 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 36.82 % Non-Parse CPU: 98.91

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.22 91.92
% SQL with executions>1: 49.72 52.94
% Memory for SQL w/exec>1: 40.15 41.26

NODE2：
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.99 In-memory Sort %: 100.00
Library Hit %: 97.77 Soft Parse %: 78.26
Execute to Parse %: 93.88 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 42.74 % Non-Parse CPU: 97.92

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.37 93.35
% SQL with executions>1: 48.06 43.69
% Memory for SQL w/exec>1: 39.77 36.94

4、检查OCR和VOTE盘的权限：

分析与结论：磁盘权限正常。

5、总结：
ORA-04031一般是由共享内存太小，存在一定碎片，没有有效的利用保留区，造成无法分配合适的共享区。常见的出现原因如下：
A、没有绑定变量造成shared_pool碎片过多，同时shared_pool_size太小。建议使用绑定变量，或者简单的加大shared_pool。或者临时解决方法就是alter system flush shared_pool。
B、shared_pool太小造成的：
--这个通过错误信息的提示很容易判断(Ora-04031 cannot allocate .. memeory in [large_pool])
--解决方法就是简单的加大 Large_pool or Java_pool or Shared_pool
C、过度的开CURSOR而不关闭。
D、有时候一些BUG也可能引发ORA-04031,但是在高版本中已经很少出现(>=8174)。

分析与结论：本case中，ORA-04031属B类偶发现象，事后，无法再现相应的报错。

三、解决方法建议：

1、若系统再出现在类似故障，临时应急的解决办法为：alter system flush shared_pool;
2、加大shared_pool(需要重启数据库)：
alter system set shared_pool_size=1200M scope=spfile;
alter system set SHARED_POOL_RESERVED_SIZE=120M scope=spfile;
已经修改了，但没有重启生效。
3、若以上两步都做过，仍有此状态，则需要找出对应的sql，进行sql级别的优化。目前这个错误很少出现。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25152497/viewspace-1054777/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25152497/viewspace-1054777/