nested exception is java.sql.SQLException: 关闭的连接

前段时间做一个后台系统，在开发测试环境应用运行的非常良好，没有出现任何的问题。

当测试完成上预发布时问题出现了，应用运行非常缓慢，一直停在某一个地方不执行，最后抛一个nested exception is java.sql.SQLException: 关闭的连接的异常，很时郁闷。刚开始碰到这个部题一直以为是我的连接池出现了问题，应用中事务太大，导致死锁等引起的。

网上也搜了一个遍，找到的答案基本都是说连接池配制有误，我用的C3P0的连接池，DB用的是oracle的，但一遍遍查应用，检查连接池配制，把超时设的很长各种方法都用尽了，最后还是不OK！

最后居然怀疑事务太大，所以就把设计做了变更，开发测试都没问题，但一上线，运行结果还是一样的问题，一度限入了僵局，难道是DB出现了问题？开发库测试库都是OK的。只能把问题定义到DB上去了

用select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
查了一下数据库，发现有张表未做提交，可会话已结束，这就是应用已死提，对数据库操作未做提交！奇怪。。

没办法，只能开始慢慢查询，非常简单，我把应用启动起来，然后让DBA在DB上观看一下执行计划，问题找到了。

 

DBA回复：

delete from table_name where id = :1 ;

这条SQL走错了执行计划，用了全表扫描引起的。

今天在测试库发现SQL执行计划：

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT   |                     |     1 |    61 |  2161   (1)| 00:00:26 |

|   1 |  DELETE            | table_name  |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| table_name |     1 |    61 |  2161   (1)| 00:00:26 |

------------------------------------------------------------------------------------------

分析表上的统计信息后走上了正确的执行计划：

--------------------------------------------------------------------------------------------

| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT   |                        |     1 |    58 |     1   (0)| 00:00:01 |

|   1 |  DELETE            | table_name    |       |       |            |          |

|*  2 |   INDEX UNIQUE SCAN| table_name _UK |     1 |    58 |     1   (0)| 00:00:01 |

这个问题主要原因是表上频繁的插入删除，数据量的变化造成表上的统计信息陈旧，SQL未能用到正确的执行计划。

防止这个问题的方法有两种：

1.定时分析数据库中表的统计信息。（无需修改SQL，但需要数据库中增加JOB，并且确定分析的周期）

2。对SQL加hints固定执行计划。（修改SQL，但可保证无论何时均可以走到这个S_REAL_BIDWORD_COST_UK索引扫描）

第一种方法如果分析周期内数据量出现急剧变化仍会出现走错执行计划的风险，个人建议采用第二种，修改SQL如下：

delete /*+ index(a  table_name _UK )*/ from table_name   a where a.ID = :1 ;

 

SQL走错执行计划的问题解释一下：

1.这个跟是否创建了唯一索引是没有关系的。

2. 插入删除频度跟SQL走错执行计划是没有必然联系的。只能说插入删除较多的表，数据量变化很快，更容易出现这样的风险

就算插入删除操作很少的表，但某一天数据量急剧变化的情况（比如平日20W左右的数据量，某时批量插入了200w），也有可能会导致执行计划错误。

3.SQL走错执行计划跟SQL本身是没有关系，SQL的执行计划是由ORACLE内部算法生成（RBO，CBO）,同一SQL可能拥有多个执行计划，ORACLE会根据表，索引等的信息来选择使用哪一个执行计划。比如某张表原来只有10条记录，ORACLE会认为走全表扫描会优于索引扫描，但当表记录变化到10w，可能走索引更快。

4。是否定时更新表上的统计信息就能解决这个问题呢？这一直是个争论的话题。官方也未有明确说法，所以这个风险在目前网站各个数据库都存在的，我们目前的解决方案是只对遇到问题表进行分析（已准备在11g的数据库上尝试由ORACLE定时任务去分析）

5.由于这个问题不确定性，性能测试其实是无法规避这个风险的。

6.我们这样做可以最大程度减小这个问题带来的风险：

   1。关注数据量巨大的表。（目前和预计增长很快的表）

   2。关注数据量变化较大的表（比如插入，删除频繁，有批量删除插入的表）

   3。关注SQL性能要求很高的SQL（执行频率很高的SQL）

总的说来这个问题不是程序BUG，不是SQL本身的问题，无法验证，其实也很难模拟重现。呵…毕竟ORACLE也是人写的程序没有那么十全十美，所以DBA每次新项目上线都会很关注是否有新的TOP SQL，偶尔会发现这样的问题，对需要关注的表和SQL大家做好沟通能最大程度减小这个风险。

 

 

 

 

 

评论

1 楼 youjianbo_han_87 2012-03-05  

这种问题原因千奇百怪，你总结的原因，没有普遍性