我们做SQL优化经常会从V$SQLAREA/V$SQL去查找TOP SQL，当然现在我们更多的从AWR报告中去查找SQL，实际上AWR的TOP SQL也是定时从V$SQL/V$SQLAREA中去抓取超出阈值范围的SQL，做成SNAP，然后通过两个SNAP的对减，产生TOP SQL执行的数据。去年曾经有朋友问我，他们的系统有数百个微应用在访问，经常因为某个微应用开发的质量存在问题导致数据库出现严重问题，因此领导要他们加强对TOP SQL的监控。因为业务的特殊性，AWR的数据无法满足他们对SQL监控的需求，他们想了个办法，想每隔5分钟对V$SQL进行一次分析。监控上线后，系统出了几次严重的HANG死，只能暂停这个监控。他十分不理解，为什么他们就是对一张视图进行一些统计分析会产生这么严重的问题呢？统计分析的SQL每次执行的时间也不长，也就是几十秒，也没有什么物理读。
实际上他误解了V$SQL这个视图了，这可不是一个简单的视图，不是对几张物理表做了一个视图而已。你在访问V$SQL/V$SQLAREA的时候，你其实是在扫描共享池里的CURSOR对象。我们先来看两张十分经典的图
这张图十分经典，是Julian Dyke在他的一个讲座的PPT中画的，在老白2012年出版的《DBA的思想天空》中，也引用了这张图，并对此进行了说明。这里篇幅有限我们就简单的介绍一下，如果想更为详细的了解共享池中CURSOR的数据结构，可以参考《DBA的思想天空》中的第80页开始的3.1.4 库缓冲与游标。先来看第一张图，对于一个游标来说，父游标包含句柄（KGLHD）、对象结构（KGLOB）和名字结构（KGLNA）。每个父游标在V$SQLAREA 中有一条记录。在X$KGLOB 中， KGLHDPAR = KGLHDADR 的记录就是父游标的。每个父游标至少有一个子游标，子游标中包含：环境信息、统计信息、绑定变量、执行计划等。每个子游标包含一个KGLHD、一个KGLOB 和SUBHEAP。在每个子游标中，包含一个Heap 6，里面存放的是执行计划。
下面这张图明确地显示了父游标和子游标之间的关系。父游标的KGLOB 中的KGLDA 里的某个列表中存放了子游标的句柄（KGLHD）指针。对于子游标的结构，Dyke 也有一张很棒的图示：
篇幅关系，我们就不再展开了。其实我们可以通过一个十分小的实验来做一些简单的验证。
我们看到又一条SQL,执行了一次。如果我们再执行一次，再来查看V$SQLAREA：
这时候我们就知道，这条SQL的执行情况被很好的统计了。如果这时候我们把这条SQL对应的CURSOR PURGE掉，也就是说把这条SQL从共享池中清理出去，会发生什么情况呢？
这时候我们在v$sqlarea中找不到这条SQL了。如果我们再次执行会怎么样呢？
我们看到，我们又可以看到这条SQL了，不过我们看到的统计数据是执行了一次，而不是事实上的3次。这是因为CURSOR再重新建立后，统计数据只能从头开始。理解了V$SQLAREA与共享池KGLOB之间的关系，我们就可以重新审视我们的TOP SQL监控了，我们可以得到以下的知识：1、TOP SQL监控是一种开销十分大的操作，会对脆弱的共享池造成较大的影响，在扫描共享池相关对象的过程中，会影响并发执行，在一个并发执行量很高的，共享池争用较为严重的系统中频繁的扫描V$SQLAREA可能会对系统造成十分巨大的影响，就像文中前面提到的那个朋友的TOP SQL监控一样
2、由于共享池经常出现AGEOUT的操作，因此我们看到的SQL统计数据有可能不是准确的，这样我们AWR/STATSPACK报告中出现统计值为负数的情况也就可以理解了。如果一条对系统危害很大的SQL执行后，在几分钟内被AGEOUT了，那么我们也是有可能抓不到它的。
3、设置足够大的共享池可以减缓CURSOR AGEOUT的频率，也可以让我们的TOP SQL监控能够获得更准确的数据。
今天篇幅有限，就讨论这么多吧，如果有兴趣在这方面做更深入的研究，可以参考《DBA的思想天空》中80页到129页之间的内容。
最后修改时间：2020-04-03 07:37:34
文章转载自
白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
