ORA-4031错误深入解析

报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：出现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.