Oracle 解决4031错误

一、问题描述

什么是4031错误和4031错误产生的原因:
简单一个句话概括:

由于服务器一直在执行大量的硬解析,导致Oracle 的shared pool Free空间碎片过多,大的chunk不足, 当又一条复杂的sql语句要硬解析时, 缺少1个足够大的Free chunk, 通常就会报4031错误.

二、解决方法

临时解决推荐方法1，其他一般推荐方法4和5，大佬推荐方法2和3解决。

方法1.清空Shared pool缓存.

此方法治标不治本，以后还会报4031，可以再次清理shared pool解决
执行

alter system flush shared_pool;

在这里插入图片描述

方法2.共享SQL

这一块需要自己理解，我是根据学习了相老师讲解的方法，可以搜索一下共享sql减少硬解析的方法；

方法3. 将复杂sql语句及执行计划强制保留在缓存

dbms.shared_pool.keep('object_name')

此话怎么理解? 其实导致4031错误的原因是shared_pool空间没有足够大的chunk提供给新的复杂sql语句进行硬解析, 其实也不是单纯地因为free 空间碎片太多, 其实当free空空间没有足够大的chunk时, oracle 会释放 Library cache中長时间不使用的chunk.

举个例子, 1个数据库实例中, free空间中chunk数量很少了,而且没有足够大的chunk, 这时有1个中等规模的sql要硬解析,就会从library cache中找那些不长用的chunk释放出来, 如果释放里面若干个小规模的chunk, 是无问题的.

但是如果其中library cache 有个很大的chunk存放着1个很复杂的sql语句, 但是长时间没有被使用了, 有可能这个chunk被释放出来被使用, 而且被拆解了… 其中一部分别使用, 另1部分被放入free空间中.

而之后偏偏那个很复杂的sql语句又执行了,需要硬解析, 而那个足够大的chunk已被分解… 则有可能报4031错误.

所以方法3就是指定一些sql语句的缓存长期保留, 不让他们被释放, 避免这种情况的发生.

方法4 增加shared pool空间.

一般当服务器增加内存的时候, 就可以给shared pool增加空间:

一般来讲Oracle 10g以后 SGA里面各大池空间都是动态分配的, 一般增加SGA大小即可.
在这里插入图片描述
这个大小不要小于你查询到的大小

alter system set sga_target = 5000M;                  --注意不能超过sga_max_size
alter system set sga_max_size = 5000M;              --该这个必须重启数据库, 目的 是限制sga_target, 以免sga_target不小心设置过大,倒置服务器内存耗尽挂掉

也可以单独设置shared pool的大小:
查询shared pool的大小
在这里插入图片描述
注意这个设置的大小不要小于查询到的大小

alter system set shared_pool_size  = 2G;

注意默认情况下用show parameter 来查查看 sga_target 和 shared_pool的话, value 都是0的, 并不是说它们的当前值就真的是0了,只是0是代表交由Oracle自己管理的意思.

可以用这个语句可以查看当前的各大池占用大小:

select component, current_size from v$sga_dynamic_components;

在这里插入图片描述

方法5: 保留区

oracle 为了避免4031错误, 其实还可以在shared pool划出1个保留的free 空间, 平时不会使用, 专门应对突然出现的复杂sql语句硬解析,相当与应急用的空间啦. 这个方法相当有效, 可以避免大部分的4031错误, 不过缺点就是平时减少了shared pool的可用空间, 减低了数据库缓存性能啦~

我们可以用下面这条语句来查看在保留区请求失败的次数:

select request_misses from v$shared_pool_reserved;

我自己测试的，所以是0，如果这个数字不为0，就会出现4031错误
在这里插入图片描述

注意这个次数一旦大于1, 就证明发生过4031错误! 因为既然去保留区申请空间了, 证明是1个复杂的sql语句, 还申请失败的话,就会报4031错误啦~ 所以这个次数最好就是0啦.
如果不为0我们就要扩大保留区：
查看保留区大小：
在这里插入图片描述
设置保留区大小都一样啦:
这个数字我随便写的，这个数字要大于你原来的保留区的大小

alter system set shared_pool_reserved_size = 500m;

注意不要设置过大啦, 你懂的.

扩展

shared pool最佳设置大小

select 'shared pool' component,   
            shared_pool_size_for_estimate estd_sp_size,
            estd_lc_time_saved_factor parse_time_factor,
            case when current_parse_time_elapsed_s + adjustment_s < 0
            then 0
            else      current_parse_time_elapsed_s + adjustment_s
            end  response_time
            from ( select a.shared_pool_size_for_estimate,
                                   a.estd_lc_time_saved_factor,
                                   a.estd_lc_time_saved,
                                   e.value/100 current_parse_time_elapsed_s,
                                   c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
                         from   v$shared_pool_advice a,
                        (select * from v$sysstat where name ='parse time elapsed') e,
                        (select estd_lc_time_saved
                          from   v$shared_pool_advice
                         where shared_pool_size_factor = 1) c
                         );