共享池之十：模拟ORA-4031错误

ORA-4031错误

ORA-04031错误就是因不能在分配连续的内存,可能是共享池内存碎片严重，也可能是确实内存不足。
引起这种情况的原因有：频繁更解析--需要内存空间多，并且容易产生更严重的内存碎片。
session_cached_cursors太高，被缓存的游标过多--占用大量library cache内存，有可能引发ORA-4031。

共享池碎片产生举例：

在报ORA-4031错误的时候，有可能使用下面的SQL语句查看，发现可用的内存还是足够大的，
    SQL> select POOL,NAME,BYTES from v$sgastat whereNAME='free memory';
    事实上，在Oracle发出4031错误之前，已经释放了不少recreatable类型的chunk了，因此会产生很多可用内存。但是这些可用的chunk中，没有一个chunk能够以连续的物理内存提供所需的内存空间
在shared pool中有一块保留区域，通过参数shared_pool_reserved_size设置，默认是shared pool空间的5%。
SQL>show parameter shared_pool_reserved_size
SQL>select request_misses from v$shared_pool_reserved;
这个参数反映了无法从保留区域获得足够大空间的chunk的总次数，尽量让这个参数为0.
对于非常大的对象，一般从这个区域中分配空间
这是一块独立管理的区域，这个区域中的chunk不会挂接到普通区域的链表中，普通区域的chunk也不会挂接到这个区域的链表中

SQL语句要缓存在共享池CHUNK中，假设SQL语句的文本、执行计划共需要4K。
在找可用CHUNK时，首先进入相应bucket进去查找，
如未找到4K大小CHUNK，则转向下一个非空bucket，假设找到一个5K的CHUNK;
此时会4K用来存放此SQL语句相关文本、执行计划，剩余1K成为一个新CHUNK并进入相应的bucket,及FREE LIST列表。
长此以往，可能产生大量1K/2K等小CHUNK，总空间很大，但是如果SQL语句需要比如4K的CHUNK，就无法请求到所需的CHUNK，产生共享池碎片，引起ORA-04031错误。
查看共享池中CHUNK SIZE情况，如果<1K比较多，可能就碎片严重。 ---通常每个bucket上的chunk多于2000个，就认为共享池碎片过多。
col sga_heap format a15
col size format a10
select KSMCHIDX "SubPool", 'sgaheap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
'8-9k', 9,'9-10k','> 10K') "size",
count(*),ksmchcls Status, sum(ksmchsiz) Bytes
from x$ksmsp
where KSMCHCOM = 'free memory'
group by ksmchidx, ksmchcls,
'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');
###################

前台进程在共享池中查找CHUNK步骤：

1.首先查找子堆 free list
2.查找子堆lru
3.从父堆中分配chunk,做为子堆新 extent
4.父堆查找free list
5.父堆查找LRU
6.使用hide free space
7.如以上步骤失败，报0RA-04031
父堆需要持有shared pool latch
子堆的操作，有mutex / library cache pin保护

模拟ORA-04031错误：

首先要将修改open_cursors 参数
BYS@ bys3>show parameter cursors
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                        integer     50
session_cached_cursors              integer     2
BYS@ bys3>alter system set open_cursors =50000;
System altered.
BYS@ bys3>show parameter shared_pool
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size           big integer 7M
shared_pool_size                    big integer 176M
重新登陆会话：
只打开游标，不关闭：select deptno from dept where deptno= 这语句的对象字段要能执行
declare
msql varchar2(100);
mcur number;
mstat number;
jg varchar2(2000);
cg number;
begin for i in 1..10000 loop
mcur:=dbms_sql.open_cursor;
msql:='select deptno from dept where deptno='||i;
dbms_sql.parse(mcur,msql,dbms_sql.native);
mstat:=dbms_sql.execute(mcur);
end loop;
end;
/
报错：
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","SQLA","tmp")
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 10
也可能报错：主要是unable to allocate1040 bytes 这里的有时不一样。上一个16bytes可能是语句确实需要16字节，但是从父堆分配区，需要按一个CHUNK分配，具体是多大CHUNK，没显示出来。
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 1040 bytes of shared memory ("sharedpool","select
o","PLDIA^191e0a8d","PAR.C:parapt:Page")
ORA-06508: PL/SQL: could not find program unit being called:"SYS.DBMS_SQL"
ORA-06512: at line 8
执行其它查询也报错：
BYS@ bys3>select * from dept where deptno=44;
select * from dept where deptno=44
             *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 136 bytes of shared memory ("sharedpool","select /*+ rule */
bucket_cn...","SQLA^337fc737","kccdef: qkxrMemAlloc")


Elapsed: 00:00:10.99
BYS@ bys3>show parameter shared_pool
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknown
object","KGLH0^92c529c4","kglHeapInitialize:temp")

警告日志中也可以看到大量的报错日志：

Mon Feb 03 20:20:44 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_mmon_10113.trc (incident=138413):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KGLH0^ca490471","kglHeapInitialize:temp")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_mmon_10113.trc (incident=138414):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KGLH0^ca490471","kglHeapInitialize:temp")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
[oracle@bys3 ~]$ tail -n 50 alert_bys3.log
Mon Feb 03 20:20:14 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_10679.trc (incident=134643):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KKSSP^37","kglseshtSegs")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_10679.trc:
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool","unknownobject","KKSSP^37","kglseshtSegs")
…………………………
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_cjq0_10175.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 528 bytes of shared memory ("sharedpool","PROPS$","PLDIA^191e0a8d","PAR.C:parapt:Page")

4031错误临时解决方案是：杀掉一部分会话或者flush shared_pool;

SYS@bys3>select sid,count(*) from v$open_cursor group by sid;
       SID   COUNT(*)
---------- ----------
       30          7
        1          2
       28          1
       13          3
       31          4
       14          2
       35       3014
       33          3
       15          7
       16          5
select sid,machine from v$session;
根据查出的机器来划分，KILL掉不太重要的会话

以SYS登陆，做flushshared_pool;

---不建议，刷新共享池后，所有的SQL语句都需要重新解析，对共享池中LATCH的争用，以及大量更解析会消耗大量CPU资源。
SYS@ bys3>alter system flush shared_pool;
System altered.
但是之后会面临大量正常SQL语句的第一次执行的硬解析。。
过后仍需要找出产生大量硬解析的SQL语句进行优化。