Oracle調優與深入之04031處理過程

　　 
　　一、錯誤提示：
　　> 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機時間，對內存進行合理的配置。

原文轉自：http://www.anti-gravitydesign.com