Oracle调优与深入之04031处理过程

发表于:2007-05-25来源:作者:点击数: 标签:oracle调优04031过程深入
一、错误提示: > E XP -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","BA MI MA: Bam Buffe 二、错误原因: 共享内存太小,存在

  一、错误提示:
  > 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.ltesting.net