最近在维护以前业务的时候,要求对一个统计报表进行修改。运行报表的时候发现数据库总是报异常ORA-04031: unable to allocate 2024 bytes of shared memory ("large pool","unknown object","session heap","frame segment"),于是把sql打印出来,看到这个sql简直是暴晕!!!
该sql长达330行,但这并不是最严重的,最严重的是其中包含38个union,82个select,116个in,总共达到37K。注意这是一个sql,就是说是数据库需要一次执行完的。集合操作本身就是比较慢的,何况38个union+116个in,并且还有82个select。普通服务器在运行这样的sql的时候基本要瘫痪!本人曾经试图在sqlplus(oracle客户端自带的那个)上运行该sql,sqlplus的默认缓存根本无法容忍这么大的sql,真不知道开发时是怎么调试的。
问题已经出现,那么就应该分析问题的原因,并解决问题!问题的原因是oracle数据库在找不到一个能够执行该sql的连续内存空间,则会报ORA-04031异常!一般有两种情况造成该问题,一种是oracle内存分配不合理;另外一种情况就是sql本身有问题。对上面提到的sql明显可以看出是sql的问题。事实上,我们是否必须可以构造多个sql来实现该功能,一般情况来说是完全可以的。
解决问题的思路:首先从自身找原因,特别是对系统进行优化的时候,首先优化的必须是sql;其次是数据库的优化。数据库的优化不是一步完成的,1万条记录和100万条记录时,同一个sql的速度可能出现指数变化!
首先保证sql合理,然后才是数据库优化!