oracle RAC中Oracle 4031报错发生的原因是:大量的hard parse硬解析导致了sag 的shared pool中的free list产生了大量的内存小碎片。当一个需要大内存的sql语句进行hard parse硬解析时,无法从free list中找到可用的内存,即使进行内存的释放,也无法找到符合条件的内存块,就会报ora-04031的报错。
解决方法是:1.清空Sga中的shared pool:
alter system flush shared_pool;
2.sql共享,绑定环境变量。
Sql语句需要统一书写风格,空格、大小写、数值等不一样,回车等,都会影响硬解析。
并且可以使用强制绑定环境变量的方法,提高解析效率。
3.扩大shared pool 的保留区。
分配内存时候,会先从普通共享池的free lists中寻找空间,free lists无可用空间时,就会寻找可释放的内存空间,如果无法找到可释放的内存空间,就会从保留池中分配内存。
select request_misses from v$shared_pool_reserved;
查看保留区的请求空间失败次数,只要有一次,就会发生ora-04031的报错。
可以查看保留区的大小:
show parameter shared_pool_reserved_size;
修改保留区的大小:
alter system set shared_pool_reserved=xxxM scope=both;
4.增大shared pool的大小。
可以查看sga中各个池子的情况:
select component,current_size from v$sga_dynamic_compoments;
select shared_pool_size_for_estimate,shared_pool_size_factor from v$shared_pool_advice;
当shared_pool_size_factor=1的时候,就是设置shared pool 大小的最佳值。
alter system set shared_pool_size = xxxM scope=both;
可以用这个语句去修改shared pool的大小。
5.把内存使用量非常大的对象keep在内存中。
查找可以keep的对象,执行dbms_shared_pool.keep('对象名');
查找对象名:
select owner,name,namespace,type,sharable_mem from v$db_object_cache where sharable_mem>10000 and (type='PACKAGE' or type='PACKAGE BODY' or type='FUNCTION' or type='PROCEDURE') and KEPT='NO';
这样可以把内存占用大的对象keep在内存中。
当ORACLE中有大量的小free chunk时,找不到合适的free chunk时,会去Library cache中找到大的chunk,根据shared pool中的LRU 链表规则,把它的空间释放,重新链到free list用来解析新的硬解析执行计划,如果此时再来一个大的sql,无法找到合适的free chunk,也在library cache中找到可释放的空间,也会报ora-04031的报错。
白虎
鼓励的话语:言慢者贵,性柔者富,厚德者旺!