我们知道,使用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/