ORA-04031: unable to allocate 4096 bytes of shared memory 　("shared pool","BEGIN_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2947 | 回复: 0

主题： ORA-04031: unable to allocate 4096 bytes of shared memory 　("shared pool","BEGIN

white

注册用户

等级：少校
经验：1327
发帖：305
精华：0
注册：2011-7-21
状态：离线
发送短消息息给white

加好友发送短消息息给white

发消息

发表于：

2015-5-22 11:12:58 | [全部帖] [楼主帖]

楼主

EXP-00008: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4096 bytes of shared memory
("shared pool","BEGIN :EXEC_STR := SYS.DBMS...","PL/SQL MPCODE","BAMIMA: Bam Buffe

二、错误原因：
共享内存太小，存在一定碎片，没有有效的利用保留区，造成无法分配合适的共享区。

三、解决步骤：
1.查看当前环境

SQL>  show sga
Total System Global Area 566812832 bytes
Fixed Size          73888 bytes
Variable Size       28811264 bytes
Database Buffers     536870912 bytes
Redo Buffers        1056768 bytes
show parameter shared_pool
NAME                 TYPE  VALUE
------------------------------------ ------- -----
shared_pool_reserved_size      string 1048576
shared_pool_size           string 20971520
SQL> select sum(free_space) from v$shared_pool_reserved;
SUM(FREE_SPACE)
---------------
1048576

我们可以看到没有合理利用保留区

SQL> SELECT SUM(RELOADS)/SUM(PINS) FROM V$LIBRARYCACHE;
SUM(RELOADS)/SUM(PINS)
----------------------
.008098188

不算太严重

SQL> SELECT round((B.Value/A.Value)*100,1) hardpaseperc
FROM V$SYSSTAT A,
V$SYSSTAT B
WHERE A.Statistic# = 171
AND B.Statistic# = 172
AND ROWNUM = 1;
hardpaseperc
------------------
26.5

2.查看保留区使用情况

SQL> SELECT FREE_SPACE,
FREE_COUNT,
REQUEST_FAILURES,
REQUEST_MISSES,
LAST_FAILURE_SIZE
FROM V$SHARED_POOL_RESERVED;
FREE_SPACE FREE_COUNT REQUEST_FAILURES REQUEST_MISSES LAST_FAILURE_SIZE
---------- ---------- ---------------- -------------- -----------------
1048576 1 146 0 4132

最近一次申请共享区失败时该对象需要的共享区大小4132

select name from v$db_object_cache where sharable_mem = 4132;
name
----------------
dbms_lob
-- dbms_lob正是exp时申请保留区的对象

3.查看导致换页的应用

SQL> select * from x$ksmlru where ksmlrsiz>0;
ADDR   INDX  INST_ID KSMLRCOM   KSMLRSIZ KSMLRNUM KSMLRHON KSMLROHV KSMLRSES
50001A88 0     1 BAMIMA: Bam Buffer 4100     64 DBMS_DDL 402745060 730DEB9C
50001ACC 1     1 BAMIMA: Bam Buffer 4108    736 DBMS_SYS_SQL 1909768749 730D0838
50001B10 2     1 BAMIMA: Bam Buffer 4112    1576 STANDARD 2679492315 730D7E20
50001B54 3     1 BAMIMA: Bam Buffer 4124    1536 DBMS_LOB 853346312 730DA83C
50001B98 4     1 BAMIMA: Bam Buffer 4128    3456 DBMS_UTILITY 4041615653 730C5FC8
50001BDC 5     1 BAMIMA: Bam Buffer 4132    3760 begin :1 := dbms_lob.getLeng... 2942875191 730CFFCC
50001C20 6     1 state objects    4184    1088 0 00
50001C64 7     1 library cache    4192    488 EXU8VEW  2469165743 730C1C68
50001CA8 8     1 state objects    4196     16 0 730C0B90
50001CEC 9     1 state objects    4216    3608 0 730D0838

3.分析各共享池的使用情况

SQL> select KSPPINM,KSPPSTVL
from x$ksppi,
x$ksppcv
where x$ksppi.indx = x$ksppcv.indx
and KSPPINM = '_shared_pool_reserved_min_alloc';
KSPPINM     KSPPSTVL
-------------------------------  --------
_shared_pool_reserved_min_alloc  4400  --(门值)

我们看到INDX=5,DBMS_LOB造成换页(就是做exp涉及到lob对象处理造成的换页情况),换出最近未使用的内存,但是换出内存并合并碎片后在共享区仍然没有合适区来存放数据,说明共享区小和碎片过多，然后根据_shared_pool_reserved_min_alloc的门值来申请保留区,而门值为4400,所以不符合申请保留区的条件,造成4031错误。我们前面看到保留区全部为空闲状态,所以我们可以减低门值，使更多申请共享内存比4400小的的对象能申请到保留区，而不造成4031错误。

4.解决办法：
1).增大shared_pool （在不DOWN机的情况下不合适）
2).打patch （在不DOWN机的情况下不合适）
3).减小门值（在不DOWN机的情况下不合适）
因为LAST_FAILURE_SIZE<_shared_pool_reserved_min_alloc所以表明没有有效的使用保留区

SQL> alter system set "_shared_pool_reserved_min_alloc" = 4000;
alter system set "_shared_pool_reserved_min_alloc"=4000
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
-- 9i的使用方法alter system set "_shared_pool_reserved_min_alloc"=4000 scope=spfile;

4).使用alter system flush shared_pool; (不能根本性的解决问题)
5).使用dbms_shared_pool.keep

5.由于数据库不能DOWN机，所以只能选择3)和4)
运行dbmspool.sql

SQL> @/home/oracle/products/8.1.7/rdbms/admin/dbmspool.sql

找出需要keep到共享内存的对象

SQL> select a.OWNER,
a.name,
a.sharable_mem,
a.kept,
a.EXECUTIONS ,
b.address,
b.hash_value
from v$db_object_cache a,
v$sqlarea b
where a.kept = 'NO' and
(( a.EXECUTIONS > 1000
and a.SHARABLE_MEM > 50000)
or a.EXECUTIONS > 10000)
and SUBSTR(b.sql_text,1,50) = SUBSTR(a.name,1,50);
OWNER  NAME            SHARABLE_MEM KEP EXECUTIONS ADDRESS HASH_VALUE
------- ----------------------—--- ------------ --- ---------- -------- ----------
SELECT COUNT(OBJECT_ID)   98292    NO  103207  74814BF8 1893309624
FROM ALL_OBJECTS
WHERE OBJECT_NAME = :b1
AND OWNER = :b2
STANDARD          286632    NO  13501
DBMS_LOB          98292  NO  103750
DBMS_LOB        47536    NO  2886542
DBMS_LOB        11452    NO  2864757
DBMS_PICKLER        10684    NO  2681194
DBMS_PICKLER        5224     NO  2663860
SQL> execute dbms_shared_pool.keep('STANDARD');
SQL> execute dbms_shared_pool.keep('74814BF8,1893309624','C');
SQL> execute dbms_shared_pool.keep('DBMS_LOB');
SQL> execute dbms_shared_pool.keep('DBMS_PICKLER');
SQL> select OWNER, name, sharable_mem,kept,EXECUTIONS from v$db_object_cache where kept = 'YES' ORDER BY sharable_mem;
SQL> alter system flush shared_pool;
System altered.
SQL> SELECT POOL,BYTES FROM V$SGASTAT WHERE NAME ='free memory';
POOL       BYTES
----------- ----------
shared pool  7742756
large pool   614400
java pool    32768
[oracle@ali-solution oracle]$ sh /home/oracle/admin/dbexp.sh
[oracle@ali-solution oracle]$ grep ORA- /tmp/exp.tmp

未发现错误，导出数据成功

四、建议：
由于以上解决的方法是在不能DOWN机的情况下，所以没能动态修改初始化参数，但问题的本质是共享区内存过小，需要增加shared pool，使用绑定变量，才能根本的解决问题，所以需要在适当的时候留出DOWN机时间，对内存进行合理的配置。

--转自