问题
今天遇见java.sql.SQLException: ORA-01000: maximum open cursors exceeded。
什么是open cursor
OPEN CURSOR是指一个session同时打开的cursor数目:
问题分析
1.首先看看哪些session用了最多的cursor。
点击(此处)折叠或打开
- @getnumberopencursor.sql
- set linesize 120
- col machine format a30
- set heading on
- select o.sid, osuser, machine, count(*) num_curs
- from v$open_cursor o, v$session s
- where user_name = '&username' and o.sid=s.sid
- group by o.sid, osuser, machine
- order by num_curs desc;
2.查看是不是同样的语句。
点击(此处)折叠或打开
- select * from v$open_cursor where sid = 695 order by sql_text
下面的这条update语句执行了很多次。
点击(此处)折叠或打开
- UPDATE TEST SET TEST_STATUS=? WHERE TEST_ID=?
3.开发查了查代码,发现有一段循环代码调用,并在循环中创建PreparedStatement.
点击(此处)折叠或打开
- ps = conn.prepareStatement(sql);
- ps.setString(1, "ACTIVE");
- rs = ps.executeQuery();
- while (rs.next())
- {
- ......
- sql = "UPDATE TEST " + " SET TEST_STATUS=?"
- + " WHERE TEST_ID=? ";
- ps = conn.prepareStatement(sql);
- ps.setString(1, "EXPIRED");
- ps.setString(2, testId);
- ps.execute();
- }
- }
4. java对象和oracle对象的对应关系
Connection 对应oracle 的session
Statement 对应oracle的cursor
5. 测试v$open_cursor的变化
col user_name format a10
select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
执行一条语句:
点击(此处)折叠或打开
- select distinct sid from v$mystat SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
- SID USER_NAME SQL_ID SQL_TEXT CURSOR_TYPE
- ---------- ---------- ------------- ------------------------------------------------------------ ---------------------------------
- 136 SYS 7d3s4yhub46g3 select * from test where rownum <20 OPEN
- 136 SYS 6ubd0c60r1tr9 select distinct sid from v$mystat DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS 7fqyv7yaqusur SELECT /* OPT_DYN_SAMP */
再执行一条语句:select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
点击(此处)折叠或打开
- SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
- SID USER_NAME SQL_ID SQL_TEXT CURSOR_TYPE
- ---------- ---------- ------------- ------------------------------------------------------------ -----------------------------------
- 136 SYS 6ubd0c60r1tr9 select distinct sid from v$mystat OPEN
- 136 SYS 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS 7fqyv7yaqusur SELECT /* OPT_DYN_SAMP */
再执行plsql(从asktom网站)
点击(此处)折叠或打开
- declare
- type rc is ref cursor;
-
- l_cursor rc;
- begin
- for i in 1 .. 2
- loop
- for j in 1 .. 500
- loop
- open l_cursor for 'select * from dual xx' || i ||j;
- close l_cursor;
- end loop;
- end loop;
- --dbms_lock.sleep(100);
- end;
- /
- SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
- SID USER_NAME SQL_ID SQL_TEXT CURSOR_TYPE
- ---------- ---------- ------------- ------------------------------------------------------------ -----------------------------------
- -
- 136 SYS 9mpxx61c3w43c select * from dual xx2472 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS g30bjwk32s70a select * from dual xx2460 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS c7pwjh6s9c8s1 select * from dual xx2451 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS b66u3729fh9f1 select * from dual xx2468 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS a3t3chatn0b3f select * from dual xx2496 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS 8z02s5f2v4cfu select * from dual xx2499 DICTIONARY LOOKUP CURSOR CACHED
- ...
- 136 SYS 04608tk6nzztd select * from dual xx2483 DICTIONARY LOOKUP CURSOR CACHED
- 已选择51行。
SQL> alter session set session_cached_cursors=200;
会话已更改。
再执行plsql(从asktom网站)
点击(此处)折叠或打开
- declare
- type rc is ref cursor;
-
- l_cursor rc;
- begin
- for i in 1 .. 2
- loop
- for j in 1 .. 500
- loop
- open l_cursor for 'select * from dual xx' || i ||j;
- close l_cursor;
- end loop;
- end loop;
- --dbms_lock.sleep(100);
- end;
- /
- SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
- SID USER_NAME SQL_ID SQL_TEXT CURSOR_TYPE
- ---------- ---------- ------------- ------------------------------------------------------------ --------------------------------------------------------
- 136 SYS 6f0t5d14qs7as select * from dual xx2331 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS 70a87hvxd47uf select * from dual xx2364 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS c7pwjh6s9c8s1 select * from dual xx2451 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS 7z4gvyu48h982 select * from dual xx2410 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS b66u3729fh9f1 select * from dual xx2468 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS d7k4574uk8amr select * from dual xx2320 DICTIONARY LOOKUP CURSOR CACHED
- 136 SYS a3t3chatn0b3f select * from dual xx2496 DICTIONARY LOOKUP CURSOR CACHED
- ...
- 136 SYS 04608tk6nzztd select * from dual xx2483 DICTIONARY LOOKUP CURSOR CACHED
- 已选择201行。
- declare
- type rc is ref cursor;
-
- l_cursor rc;
- begin
- for i in 1 .. 2
- loop
- for j in 1 .. 500
- loop
- open l_cursor for 'select * from dual xx' || i ||j;
- -- close l_cursor;
- end loop;
- end loop;
- --dbms_lock.sleep(100);
- end;
- /
- select a.name, b.value
- from v$statname a, v$mystat b
- where a.statistic# = b.statistic#
- and lower(a.name) like '%' || lower('cursor')||'%'
- /
- NAME VALUE
- ---------------------------------------------------------------- ----------
- opened cursors cumulative 2122
- opened cursors current 1
- pinned cursors current 1
- CCursor + sql area evicted 0
- session cursor cache hits 81
- session cursor cache count 200
- cursor authentications 5
- NAME VALUE
- ---------------------------------------------------------------- ----------
- opened cursors cumulative 3124
- opened cursors current 1
- pinned cursors current 1
- CCursor + sql area evicted 0
- session cursor cache hits 82
- session cursor cache count 200
- cursor authentications 5
- NAME VALUE
- ---------------------------------------------------------------- ----------
- opened cursors cumulative 4126
- opened cursors current 1
- pinned cursors current 1
- CCursor + sql area evicted 0
- session cursor cache hits 83
- session cursor cache count 200
- cursor authentications 5
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-696667/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25105315/viewspace-696667/