ora-4031原因和解决方法

前天，发现一个10g数据库出现ora-4031错误。

4031错误产生原因
当尝试在共享池分配大块的连续内存失败(很多时候是由于碎片过多,而并非真是内存不足)时,Oracle首先清除共享池中当前没使用的所有对象,使空闲内存块合并.如果仍然没有足够大的单块内存可以满足需要,就会产生ORA-04031错误.
1、Oracle的bug
2、没使用绑定变量,sql没有足够的共享,产生大量不必要的解析
3、Shared pool设的太小
4、SubPool的划分可能也会导致各分区之间的协调问题,但从Oracle10g开始,允许内存请求在不同的SubPool之间进行切换,提高进请求成功的可能,但不能切换无限次,所以问题仍然存在)
5、open_cursors（0 to 65535） 设的过大

白鳝老师的《dba的思想的天空》中一书对该错误有比较详细的解释，当然这个问题出现在9i的数据库里是比较多的。上述描述该问题产生的原因也基本包含了9i数据库产生ora-4031错误。

解决:4031错误的方法
1、刷共享池
  alter system flush shared_pool;
不过这个办法不建议使用，因为这个操作之后，sql需要新的硬解析。
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;

综合上述，最靠谱的方法是增加系统内存，设置合适的sga值，比如系统64G内存，sga_target 分配64*0.6*0.8

查看sga中shared pool 分配的大小

select * from v$sga_dynamic_components;