ORA-01000: maximum open cursors exceeded, 引发的JDBC, DBPool的思考

1.首先我们来看oracle为什么会产生这个错误,默认的数据库最大的cursors是多少,当前使用又是多少.

1.1 Oracle不同数据库版本能打开的最大的的游标数和缓存的游标数略有不同，下面是windows 64 11.2版的结果.

SQL> show parameter cursors;   （或者show parameter open_cursor;）
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
session_cached_cursors               integer     50

1.2 其中open_cursor是每个session所支持的最大游标数目，如果超出这个数据,就会抛出ORA-01000号错误.

1.3 那么我们如何修改最大它呢,是不是越大越好呢?

alter system set open_cursors=1000 scope=both;可以修改支持的最大游标数目，但所能支持到的最大数目与share pool的大小有关

1.4 session_cached_cursors 和 open_cursor的区别是什么呢? session_cached_cursor 是指在PGA区存放的一个游标的缓存，open_cursor是指实际打开的软引用。

 

2.接下来我们看看oracle是如何管理cursor的，只有当当前session失效时，oracle才会清除所有连接的游标。

查看数据库打开的游标：

 select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

2.1 conn = DriverManager.getConnection(jdbcURL, user, passwd); // 取得连接时打开一个cursor

    TOTAL_CUR AVG_CUR MAX_CUR USERNAME MACHINE
     1                     1                        1      SCOTT        ASUS

     stmt.executeQuery("SELECT * FROM emp"); // 执行sql时打开一个cursor

     rs.close() // 不会release cursor

     stmt.close() // 不会release cursor

     conn.close() // 释放所有游标

2.2 使用连接池的问题，由于连接池技术是数据库物理连接的一个池，当从connetionPool取得的连接并没有真正释放物理连接，所以如果同一个物理连接被再次获取到后，打开的游标数会继续累加，增加了ORA-01000发生的概率。

2.3 如果创建statment, 即便执行相同的sql在同一个connection中，也会引起cursor的增加 （statement 没有close, 产生了leak）

conn.createStatement().executeQuery("SELECT * FROM emp");//增加一个

conn.createStatement().executeQuery("SELECT * FROM emp");//同一个connection，创建了另一个statment实例，又增加了一次

2.4 如果创建PreparedStatement/CallableStatement，也引起cursor的增加，即便sql相同（statement 没有close, 产生了leak）

PreparedStatement pre = conn.prepareStatement("SELECT * FROM bonus");pre.execute();//;//同一个connection，创建了另一个statment实例，又增加了一次

2.5 statement 的重用问题

Statement stmt = conn.createStatement();

stmt.executeQuery(sql1);

...

stmt.execteQuery(sql2);

以上正解，以下误解

stmt = conn.createStatement();

stmt.executeQuery(sql1);

stmt = conn.createStatement();

stmt.executeQuery(sql2);

2.6 Spring JdbcTemplate的好处

由于spring使用JdbcTemplate,并且在execute的方法是实际使用的callback，具体参照源代码，下面给出伪代码如下

for(1 : 301){

jdbcTemplate.execute("sql")  // 这是数据库游标始终保持为2，但如果使用conn.createStatement().executeQuery("sql")将会01000错误

}

根本原因是

jdbcTemplate.execute() =>

try{

conn // get connection from ds.

stmt // do create stmt

}catch(exception e){

}finally{

stmt.close()

conn.release() // release connection back to DBPool, If it's in transaction, do not close connection. 也就是说他及时的关闭了

}

2.7 如何在rs中使用createStatement的问题, 下面的代码有潜在oracle游标益处的风险，应该极力避免类似的嵌套。

Stmt stmt = conn.createStatement();

ResultRest rs = stmt.executeQuery(sql1);

while(rs.next()){

String name = rs.getString(1);

PreparedStatement ps = conn.preparedStatement(sql2);

ps.setString(1, name);//忘记关闭

}

改进如下，但不能根本解决

while(rs.next()){

jdbcTempate.executeUpdate();// 根据数据量和pool的大小调整

}

while(rs.next()){

connection conn1 = DBPool.getConnection(); // 根据数据量和pool的大小调整，

PreparedStatement ps = conn1.preparedStatement(sql2);

ps.setString(1, name);

ps.close();

conn1.close();

}