ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 全面解析ORA-4031错误

全面解析ORA-4031错误

原创 Linux操作系统 作者:258646730 时间:2013-08-13 14:21:50 0 删除 编辑

  报ORA-4031错误时,我们通常可以根据Oracle无法分配多少字节的内存,来判断共享池碎片的严重程度,以下是4031错误官方的解释:

  [oracle@guoyj ~]$ oerr ORA 4031 oracle培训

  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:出现ORA-4031的次数,这里指出现了114次的4031错误

  kghlunfs:最后一次出现ORA-4031时,申请的分配大小,这里是指最后申请共享池的大小是4096字节的chunk找不到,最终报错。。。。

  实际你可以监控制告警日志alert_bxocp.log,里面会显示114次4031错误,为什么报这么多次错误呢,从不同的Free Lists里找空闲的chunk,最后到保留池(SHARED_POOL_RESERVED_SIZE)找,也没找到4096字节的chunk,最后在执 行sql的地方报错4031错误。

  10、这里说到了free lists和SHARED_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_SIZE的5%

  _SHARED_POOL_RESERVED_PCT缺省 5%

  _SHARED_POOL_RESERVED_MIN_ALLOC,缺省4400,超过这个大小才被认为是大对象

  从_SHARED_POOL_RESERVED_MIN_ALLOC=4400字节看出申请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

  可以看出有一共有255个Free List链。。。。

  每条Free List挂的空闲chunk大小具有一定的范围,从小到大。。。,结合我们上面的实验,产生114次的ORA-04031错误,是从这254链的某条Free List链开始找空闲chunk,直到找到最后一条为止,没找到4096字节的chunk。

  二、4031错误产生原因

  当尝试在共享池分配大块的连续内存失败(很多时候是由于碎片过多,而并非真是内存不足)时,Oracle首先清除共享池中当前没使用的所有对象,使空闲内存块合并.如果仍然没有足够大的单块内存可以满足需要,就会产生ORA-04031错误.

  1、Oracle的bug

  2、没使用绑定变量,sql没有足够的共享,产生大量不必要的解析

  3、Shared pool设的太小

  4、SubPool的划分可能也会导致各分区之间的协调问题,但从Oracle10g开始,允许内存请求在不同的SubPool之间进行切换,提高进请求成功的可能,但不能切换无限次,所以问题仍然存在)

  5、open_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_cursor、session_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参数

  如何找出不能共享cursor的sql

  在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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29104442/viewspace-768384/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-08-08

  • 博文量
    11
  • 访问量
    16605