shared_pool的KGLHD 一直在快速的消耗memory 造成ora-04031错误

柔光的暖阳◎ 2022-05-28 05:38 244阅读 0赞

Ora-04031作为数据库较为典型的内存分配错误，它的出现往往意味着数据库的无法使用直接影响用户生产系统的正常运作。

最近就遇到了一例，环境如下：

数据库版本：Oracle 11.2.0.3

操作系统：AIX 6.1

物理内存： 64G

数据量：300G左右

错误现象： 应用无法连接数据库，数据库无响应

通过检查后台日志发下报错信息如下：

Errors in file/oracle/diag/rdbms/orcl/orcl/trace/orcl\_ora\_17760806.trc (incident=482899):

ORA-04031: 无法分配 1016 字节的共享内存 ("shared pool","unknownobject","sga heap(1,1)","KGLHD")

此时生成了问题发生时间段前，数据库的AWR报告 [![Oracle 11g上的ora-04031][Oracle_wbr_11g_ora-04031]][Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031] 
    发现在问题发生时间段，buffer\_cache只有64M，而shared\_pool几乎占据了所有分配给oracle的内存空间。

而64M的buffer\_cache对于一个300G左右数据量的数据库显然是不合理的，此时的bufferhit只有37%，一般来说buffer hit至少要在90%以上才算正常。

[![Oracle 11g上的ora-04031][Oracle_wbr_11g_ora-04031 1]][Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 1]

继续检查各个内存参数，分配给oracle的总内存memory\_max\_target=38G，其中pga=5G，db\_cache=10g，shared\_pool=20G，而memory\_target和sga\_target都为0，正常来说数据库应该为手动管理，即各个内存区域设置后不应该自动调整状态，为什么会自动进行调整，这一点还有待进一步验证。

通过查询

SELECT start\_time, end\_time, component, oper\_type, oper\_mode,initial\_size, target\_size, final\_size, status FROMdba\_hist\_memory\_resize\_ops ORDER BY 1, 2;

[![Oracle 11g上的ora-04031][Oracle_wbr_11g_ora-04031 2]][Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 2] 
    发现在几天前数据库就已经在不停的自动进行内存分配的调整，不断减小buffer\_cache，来增大shared\_pool,那问题来了，是什么原因导致shared\_pool不断增大，仍然出现了内存不够分配的情况。

继续查看相关AWR

[![Oracle 11g上的ora-04031][Oracle_wbr_11g_ora-04031 3]][Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 3]

其中freememory还有非常多的空间，由于为了用户尽快能够使用，数据库已经进行了重启，无法获得当时的内存堆栈情况；

检查数据库相关SQL语句，应用语句基本上都没有使用绑定变量，同时cursor\_sharing设置为similar，当cursor\_sharing设置为similar容易造成数据库的一些问题类似于latch和pin发生的时间增多，内存碎片化严重无法回收，甚至于一些bug，12c中已经取消了该参数similar的设置，当然设置为force也会带来一定的问题，针对此类情况最好的方法是通过应用修改程序，使用绑定变量可以从根本上有效解决shared\_pool过度分配的情况。

再看KGLHD也占用了相当大的空间，记忆中MOS上曾经看到过类似的文章，

当 \_KGHDSIDX\_COUNT>1，而KGLHD又占用较高，出现ORA-04031错误，即使通过flushshared\_pool也无法释放相关内存空间，这些现象下，可能是由于bug引起的。

\_KGHDSIDX\_COUNT参数是什么？

Oracle 9i之前，shared\_pool太大的情况下，会由于shared\_pool free list 或者 usedlist太长造成一些性能问题。Oracle 9i开始，增加了shared\_pool subpool的概念，一个大的shared\_pool，会被分割成几个sub pool，每个sub pool有自己的free list,used list. 这样，就不会因shared\_pool太大造成性能问题。  
sub pool的数量通常是默认的，Oracle根据shared pool大小决定的，也可以由 \_kghdsidx\_count来决定。

通过语句，检查\_KGHDSIDX\_COUNT的值为4。

select a.ksppinm, b.ksppstvlfrom   x$ksppi a, x$ksppsvb  
where  a.indx = b.indx and a.ksppinm ='\_kghdsidx\_count';

![20180330153115281][]

再次通过MOS进行比对，此次错误情况的确符合Bug: 13250244的描述

Oracle官方给出的说法在12c版本中进行了修复

当前状态下可以通过

1）升级到11.2.0.4，或安装11.2.0.3.4补丁。

2）修改隐含参数（需重启数据库生效）来应对ora-04031的错误

ALTERSYSTEM SET "\_kghdsidx\_count"=1 SCOPE=SPFILE;

进行此bug的应对，但是修改参数可能导致内存shared\_pool和librarycache争用的情况影响运行性能。

至此问题基本可以明确，由于未使用绑定变量的情况下，修改cursor\_sharing参数可能导致的oraclebug，解决此问题的思路为：

1）应用厂商修改程序，使用绑定变量，并修改cursor\_sharing参数，改回默认的exact；

2）安装补丁或升级到11.2.0.4版本；

3）增加物理内存（不能根除问题），扩大oracle可用内存空间；

4）定期重启数据库释放空间。

日常观察和预防该错误的手段，可以使用：

selectfree\_space,avg\_free\_size,used\_space,avg\_used\_size,request\_failures,last\_failure\_size  
from v$shared\_pool\_reserved;

[![Oracle 11g上的ora-04031][Oracle_wbr_11g_ora-04031 4]][Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 4]

当request\_failures > 0 那说明shared\_pool 在内存分配上已经存在一定的问题。

[Oracle_wbr_11g_ora-04031]: http://s7.sinaimg.cn/mw690/004crabMgy6XZ4qIYWa86&690
[Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031]: /images/20220528/c474cfd62fe34b3393d99b7e4bd81539.png
[Oracle_wbr_11g_ora-04031 1]: http://s11.sinaimg.cn/mw690/004crabMgy6XZ4J0OWS1a&690
[Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 1]: /images/20220528/47be3a9f60614d52a002cd19f0e86577.png
[Oracle_wbr_11g_ora-04031 2]: http://s5.sinaimg.cn/mw690/004crabMgy6XZ60c0MA64&690
[Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 2]: /images/20220528/3308b8fa5fff45279ddced549c064f10.png
[Oracle_wbr_11g_ora-04031 3]: http://s8.sinaimg.cn/mw690/004crabMgy6XZ6U5zHV87&690
[Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 3]: /images/20220528/8cfefed3a2cf45209dd64f026364b3cc.png
[20180330153115281]: /images/20220528/21cda2ac11834ed5a1972b32453395f1.png
[Oracle_wbr_11g_ora-04031 4]: http://s8.sinaimg.cn/mw690/004crabMgy6XZeFzPgz47&690
[Oracle_wbr_11g_ora-04031_Oracle_wbr_11g_ora-04031 4]: /images/20220528/aaab950bcd1d40a4bd88155d34a75201.png