高并发环境中With查询结果集过大引发的性能问题一例

我们知道，使用With as方式，可以简化SQL，更重要的是避免重复访问数据。

在一条SQL中，With子查询的数据，一次查询，多次使用，所以，Oracle会自动将With查询的结果集产生为临时表，这些临时数据是存储在PGA中的，也就是每个会话查询后的缓存数据不能被其他会话共享。那么，如果这样的SQL被高频、高并发的使用，会产生什么样的结果呢？今天分享这样一个发生在真实用户生产环境下的案例。

       接到一开发人员反馈，说某个SQL在产品中一执行就卡住了，是住院医生站中的一个比较高频的SQL，并发量很大，导致很多住院医生的正常业务操作无法进行。在PLSQL中执行该SQL，以及单独编写了一个小工具来模拟产品中相同的方式执行该SQL都没有遇到问题，而只要在产品中一执行到该功能就卡住了，可以重现。

       据了解，用户是今天上午突然出现这种情况的，用户说发生问题前后没有什么改变（肯定有，只是他不知道，也难以查证），立即瞄了一眼该SQL，并不复杂，没有什么特殊语法，执行计划也没有发现什么问题，只是用到了With查询，怀疑可能是With引发的，会不会是With在该用户特定环境下的BUG呢，因为之前遇到过With中的树型查询、With嵌套查询引发的游标高版本的BUG。

       立即让开发人员改写SQL，不用With，然后编译产品进行验证，问题得到解决。当时正好中午了，准备吃了饭再把所有客户端的产品进行替换。然而，吃完饭后，奇怪的事情出现了，还没有替换客户端产品，用户反映执行相同的产品功能，不卡了，没有在生产库进行任何修改的情况下，问题却被解决了，难道之前的问题是因为系统饿了？

现在，开发人员不知道这个SQL到底还改不改，有几个疑问需要得到合理的解释：

1.     为什么中午过后，问题自动消失了？

2.     为什么同样的SQL，在PLSQL和单独编写的工具中执行都没有问题？

3.     为什么同样的SQL，在其他很多使用相同版本的用户处却没有出现问题？

 

带着这几个疑问，仔细阅读了With中的SQL业务逻辑，终于发现一个问题：

由于缺省一个谓词条件，导致该SQL执行时会大范围的访问历史数据，并且可能返回一个比预期的大几倍到几十倍的结果集。

结合With产生临时表的原理，以上几个疑问就容易解释了：

1.     一条SQL中使用With时，如果多次使用With中的子查询，Oracle会自动为每个执行该SQL的会话产生临时表来存储这些中间数据，这些临时数据在每个会话之间不能共享，所以，在高并发执行时，可能会消耗大量的PGA内存。如果With中的子查询只使用了一次（这种情况就没有必要使用With了），执行计划不会产生临时表，可以加提示字MATERIALIZE来强制产生临时表。

2.     由于高频、高并发的执行该SQL，导致PGA不够用，不得不使用Oracle的临时文件，可能用到了虚拟内存或交换分区，而数据量比较大的情况下，查询性能会非常慢（特别是还要进行多表嵌套连接时），前台客户端表现出的问题就是SQL被卡住了。

3.     中午，大多数操作人员下班了，退出客户端后，PGA得到了释放，内存充足了，所以再次使用产品功能，临时表可以直接在内存中产生，不会使用临时文件，所以问题自动消失了。

4.     在PLSQL和单独编写的工具中执行，当前会话中只缓存了这一条SQL，而在产品中执行，在执行到该SQL之前，缓存了大量SQL及会话私有信息，PGA已占用较多，执行到该SQL时，该会话能够分配到的PGA可能很少甚至没有了，就会用到临时文件。

5.     其他很多使用相同版本的用户，由于业务模式不同导致的数据差别，其中另外一个谓词条件正好能过滤掉多余的数据。

 

最后，开发人员需要做的修改，不是避免使用With，而是在With子查询中加上缺失的谓词条件，正常情况下，只会返回少量的结果集，从而避免了占用大量PGA的情况发生。

 

       这个案例的关键点在于With子查询中返回了过多了数据，在高并发的环境中执行时，过度消耗了PGA，那么，如果这样的SQL执行是低频的，是否会有问题呢？

       之前我们也遇到过另外一个案例，在一段历史数据修正脚本中，With子查询返回了数百万行数据，再将这些数据与另外的表进行表间嵌套查询，结果发现速度非常慢，最后，修改为不使用With，虽然SQL多了一些重复的代码，并且重复访问了一些数据表，然后，对比性能后发现，速度提高了几倍。

       所以，使用With子查询时，我们需要注意以下两点：

1.     如果是高并发执行的 SQL，应避免With子查询中返回太多的数据，具体多少才合适需要根据你的并发量和数据库内存情况来评估，没有一个严格的数字。

2.     即使是单次执行的SQL，如果With子查询中的结果集巨大，查询性能可能还不如不使用With方式，这个也是需要对比测试，没有一个准确的数字。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/117319/viewspace-2123660/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/117319/viewspace-2123660/