诊断和解决ORA-04031错误

Oracle 9iR2开始引入了段级统计信息（segment statistics）收集的新特性，其中一个新引入的视图是v$segstat，查询该视图会引发Shared Pool的内存泄露（在9201～9206版本中都存在此问题，本案例测试来自Windows平台9206），我们可以利用这一问题来模拟ORA-00431错误（该Bug在Oracle 10g中已经修正，所以以下方法并不适用于Oracle 10g）。

以下是一段测试代码：

set heading off
column what format a40
column value format a30
 
select 'db instance' what, user || '@' || global_name value from global_name
UNION
select '# rows in v$segstat', to_char(count(*)) from v$segstat;
 
set linesize 200
set time on
set serveroutput on size 300000
 
declare
  l_temp        char(1);
  l_before      number;
  l_after       number := 0;
  l_loop_times  pls_integer := 1000;    -- try 1000
  l_sleep       number   := 0.00;    -- makes no difference
 
  cursor c_seg is select * from v$segstat;
  r_seg  c_seg%ROWTYPE;
 
   function get_mem return number is
     cursor c_mem is select bytes from v$sgastat
        where name = 'free memory' and pool = 'shared pool';
     r_mem   c_mem%ROWTYPE;
     begin
       open c_mem; fetch c_mem into r_mem; close c_mem;
       return r_mem.bytes;
     end get_mem;
begin
  l_after := get_mem();  
 
  for x in 1..l_loop_times loop
    l_before := l_after;
 
    OPEN c_seg; FETCH c_seg INTO r_seg; CLOSE c_seg;
 
    l_after := get_mem();
    dbms_output.put_line ('Loop ' || x || ': (' ||
       to_char(sysdate,'hh24:mi:ss') || ') from ' ||
       to_char(l_before,'999,999,999') || ' to ' ||
       to_char(l_after,'999,999,999') || ' (loss of ' ||
       to_char((l_before-l_after),'9,999,999') || ')');
    dbms_lock.sleep(l_sleep);
  end loop;
end;
/

首先来看看之前的状态（测试环境，已经把Shared Pool调整降低）：

SQL> select * from v$sgastat
  2  where name in('miscellaneous','free memory') and pool='shared pool';
POOL        NAME                    BYTES
----------- -------------------------- ----------
shared pool free memory                   3927884
shared pool miscellaneous                  5752896
执行以上代码：
20:49:57 SQL> @d:\mem.leak.sql
 
# rows in v$segstat                      1034
db instance                              SYS@EYGLE
 
Loop 1: (20:50:00) from      782,072 to      769,336 (loss of     12,736)
Loop 2: (20:50:00) from      769,336 to      949,276 (loss of   -179,940)
Loop 3: (20:50:00) from      949,276 to      978,872 (loss of    -29,596)
Loop 4: (20:50:00) from      978,872 to      970,436 (loss of      8,436)
Loop 5: (20:50:00) from      970,436 to      962,012 (loss of      8,424)
Loop 6: (20:50:00) from      962,012 to      949,364 (loss of     12,648)
Loop 7: (20:50:00) from      949,364 to      946,280 (loss of      3,084)
Loop 8: (20:50:00) from      946,280 to      993,064 (loss of    -46,784)
Loop 9: (20:50:00) from      993,064 to      984,640 (loss of      8,424)
Loop 10: (20:50:00) from      984,640 to    1,092,628 (loss of   -107,988)
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 4212 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","obj stat memor")
ORA-06512: at line 26
 
20:50:10 SQL>  alter session set events 'immediate trace name heapdump level 2';
 
Session altered.
20:50:20 SQL> select * from v$sgastat
20:50:20   2  where name in('miscellaneous','free memory') and pool='shared pool';
 
shared pool free memory                    888268
shared pool miscellaneous                 10144656

转储共享内存，可以看到：

……
Bucket 248 size=3948
  Chunk 7b69b5a0 sz=     3956    free      "               "
  Chunk 7b6d16b4 sz=     3956    free      "               "
 Bucket 249 size=4012
  Chunk 7b5da178 sz=     4064    free      "               "
  Chunk 7b66360c sz=     4060    free      "               "
  Chunk 7b554204 sz=     4060    free      "               "
  Chunk 7b6e2af4 sz=      4060    free      "               "
  Chunk 7b76b8b4 sz=     4052    free      "               "
 Bucket 250 size=4108
 Bucket 251 size=8204
 Bucket 252 size=16396
 Bucket 253 size=32780
 Bucket 254 size=65548
Total free space   =   180396

当前最大的Chunk size是4052，所以请求4212 时出现了04031错误。

如果系统的ORA-04031错误通常都是在4200左右出现，如前文提到的，可以通过修改_shared_pool_reserved_min_alloc参数设置减少04031错误的出现，可以比较一下。

（1）缺省情况下，_shared_pool_reserved_min_alloc = 4400，在04031错误情况下，来看一下保留池的使用：

RESERVED FREE LIST:
  Chunk 7a000038 sz=    85940  R-free      "               "
  Chunk 7a400038 sz=    85940  R-free      "               "
  Chunk 7a800038 sz=    85940  R-free      "               "
  Chunk 7ac00038 sz=    85940  R-free      "               "
  Chunk 7b000038 sz=    85940  R-free      "               "
  Chunk 7b400038 sz=    85940  R-free      "               "
Total reserved free space   =   515640

保留池保留了85940的Chunk Size，未被使用。

（2）修改_shared_pool_reserved_min_alloc = 4100，在04031错误情况下，来看一下保留池的使用。

修改方式如下，修改参数后需要重新启动数据库：

21:01:43 SQL> alter system set "_shared_pool_reserved_min_alloc"=4100 scope=spfile;
System altered.

修改后保留池的使用情况：

RESERVED FREE LIST:
  Chunk 7b414994 sz=     1624  R-free      "               "
  Chunk 7b014994 sz=     1624  R-free      "               "
  Chunk 7ac14988 sz=     1636  R-free      "               "
  Chunk 7a814994 sz=     1624  R-free      "               "
  Chunk 7a414988 sz=     1636  R-free      "               "
  Chunk 7a014994 sz=     1624  R-free      "               "
Total reserved free space   =     9768

可以看到，在修改了_shared_pool_reserved_min_alloc参数以后，保留池的使用更为充分，从而使得ORA-04031错误的出现得以延迟。

提示：本例提供一种方法模拟ORA-04031错误，你可以在测试环境中模拟和研究04031问题，但是严禁在生产环境中使用。