从如何更好的监控Oracle共享池谈起

白鳝的洞穴 2023-03-02

1566

二十年前搞Oracle运维的时候，被折腾得最厉害的是共享池的问题，ORA-4031绝对是DBA必须面对的，也是最束手无措的错误。很多DBA面试官也会问大量的共享池诊断与优化的问题，虽然他自己对很多问题的了解也不过如此。

今早的这篇文章的主体结构是昨天下班前写出来的，今早做了一些补充就发出来了。因为昨天上午我一直在做D-SMART这个部分的优化设计，这篇文章实际上是我这一天工作的一些总结。

Oracle 10G以后有了SGA动态分配的能力，而且服务器的内存也从MB级别进入到了VLM的级别，共享池和ORA-4031的问题也就见得少了。在D-SMART里，针对ORA-4031的监控功能比较少，只提供了一些用于分析的工具，不过这几年也很少能发挥作用。

最近一个客户的数据库因为遇到BUG导致了一个实例出现ORA-4031,必须重启才能解决问题。用户提出了针对ORA-4031问题能否加强监控与分析。我这几天也一直在考虑这个问题。Oracle数据库中最脆弱和最复杂的组件就是SHARED POOL，对SHARED POOL的监控一定要特别小心。十多年前给用户做Oracle服务的时候也经常遇到采集SHARED POOL的数据的时候把数据库实例HANG死的问题。我甚至养成了采集共享池数据的时候一定另外开好另外一个窗口，一旦有问题立马杀掉采集的会话。

可能很多朋友开发的Oracle监控工具里都有共享池监控的功能，他们也觉得监控共享池的手段是很丰富的，为什么我们会把这件事搞得这么复杂呢？

在D-SMART的共享池数据采集方面，我也是十分谨慎的，不希望因为监控工具设计的不慎而导致原本负载过高的数据库实例被监控脚本搞垮。在V2.2版本的D-SMART中，和SHARED POOL相关的指标都是通过比较稳妥的系统视图采集的。如今要加强共享池数据的采集，首先想到的就是v$sgastat，因为Oracle的AWR也会采集这个视图里的数据。

为了确认访问的视图的风险，我们需要找出视图访问的基础数据结构，如果需要大量扫描共享池，那么就应该尽可能避免。通过下面的脚本可以查找相关信息。

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SGASTAT';

可以看出，GV$SGASTAT的基础视图是x$ksmfs ,x$ksmss ,x$ksmls ,x$ksmjs ,x$ksmns, x$ksmstrs，这些基础数据结构都是汇总KGH的数据的，本身不需要遍历KGH，因此风险都不大。

比如ksmss存储了共享对象的一些属性，虽然不会在访问该对象时持有shared pool的闩锁，不过访问过程中也会对共享池内的对象的变更产生影响。因此虽然我们可以比较安全的采集数据，不过也不适合过于频繁。这样的指标的采集，每个小时一次就可以了。

column indx heading "indx|indx num"

column kghlurcr heading "RECURRENT|CHUNKS"

column kghlutrn heading "TRANSIENT|CHUNKS"

column kghlufsh heading "FLUSHED|CHUNKS"

column kghluops heading "PINS AND|RELEASES"

column kghlunfu heading "ORA-4031|ERRORS"

column kghlunfs heading "LAST ERROR|SIZE"

select indx, kghlurcr, kghlutrn, kghlufsh, kghluops, kghlunfu, kghlunfs from sys.x$kghlu where inst_id = userenv('Instance')

对于监控共享池的情况来说，kghlu数据结构更为有效，可以十分详细地查看到共享池中的每个子池的统计信息。

特别是kghlunfu/ kghlunfs这两个字段，显示了每个子池出现的ORA-4031错误的次数以及最后一次分配错误所需分配的空间的大小。一般来说如果在某个子池中分配共享池空间失败只是一个miss，此时会从另外一个池中分配，直到所有的子池中都无法分配空间，才会真正的出现FAILURE。因此ERRORS数量真正指出了共享池内存无法分配空间的情况。对该内存结构的监控可以比较准确地反映出共享池碎片产生的后果。不过这个数据结构的访问也需要通过相关闩锁，并且这个结构的访问频率要比前面所提的那些结构要频繁。因此对该数据结构的采集依然不建议过于频繁，一个小时采集一次已经足够了。

为什么这样说呢？kghlu中的kghlusep指针是一个十分重要的指针，它指向了共享池LRU链上的一个关键位置，那个位置分割了共享池LRU链的冷热区。当新的CHUNK要加入LRU链的时候，是添加在该指针左侧的冷区尾部。而冷区中的CHUNK被多次访问时会迁移到LRU链的热端，以便于被重用。因此这个指针是访问十分频繁的，采集该结构的数据要格外谨慎。

x$kghlu经常被某些数据库监控软件用来监控共享池问题，不过频繁的访问这个数据结构还是会对数据库产生影响的，特别是数据库并发比较大，共享池存在性能问题的时候，如果过于频繁的监控这个数据结构，可能会产生一些相当严重的问题。如果知道了这一点，我想大家应该理解为什么我会对共享池的监控数据采集如此谨慎了。

col "avg size" format a30 truncate;

col siz format 999999999999

SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ) /COUNT(KSMCHCLS) /1024)), '999,999.00')||'k' "AVG SIZE" FROM X$KSMSP GROUP BY KSMCHCLS;

实际上要分析shared pool的风险，上面的语句具有更好的效果，如果发现perm内存不断增长，free的平均大小不断下降，甚至低于4KB，那么说明共享池出现了较大的碎片化风险。而下面的语句可以作更细致的分析。

col sga_heap format a15

col size format a10

select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') "size" ,count(*),ksmchcls Status, sum(ksmchsiz) Bytes from x$ksmsp where KSMCHCOM = 'free memory' group by ksmchidx, ksmchcls,'sga heap('|| KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

这条SQL可以采集到共享池中free内存的详细情况，如果较大的heap比较少时，共享池的碎片化就很严重了。

似乎我们可以直接对x$ksmsp直接做采集，从而获得对共享池分析的更有效的数据。不过真的如此吗？我们如果看一下x$ksmsp的实际结构，就会明白为什么我们不想把这个采集放到自动化采集的脚本中，更好的采集共享池的信息了。

我们可以看到ksmsp实际上指向了一个kghds的链表，而这个链表实际上是指向真实的heap链，对x$ksmsp的统计实际上会遍历heap链表，对于共享池很大，并且共享池并发访问很重，特别是共享池存在性能问题的场景，这种访问无疑会加重共享池的负担，甚至成为压垮骆驼的最后一根稻草。如果这种采集放到不受控的自动化采集中去，那可能会带来不可知的影响。因此这种分析我们只是在手工点击的工具中提供，而不会做成自动化采集的一部分。

监控与诊断实际上也是一种运维知识，开发监控与诊断工具，产品经理中应该有资深的运维专家，仅仅依靠高水平的研发人员是开发不出一套真正高水平的运维监控与诊断工具的。而对于一些比较脆弱的数据库模块的监控采集，也需要十分谨慎的做设计，否则监控软件会成为伪装成天使的恶魔。

oracle 数据库数据结构

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

从如何更好的监控Oracle共享池谈起

评论