oraclerac共享内存错误

1、ora-04031: unable to allocate 4096 bytes of shared memory (shared pool,begin:exec_str := sys.dbms.,pl/sql mpcode,bamima: bam buffe 二、错误原因：共享内存太小，存在一定碎片，没有有效的利用保留区，造成无法分配合适的共享区。三、解决步骤：1.查看当前环境sqlshow sga total system global area566812832 bytes fixed size73888 bytes variable size28811264 bytes databas

2、e buffers536870912 bytes redo buffers1056768 bytes show parameter shared_pool nametypevalue - - - shared_pool_reserved_sizestring1048576 shared_pool_sizestring20971520 sql select sum(free_space) from v$shared_pool_reserved; sum(free_space) - 1048576 我们可以看到没有合理利用保留区sql select sum(reloads)/sum(pins) f

3、rom 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.查看保留区使用情况sqlselect free_space, free_count, request_failures, request_

4、misses, last_failure_size from v$shared_pool_reserved; free_space free_count request_failures request_misses last_failure_size - - - - - 1048576114604132 最近一次申请共享区失败时该对象需要的共享区大小4132 select name from v$db_object_cache where sharable_mem = 4132; name - dbms_lob - dbms_lob 正是 exp 时申请保留区的对象3.查看导致换页的应用sq

5、l select * from x$ksmlru where ksmlrsiz0; addrindxinst_id ksmlrcomksmlrsizksmlrnum ksmlrhon ksmlrohv ksmlrses 50001a88 01 bamima: bam buffer410064 dbms_ddl 402745060 730deb9c 50001acc 11 bamima: bam buffer4108736 dbms_sys_sql 1909768749 730d0838 50001b10 21 bamima: bam buffer41121576 standard 267949

6、2315 730d7e20 50001b54 31 bamima: bam buffer41241536 dbms_lob 853346312 730da83c 50001b98 41 bamima: bam buffer41283456 dbms_utility 4041615653 730c5fc8 50001bdc 51 bamima: bam buffer41323760 begin :1 := dbms_lob.getleng.2942875191 730cffcc 50001c20 61 state objects41841088 0 00 50001c64 71 library

7、cache4192488 exu8vew2469165743 730c1c68 50001ca8 81 state objects419616 0 730c0b90 50001cec 91 state objects42163608 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; ksppinmksppstvl - _shared_

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

9、shared_pool （在不 down 机的情况下不合适）2).打 patch（在不 down 机的情况下不合适）3).减小门值（在不 down 机的情况下不合适）因为last_failure_size 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 -

10、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.na

11、me, 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) ora.executions 10000) and substr(b.sql_text,1,50) = substr(,1,50); ownernamesharable_mem kep executions address hash_valu

12、e - - - - - - select count(object_id)98292no10320774814bf8 1893309624 from all_objects where object_name = :b1 and owner = :b2 standard286632no13501 dbms_lob98292no103750 dbms_lob47536no2886542 dbms_lob11452no2864757 dbms_pickler10684no2681194 dbms_pickler5224no2663860 sql execute dbms_shared_pool.k

13、eep(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; poolbytes - - shared pool7742756 large pool614400 java pool32768 oracleali-solution oracle$ s