Oracle 解决4031错误

客官°小女子只卖身不卖艺 2022-03-19 13:11 494阅读 0赞

这篇文章是参考甲骨论老相老师的教学视频:  
http://v.youku.com/v\_show/id\_XMzkyMTkzNjg4.html  
所做的学习笔记.  
  
  
**什么是4031错误和4031错误产生的原因:**  
详细请看这里:  
**http://v.youku.com/v\_show/id\_XMzkyMTkzNjg4.html**  
  
简单一个句话概括:  
     由于服务器一直在执行大量的硬解析,导致Oracle 的shared pool Free空间碎片过多,大的chunk不足, 当又一条复杂的sql语句要硬解析时, 缺少1个足够大的Free chunk, 通常就会报4031错误.  
  
  
**解决方法:**  
  
**方法1.清空Shared pool缓存.**  
执行Alter system flush shared\_pool;  
  
这样的话释放出大量的free 空间, 不过由于缓存都没了,接下来肯定会发生大量的硬解析,这是1个指标不治本的方法..  
  
**方法2.共享SQL**  
详细请看这里:  
[http://nvd11.blog.163.com/blog/static/200018312201301945631729/][http_nvd11.blog.163.com_blog_static_200018312201301945631729]  
  
  
  
  
**方法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语句的缓存长期保留, 不让他们被释放, 避免这种情况的发生.  
  
      **步骤1**  
      首先是找出Library cache中那些占用大的对象啦.  
  
      语法如下:  
      ***select*** name **from** v$db\_object\_cache **where** sharable\_mem >  **10000**  
                  **and** type  **in** ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')  
                   **and** kept = 'NO';  
  
      注意红色数字单位是kb, 生产中要根据实际情况修改..  
     如图:

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011]

我找到1个包 PAK\_EMP, 占用超过10k以上的(其实10k是很小的值了).  
  
  
接下来就执行  **dbms.shared\_pool.keep了?**  
  
但是实际上dbms.shared\_pool这个默认不会安装在数据库的如下图:  
不过提供了安装脚本:  
路径是  
$ORACLE\_HOME/rdbms/admin/dbmspool.sql  
  
所以在sqlplus执行:  
@?/rdbms/admin/dbmspool.sql   就可以安装这个系统包了, 如图:

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 1]

接下来就可以执行  
exec dbms\_shared\_pool.keep('SCOTT.PAK\_EMP')    
![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 2]  
如上图, 这个对象一直会保留在shared\_pool缓存了. 注意对象名字要带模式名啦.  
这时在查看对象缓存:

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 3]

见到kept 字段变成yes了!  
  
如果想撤销点算: 执行  
exec dbms\_shared\_pool.unkeep('SCOTT.PAK\_EMP')    
就得了

**方法4 增加shared pool空间.**  
  
一般当服务器增加内存的时候,  就可以给shared pool增加空间:  
  
一般来讲Oracle 10g以后 SGA里面各大池空间都是动态分配的, 一般增加SGA大小即可.  
  
**alter system set sga\_target = 2000M;**                  --注意不能超过sga\_max\_size  
**alter system set sga\_max\_size = 2000M;**              --该这个必须重启数据库, 目的  
                                                                            是限制sga\_target, 以免sga\_target  
                                                                            不小心设置过大,倒置服务器内存耗尽挂掉  
也可以单独设置shared pool的大小:                
**alter system set shared\_pool\_size  = 200M;**          
  
         **注意默认情况下 用show parameter 来查查看 sga\_target 和 shared\_pool的话, value 都是0的, 并不是说它们的当前值就真的是0了,只是0是代表交由Oracle自己管理的意思.**  
  
可以用这个语句可以查看当前的各大池占用大小:  
select component, current\_size from v$sga\_dynamic\_components;

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 4]

可以看出其实当前shared pool大小接近250m了  
  
我们现在做个实验,  我们手动将shared pool设置为60m 看看神马情况

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 5]  
  
再查看一次大小:

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 6]

居然没有任何变化,  
  
       **实际情况是这样的:  当dba 设置了1个sga\_target后, sga就根据sga\_target的设置大小自动为shared pool设置1个最小的值, 假如这个值是150m, 而当我将shared pool手动设置为60m时, 小于这个值, oracle就会无视这个命令的..**  
  
**方法5: 保留区**  
oracle 为了避免4031错误,  其实还可以在shared pool划出1个保留的free 空间, 平时不会使用, 专门应对突然出现的复杂sql语句硬解析,相当与应急用的空间啦. 这个方法相当有效, 可以避免大部分的4031错误, 不过缺点就是平时减少了shared pool的可用空间, 减低了数据库缓存性能啦~  
  
  
我们可以用下面这条语句来查看 **在保留区请求失败的次数**:  
select request\_misses from v$shared\_pool\_reserved;

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 7]

注意这个次数一旦 大于1, 就证明发生过4031错误! 因为既然去保留区申请空间了, 证明是1个复杂的sql语句, 还申请失败的话,就会报4031错误啦~ 所以这个次数最好就是0啦.  
  
可以用下面语句去查看当前 shared pool保留区的大小

![Oracle 解决4031错误 - 饥民 - 饥民2011][Oracle _4031_ - _ - _2011 8]

可以看到保留去有10m多啦~  
还见到 shared\_pool\_size 只有60m, 头先设置的啦,无视他吧~ 将其设置会0就会自动由 oracle 管理了.  
  
设置保留区大小都一样啦:  
alter system set shared\_pool\_reserved\_size = 100m;  
  
注意不要设置过大啦, 你懂的.

[http_nvd11.blog.163.com_blog_static_200018312201301945631729]: http://nvd11.blog.163.com/blog/static/200018312201301945631729/
[Oracle _4031_ - _ - _2011]: http://img3.ph.126.net/MsCEuiKEXTOkHuZt1DuIuw==/6598127496842666043.jpg
[Oracle _4031_ - _ - _2011 1]: http://img7.ph.126.net/tYTQl_gVzdQtekDgcc7ivw==/6598104407098488257.jpg
[Oracle _4031_ - _ - _2011 2]: 
[Oracle _4031_ - _ - _2011 3]: http://img6.ph.126.net/gP8p6S3dRBr1atJ-IA0T_A==/6598167079261266586.jpg
[Oracle _4031_ - _ - _2011 4]: http://img7.ph.126.net/R_KzgRmiEc1dznFbTw1cfQ==/6597907594517333757.jpg
[Oracle _4031_ - _ - _2011 5]: http://img1.ph.126.net/6MJE3vU7T83M61V61qjK6w==/6597140135402461211.jpg
[Oracle _4031_ - _ - _2011 6]: http://img1.ph.126.net/DjYutTmmRG-GtBIe2A57tA==/6597178618309429191.jpg
[Oracle _4031_ - _ - _2011 7]: http://img9.ph.126.net/Pu_Ma0g68Za9AzzCE6mFxQ==/6598292423586841649.jpg
[Oracle _4031_ - _ - _2011 8]: http://img6.ph.126.net/e4QnFh9ojcVWVCvPc9IZfA==/6597502974238094466.jpg