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 二、錯誤原因：共享內存太小，存在一定碎片，沒有有效的利用保留區(qū)，造成無法分配合適的共享區(qū)。三、解決步驟：1.查看當前環(huán)境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 我們可以看到沒有合理利用保留區(qū)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.查看保留區(qū)使用情況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 最近一次申請共享區(qū)失敗時該對象需要的共享區(qū)大小4132 select name from v$db_object_cache where sharable_mem = 4132; name - dbms_lob - dbms_lob 正是 exp 時申請保留區(qū)的對象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 對象處理造成的換頁情況),換出最近未使用的內存,但是換出內存并合并碎片后在共享區(qū)仍然沒有合適區(qū)來存放數據,說明共享區(qū)小和碎片過多，然后根據_shared_pool_reserved_min_alloc的門值來申請保留區(qū),而門值為 4400, 所以不符合申請保留區(qū)的條件,造成4031 錯誤。我們前面看到保留區(qū)全部為空閑狀態(tài),所以我們可以減低門值，使更多申請共享內存比4400 小的的對象能申請到保留區(qū)，而不造成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