Maximum open cursor error에 관해

Maximum open cursor error에 관해

 

잡로 JDBC를 이용하여 프로그래밍 할 때 반복문(for, while)안에 Statement나 PreparedStatement등이 위치하는 경우엔 간혹 “ORA-01000: 최대 열기 커서 수를 초과했습니다” 라는 오류를 만날 수 있습니다.

 

==============================

오라클 서버에서 해야 할 일

==============================

 

이 경우엔 우선 오라클 서버의 initSID.ora 파일에서 open_cursors 파라미터 값(기본값은 50)을 늘여 줄 수도 있지만 제가 봤을 때는 근본적인 해결책은 아닙니다.

 

SQL문은 SQL문의 내용이 같아도 (host 변수를 사용해서 값만 바뀌고 SQL문은 그대로인 경우라도) 무조건 실행 될 때 마다 Oracle 서버의 Cursor를 묵시적으로 할당 합니다.

 

어떤 프로그램이 for, while 루프 안에서 5개의 SQL문을 실행한다고 가정하고, for 루프가 10000번을 돈다고 하면 프로그램은 총 50000 개의 Oracle Implicit Cursor를 사용하게 되는 것입니다.

 

이것은 OPEN_CURSORS 파라미터를 50000개 이상으로 설정하지 않으면 ORA-01000 에러를 만날 수 있다는 이야깁니다. 그러나 실제 SMON이 적정한 수 이상의 Cursor가 열려있으면 이를 정리해주므로 50000 개 정도로 크게 OPEN_CURSORS 파라미타를 설정할 필요는 없습니다.

 

SMON이 특성을 고려 했을 때 보통 1000 ~ 2000개 정도면 적당 합니다. 오라클의 Cursor는 1개당 25 byte 의 메모리를 사용하며 MTS에서는 SGA의 영역이 사용되므로 너무 크게 잡지 않도록 하고 SGA를 충분히 크게 잡아주는 것이 필요 합니다.

 

아래 SQL문으로 현재 커서를 많이 오픈 해 놓고 있는 프로세스를 찾을 수 있는 방법이 있으니 참고 바랍니다.

 

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

-- Open 된 Cursor 수의 조회
-------------------------------------------------------------

select * from ( select sid, count(*)

from v$open_cursor

group by sid  

order by 2 desc )

where rownum <= 10;

 

select a.sid, s.process, count(*)

from V$OPEN_CURSOR a, V$SESSION s 

where s.sid = a.sid 

group by a.sid, s.process 

having count(*) > 100 

order by count(*) desc;

 

maximum open cursor 수를 파악하고자 하면, 다음과 같이 sqlplus나 svrmgrl 에서 sys유저로 접속해서 show parameter 명령을 사용하면 됩니다.

 

SQL> show parameter open_cursors

 

NAME                 TYPE      VALUE
------ ------- ------------------------------
open_cursors          nteger    2000

 

 

==============================

JDBC 프로그램에서 해야 할 일

==============================

 

Masimum Open Curdor  문제를 해결하기 위해서 다음과 같이 코딩 할 수도 있습니다.

 

for()

{

 Statement stmt = conn.preparedStatement();

 rs = stmt.executeQuery(sql);

 stmt.close();   //루프안에서 close 해 버림

}

 

이러한 경우엔 위 오류는 발생하지 않겠지만 성능상의 문제가 있을 수 있으므로 아래처럼 PreparedStatement를 사용하시는 것이 좋습니다.

 

 

PreparedStatement pstmt = conn.PreparedStatement(sql);

for()

{

 pstmt.setString(1, value);

 ………

 

 rs = pstmt.executeQuery();

}

pstmt.close();

[출처] ORACLE & JAVA - http://www.oraclejava.co.kr/bbs/board.php?bo_table=LecJDBC&wr_id=57