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();
}