Chinaunix首页 | 论坛 | 博客
  • 博客访问: 845300
  • 博文数量: 72
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 435
  • 用 户 组: 普通用户
  • 注册时间: 2013-04-27 20:07
个人简介

郭一军,网名guoyJoe,尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com

文章分类
文章存档

2014年(72)

分类: Oracle

2014-06-16 22:57:30

ORA-4031错误时,我们通常可以根据Oracle无法分配多少字节的内存,来判断共享池碎片的严重程度,以下是4031错误官方的解释:
[oracle@guoyj ~]$ oerr ORA 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.  
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

一、重现ORA-04031错误:
1、第一个实验硬解析产生大量的碎片重现4031错误
2、当前的数据库版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
3、关闭ASMM,设置shared pool大小为120M
SQL> show parameter mem
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 804M
memory_target                        big integer 804M
shared_memory_address                integer     0
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 804M
sga_target                           big integer 0
SQL>alter system set memory_target=0;  --11g的新特性调整(SGA+PGA
SQL>startup force;
SQL> alter system set sga_target=0;
SQL>startup force;
SQL> show parameter memory_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 0
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
SQL> alter system set shared_pool_size=120M;
System altered.
4、创建表并插入数据
create table t1(id int,name varchar2(100));
begin
  for i in 1 .. 100000 loop
  insert into t1 values(i,'gyj'||i);
  commit;
end loop;
end;
/
5、跑下面匿名块模拟产生大量的硬解析
declare
    msql varchar2(500);
    mcur number;
    mstat number;
    jg varchar2(4000);
    cg number;
begin
    mcur:=dbms_sql.open_cursor;
    for i in 1..999999999 loop
      msql:='select id from gyj.t1 where id='||to_char(i);
      dbms_sql.parse(mcur,msql,dbms_sql.native);
      dbms_sql.define_column(mcur,1,jg,4000);
      mstat:=dbms_sql.execute(mcur);
     end loop;
      dbms_sql.close_cursor(mcur);
end;
/
6、这个实验可能要等好久才能看到4031错误,硬解析会占用共享池,但执行完后游标马上关闭,共享池内存也会随之释放,所以用这个实验来模拟4031错误不一定能成功,我也是偶尔做成,下面再来个实验,保证马上重现4031错误。。
*******************************************************************************
8、第二个实验:一直打开游标不关闭,直到消耗完共享池导致4031错误
SQL> show parameter open_cursor
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
alter system set open_cursors=65535;
SQL> declare
  2      msql varchar2(500);
  3      mcur number;
  4      mstat number;
  5      jg varchar2(4000);
  6      cg number;
  7  begin
  8      for i in 1..65535 loop
  9        mcur:=dbms_sql.open_cursor;
10        msql:='select id from t1 where id='||to_char(i);
11        dbms_sql.parse(mcur,msql,dbms_sql.native);
12        dbms_sql.define_column(mcur,1,jg,4000);
13        mstat:=dbms_sql.execute(mcur);
14        dbms_sql.column_value(mcur,1,jg);
15       end loop;
16  end;
17  /
declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 11
如果报这个错误,说明alter system set open_cursors=65535;设置没生效,重启库
SQL> conn / as sysdba
Connected.
SQL> startup force;
ORACLE instance started.
Total System Global Area  455163904 bytes
Fixed Size                  2229224 bytes
Variable Size             138415128 bytes
Database Buffers          310378496 bytes
Redo Buffers                4141056 bytes
Database mounted.
Database opened.
SQL> conn gyj/gyj
Connected.
再次执行
呵呵,耐心等待五分钟之内绝对报错。。。。
SQL> declare
  2      msql varchar2(500);
  3      mcur number;
  4      mstat number;
  5      jg varchar2(4000);
  6      cg number;
  7  begin
  8      for i in 1..65535 loop
  9        mcur:=dbms_sql.open_cursor;
10        msql:='select id from t1 where id='||to_char(i);
11        dbms_sql.parse(mcur,msql,dbms_sql.native);
12        dbms_sql.define_column(mcur,1,jg,4000);
13        mstat:=dbms_sql.execute(mcur);
14        dbms_sql.column_value(mcur,1,jg);
15       end loop;
16  end;
17  /
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 80 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","SQLA^337fc737","ctxPlanSig:qksctxPlanSigGet")
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 11
9、查看ORA-4031发生了几次
SQL> select indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs
  2  from  sys.x$kghlu where inst_id = userenv('Instance');
select indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA","tmp")
现在不能做任何大的查询操作了,怎么办?
先退出回话,重新登录:
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@guoyj ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 12 20:50:21 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs
  2  from  sys.x$kghlu where inst_id = userenv('Instance');
      INDX   KGHLURCR   KGHLUTRN   KGHLUFSH   KGHLUOPS   KGHLUNFU   KGHLUNFS
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0       1233      30360      57700     518988        114       4096
kghlunfu:出现ORA4031的次数,这里指出现了114次的4031错误
kghlunfs:最后一次出现ORA4031时,申请的分配大小,这里是指最后申请共享池的大小是4096字节的chunk找不到,最终报错。。。。
实际你可以监控制告警日志alert_bxocp.log,里面会显示1144031错误,为什么报这么多次错误呢,从不同的Free Lists里找空闲的chunk,最后到保留池(SHARED_POOL_RESERVED_SIZE)找,也没找到4096字节的chunk,最后在执 sql的地方报错4031错误。
10、这里说到了free listsSHARED_POOL_RESERVED_SIZE
首先说一下保留池,保留池的大小有个参数就是SHARED_POOL_RESERVED_SIZE
关于保留池的隐含参数:
SQL> @?/rdbms/admin/show_para
Enter value for p: SHARED_POOL_RESERVED
old  12:     AND upper(i.ksppinm) LIKE upper('%&p%')
new  12:     AND upper(i.ksppinm) LIKE upper('%SHARED_POOL_RESERVED%')
P_NAME                                   P_DESCRIPTION                                      P_VALUE                 ISDEFAULT ISMODIFIED ISADJ
---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----
_shared_pool_reserved_min_alloc          minimum allocation size in bytes for reserved area 4400                    TRUE      FALSE       FALSE
                                          of shared pool
_shared_pool_reserved_pct                percentage memory of the shared pool allocated for 5                       TRUE      FALSE       FALSE
                                          the reserved area
shared_pool_reserved_size                size in bytes of reserved area of shared pool      6291456                 TRUE      FALSE       FALSE
SHARED_POOL_RESERVED_SIZE,缺省是SHARED_POOL_SIZE5%
_SHARED_POOL_RESERVED_PCT缺省 5%
_SHARED_POOL_RESERVED_MIN_ALLOC,缺省4400,超过这个大小才被认为是大对象
_SHARED_POOL_RESERVED_MIN_ALLOC4400字节看出申请4096字节的chunk不会到保留池去找,只有符合申请>=4400字节的chunk才会去保留池上找空闲chunk,申请4096字节只会在Free List去找,找不到就最后报错4031错误。
那么我们来看一下共享池的Free Lists管理free内存块(Chunk),把Free Lists链的内容dump出来看一下:
alter session set events 'immediate trace name heapdump level 2';
FREE LISTS:
Bucket 0 size=32
  Chunk        074dca368 sz=       32    free      "               "
  Chunk        07b000078 sz=        0    kghdsx
Bucket 1 size=40
  Chunk        06e37cf50 sz=       40    free      "               "
  Chunk        06de531b8 sz=       40    free      "               "
  Chunk        071e9a318 sz=       40    free      "               "
  Chunk        06f24c408 sz=       40    free      "               "
   .省略...
  .
  .
Bucket 40 size=352
  .省略...
  .
  .
Bucket 48 size=416
  Chunk        0753eac30 sz=      416    free      "               "
  Chunk        070da9858 sz=      416    free      "               "
Bucket 49 size=424
  Chunk        070bf3000 sz=      424    free      "               "
  Chunk        074197e10 sz=      424    free      "               "
Bucket 50 size=432
Bucket 51 size=440
Bucket 52 size=448
  Chunk        074d45708 sz=      448    free      "               "
Bucket 53 size=456
  Chunk        06e6b1e20 sz=      456    free      "               "
  Chunk        06de86a80 sz=      456    free      "               "
  Chunk        06e344648 sz=      456    free      "               "
  Chunk        06e430e70 sz=      456    free      "               "
Bucket 54 size=464
Bucket 55 size=472
Bucket 56 size=480
Bucket 57 size=488
  Chunk        06be70bc0 sz=      488    free      "               "
Bucket 58 size=496
Bucket 59 size=504
  Chunk        06e405f48 sz=      504    free      "               "
Bucket 60 size=512
Bucket 61 size=520
Bucket 62 size=528
Bucket 63 size=536
Bucket 64 size=544
  .省略...
  .
  .
Bucket 102 size=848
Bucket 103 size=856
Bucket 104 size=864
Bucket 105 size=872
  .省略...
  .
  .
Bucket 252 size=16408
Bucket 253 size=32792
Bucket 254 size=65560
可以看出有一共有255Free List链。。。。
每条Free List挂的空闲chunk大小具有一定的范围,从小到大。。。,结合我们上面的实验,产生114次的ORA04031错误,是从这254链的某条Free List链开始找空闲chunk,直到找到最后一条为止,没找到4096字节的chunk

二、4031错误产生原因
当尝试在共享池分配大块的连续内存失败(很多时候是由于碎片过多,而并非真是内存不足),Oracle首先清除共享池中当前没使用的所有对象,使空闲内存块合并.如果仍然没有足够大的单块内存可以满足需要,就会产生ORA-04031错误.
1Oraclebug
2、没使用绑定变量,sql没有足够的共享,产生大量不必要的解析
3Shared pool设的太小
4SubPool的划分可能也会导致各分区之间的协调问题,但从Oracle10g开始,允许内存请求在不同的SubPool之间进行切换,提高进请求成功的可能,但不能切换无限次,所以问题仍然存在)
5open_cursors(0 to 65535) 设的过大

三、解决:4031错误的方法
1、刷共享池
  alter system flush shared_pool;
不过这个办法只是暂时的,治标不治本。
2、保留池的设置
参数shared_pool_reserved_size,shared_pool_reserved_min_alloc
         select REQUEST_MISSES from v$shared_pool_reserved;
 3、缓存大对象
   select * from v$db_object_cache where sharable_mem > 50000
         and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')
                   and kept = 'NO';
         执行dbms_shared_pool.keep('对象名');
         DBMS_SHARED_POOL
         @?/rdbms/admin/dbmspool.sql
 4、参数open_cursorsession_cached_cursors的设置
  5、共享SQL:应用绑定变量或修改参数cursor_sharing
      a.使用绑定变量
        declare  v_sql varchar2(100);
        begin  for i in 1..30000 loop
        v_sql := 'insert /*bind*/ into gyj.t1(id) values (:1)';  
        execute immediate v_sql using i;
        end loop;  
        commit;  
        end;
        /
      b.cursor_sharing参数
          如何找出不能共享cursorsql
         v$sql查找执行次数较小的sql语句,观察这些sql语句是否是经常执行的。
         select SQL_FULLTEXT from v$sql where EXECUTIONS=1 and sql_text like '%from gyj.t1%';
         select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text;
         alter system set cursor_sharing=SIMILAR  | FORCE;
 6、增加shared pool空间
     select sum(bytes/1024/1024) from v$sgastat where pool='shared pool';
     select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
     show parameter sga_target
     show parameter sga_max_size
     alter system set shared_pool_size=300M scope=both;

四、查共享池碎片的一个脚本
SQL> set linesize 1000
SQL> col sga_heap format a15
SQL> col size format a10
SQL> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
  2  decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
  3  4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
  4  '8-9k', 9,'9-10k','> 10K') "size",
  5  count(*),ksmchcls Status, sum(ksmchsiz) Bytes
  6  from x$ksmsp
  7  where KSMCHCOM = 'free memory'
  8  group by ksmchidx, ksmchcls,
  9  'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
10  1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
11  '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');
   SubPool SGA_HEAP        CHUNKCOMMENT     size         COUNT(*) STATUS        BYTES
---------- --------------- ---------------- ---------- ---------- -------- ----------
         1 sga heap(1,0)   free memory      > 10K              94 R-free     19837104
         1 sga heap(1,0)   free memory      3-4K               54 free         144592
         1 sga heap(1,0)   free memory      1-2K                1 R-free          680
         1 sga heap(1,0)   free memory      3-4K                1 R-free         3344
         1 sga heap(1,0)   free memory      4-5K              151 free         606936
         1 sga heap(1,0)   free memory      8-9k                3 free          24576
         1 sga heap(1,0)   free memory      4-5K                2 R-free         7336
         1 sga heap(1,0)   free memory      0-1K            17490 free        2029992
         1 sga heap(1,0)   free memory      1-2K               11 free           5944
9 rows selected.





**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name:    guoyJoe

QQ:        252803295

Email:    oracledba_cn@hotmail.com

Blog:      http://blog.csdn.net/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

OCM:    
 _____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!

答案在:http://blog.csdn.net/guoyjoe/article/details/8624392

Oracle@Paradise  总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036


阅读(2192) | 评论(0) | 转发(0) |
1

上一篇:没有了

下一篇:重现ORA-01555错误

给主人留下些什么吧!~~