某客户Oracle数据库出现ORA-04031错误分析与建议

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:355

关键字:
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级别的优化。目前这个错误很少出现。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】