熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 15:54:03  來源: Oracle 

  錯誤提示
  > EXP: ORACLE error encountered
  > ORA: unable to allocate bytes of shared memory
  (shared poolBEGIN :EXEC_STR := SYSDBMSPL/SQL MPCODEBAMIMA: Bam Buffe
  
  錯誤原因
  共享內存太小存在一定碎片沒有有效的利用保留區造成無法分配合適的共享區
  
  解決步驟
  查看當前環境
  SQL>  show sga
  
  Total System Global Area  bytes
  Fixed Size           bytes
  Variable Size        bytes
  Database Buffers      bytes
  Redo Buffers         bytes
  
  show parameter shared_pool
  NAME                 TYPE  VALUE
  
  shared_pool_reserved_size      string 
  shared_pool_size           string 
  
  SQL> select sum(free_space) from v$shared_pool_reserved;
  
  SUM(FREE_SPACE)
  
  
  
  我們可以看到沒有合理利用保留區
  
  SQL> SELECT SUM(RELOADS)/SUM(PINS) FROM V$LIBRARYCACHE;
  
  SUM(RELOADS)/SUM(PINS)
  
  
  
  不算太嚴重
  SQL> SELECT round((BValue/AValue)*) hardpaseperc
  FROM V$SYSSTAT A
  V$SYSSTAT B
  WHERE AStatistic# =
  AND BStatistic# =
  AND ROWNUM = ;
  
  hardpaseperc
  
  
  
  查看保留區使用情況
  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
  
                            
  
  最近一次申請共享區失敗時該對象需要的共享區大小
  
  select name from v$db_object_cache where sharable_mem = ;
  name
  
  dbms_lob
  
   dbms_lob正是exp時申請保留區的對象
  查看導致換頁的應用
  SQL> select * from x$ksmlru where ksmlrsiz>;
  
  ADDR   INDX  INST_ID KSMLRCOM   KSMLRSIZ KSMLRNUM KSMLRHON KSMLROHV KSMLRSES
  
  A       BAMIMA: Bam Buffer      DBMS_DDL DEBC
  
  ACC       BAMIMA: Bam Buffer      DBMS_SYS_SQL D
  
  B       BAMIMA: Bam Buffer     STANDARD DE
  
  B       BAMIMA: Bam Buffer     DBMS_LOB DAC
  
  B       BAMIMA: Bam Buffer     DBMS_UTILITY CFC
  
  BDC       BAMIMA: Bam Buffer     begin : := dbms_lobgetLeng  CFFCC
  
  C       state objects       
  
  C       library cache         EXUVEW  CC
  
  CA       state objects         CB
  
  CEC       state objects        D
  
  分析各共享池的使用情況
  SQL> select KSPPINMKSPPSTVL
  from x$ksppi
  x$ksppcv
  where x$ksppiindx = x$ksppcvindx
  and KSPPINM = _shared_pool_reserved_min_alloc;
  
  KSPPINM     KSPPSTVL
   
  _shared_pool_reserved_min_alloc    (門值)
  
  我們看到INDX=DBMS_LOB造成換頁(就是做exp涉及到lob對象處理造成的換頁情況)換出
  最近未使用的內存但是換出內存並合並碎片後在共享區仍然沒有合適區來存放數據說明共享
  區小和碎片過多然後根據_shared_pool_reserved_min_alloc的門值來申請保留區而門值為
  所以不符合申請保留區的條件造成錯誤我們前面看到保留區全部為空閒狀態所以我們可以
  減低門值使更多申請共享內存比小的的對象能申請到保留區而不造成錯誤
  
  解決辦法
  )增大shared_pool (在不DOWN機的情況下不合適)
  )打patch  (在不DOWN機的情況下不合適)
  )減小門值 (在不DOWN機的情況下不合適)
  因為LAST_FAILURE_SIZE<_shared_pool_reserved_min_alloc所以表明沒有有效的使用保留區
  SQL> alter system set _shared_pool_reserved_min_alloc = ;
  alter system set _shared_pool_reserved_min_alloc=
  *
  ERROR at line :
  ORA: specified initialization parameter cannot be modified
  
   i的使用方法alter system set _shared_pool_reserved_min_alloc= scope=spfile;
  
  )使用alter system flush shared_pool; (不能根本性的解決問題)
  )使用dbms_shared_poolkeep
  
  由於數據庫不能DOWN機所以只能選擇)和)
  運行dbmspoolsql
  SQL> @/home/oracle/products//rdbms/admin/dbmspoolsql
  找出需要keep到共享內存的對象
  
  SQL> select aOWNER
  aname
  asharable_mem
  akept
  aEXECUTIONS
  baddress
  bhash_value
  from v$db_object_cache a
  v$sqlarea b
  where akept = NO and
  (( aEXECUTIONS >
  and aSHARABLE_MEM > )
  or aEXECUTIONS > )
  and SUBSTR(bsql_text) = SUBSTR(aname);
  OWNER  NAME            SHARABLE_MEM KEP EXECUTIONS ADDRESS HASH_VALUE
    
  SELECT COUNT(OBJECT_ID)       NO    BF
  FROM ALL_OBJECTS
  WHERE OBJECT_NAME = :b
  AND OWNER = :b
  
  STANDARD              NO 
  DBMS_LOB            NO 
  DBMS_LOB            NO 
  DBMS_LOB            NO 
  DBMS_PICKLER            NO 
  DBMS_PICKLER             NO 
  
  SQL> execute dbms_shared_poolkeep(STANDARD);
  SQL> execute dbms_shared_poolkeep(BFC);
  SQL> execute dbms_shared_poolkeep(DBMS_LOB);
  SQL> execute dbms_shared_poolkeep(DBMS_PICKLER);
  SQL> select OWNER name sharable_memkeptEXECUTIONS from v$db_object_cache where kept = YES ORDER BY sharable_mem;
  SQL> alter system flush shared_pool;
  System altered
  
  SQL> SELECT POOLBYTES FROM V$SGASTAT WHERE NAME =free memory;
  
  POOL       BYTES
  
  shared pool  
  large pool   
  java pool    
  
  [oracle@alisolution oracle]$ sh /home/oracle/admin/dbexpsh
  
  [oracle@alisolution oracle]$ grep ORA /tmp/exptmp
  未發現錯誤導出數據成功
  
  建議
  由於以上解決的方法是在不能DOWN機的情況下所以沒能動態修改初始化參數
  但問題的本質是共享區內存過小需要增加shared pool使用綁定變量才能根本
  的解決問題所以需要在適當的時候留出DOWN機時間對內存進行合理的配置

From:http://tw.wingwit.com/Article/program/Oracle/201311/17486.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.