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

共享池之十：模拟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%。SQLshow parameter shared_pool_reserved_sizeSQLselect 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情况，如果 10K) size,count(*),ksmchcls Status, sum(ksmchsiz) Bytesfrom x$ksmspwhere KSMCHCOM = free memorygroup 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 list2.查找子堆lru3.从父堆中分配chunk,做为子堆新 extent4.父堆查找free list5.父堆查找LRU6.使用hide free space7.如以上步骤失败，报0RA-04031父堆需要持有shared pool latch子堆的操作，有mutex / library cache pin保护模拟ORA-04031错误：首先要将修改open_cursors 参数BYS bys3show parameter cursorsNAMETYPE VALUE- - -open_cursorsinteger 50session_cached_cursorsinteger 2BYS bys3alter system set open_cursors =50000;System altered.BYS bys3show parameter shared_poolNAMETYPE VALUE- - -shared_pool_reserved_sizebig integer 7Mshared_pool_sizebig integer 176M重新登陆会话：只打开游标，不关闭：select deptno from dept where deptno= 这语句的对象字段要能执行declaremsql varchar2(100);mcur number;mstat number;jg varchar2(2000);cg number;begin for i in 1.10000 loopmcur:=dbms_sql.open_cursor;msql:=select deptno from scott.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 1199ORA-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,selecto,PLDIA191e0a8d,PAR.C:parapt:Page)ORA-06508: PL/SQL: could not find program unit being called:SYS.DBMS_SQLORA-06512: at line 8执行其它查询也报错：BYS bys3select * from dept where deptno=44;select * from dept where deptno=44*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 2ORA-04031: unable to allocate 136 bytes of shared memory (sharedpool,select /*+ rule */bucket_cn.,SQLA337fc737,kccdef: qkxrMemAlloc)Elapsed: 00:00:10.99BYS bys3show parameter shared_poolORA-04031: unable to allocate 16 bytes of shared memory (sharedpool,unknownobject,KGLH092c529c4,kglHeapInitialize:temp)警告日志中也可以看到大量的报错日志：Mon Feb 03 20:20:44 2014Errors 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,KGLH0ca490471,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,KGLH0ca490471,kglHeapInitialize:temp)Use ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.oraclebys3 $ tail -n 50 alert_bys3.logMon Feb 03 20:20:14 2014Errors 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,KKSSP37,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,KKSSP37,kglseshtSegs)Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_cjq0_10175.trc:ORA-00604: error occurred at recursive SQL level 1ORA-04031: unable to allocate 528 bytes of shared memory (sharedpool,PROPS$,PLDIA191e0a8d,PAR.C:parapt:Page)4031错误临时解决方案是：杀掉一部分会话或者flush shared_pool;SYSbys3select sid,count(*) from v$open_cursor group by sid; SID COUNT(*)- -30 71 228 113 331 414 235 301433 315 716 5s