[SQL Exeption] ORA-01000 open cursor 에러

이슈 발생 

웹 접속 시 SQL Exception으로인해 로그인이 안되는 상황 

Open Cursor?
-- v$open_cursor에서 session과 cursor수를 조회하여 어떤 쿼리가 cursor를 잡고 있는지 확인

select sid, count(sid) cursor
from v$open_cursor
where user_name = '[user_name]'
group by sid
order by cursor desc;

-- 쿼리별로 sid의 개수를 확인 - SQL문당 사용하는 커서수

select sql_text, count(sid) cnt
from v$open_cursor
where user_name = '[user_name]'
group by sql_text
order by cnt desc;
 

-- sid의 serial 확인 (문제가 되는 쿼리의 sid 를 통해 세션확인)

select sid, serial#, username
from v$session;

--결과값의 sid와 username을 파악한 후 문제가 되는 session있다면 죽여라

alter system kill session 'sid,serial#';

커서 수 올리기

open_cursors 설정

ALTER SYSTEM SET open_cursors=1000;

maximum open cursor 수 확인

show parameter open_cursors;

원인 분석 1

ibatis 프레임 워크를 사용하므로 connection 이나 preparedstatement를 직접 열고 닫을 일은 없으므로 소스내에서 close하지 않은 부분을 찾는 해결방법을 의미가 없다. 

참고로 ibatis는 모든 쿼리를 prepared statement로 실행시킨다. 기본적인 statement pool도 제공한다. (JDBC3.0 스펙에 해당됨)

원인 분석 2
statement pooling은 JDBC 3.0에 정의된 명세다. JDBC 드라이버가 3.0 명세를 지원하지 않으면 사용할 수 없는 기능이다. 하지만 JDBC 2.0 명세만 지원하는 JDBC 드라이버를 사용할 때도 커넥션 풀로 Commons DBCP를 사용하고 있다면 poolPreparedStatements 옵션을 true로 설정해서 Commons DBCP를 커넥션 풀뿐만 아니라 statement pool로도 사용할 수 있다. 이때는 반드시 maxOpenPreparedStatements 옵션을 같이 사용해 커넥션당 풀링할 PreparedStatement의 적절한 개수를 설정해야 한다. 그렇지 않으면 런타임에서 메모리 부족(out of memory) 오류 등이 발생할 수 있다.
maxOpenPreparedStatements 값은 문제가 발생지 않도록 50 정도로 작게 설정한 후 데이터베이스 관리자의 도움을 얻어 PreparedStatement의 캐시 적중률(hit ratio)을 관찰한 후 조정하기를 권장한다. 여기서 설정한 PreparedStatement 개수는 개별 커넥션마다 할당된다. 즉 커넥션 풀에 10개의 커넥션이 있을 때 maxOpenPreparedStatements = 50이라면 총 10 x 50 = 500개의 PreparedStatement가 캐시에 저장된다. 절대 BasicDataSource 클래스에 설정되는 개수가 아니다. 
아래 connection pool에 대한 자세한 설명이 있음

http://d2.naver.com/helloworld/5102792

Parameter	Default	Description
poolPreparedStatements	false	Enable prepared statement pooling for this pool
maxOpenPreparedStatements	unlimited	The maximum number of open statements that can be allocated from the statement pool at the same time, or negative for no limit.

This component has also the ability to pool PreparedStatements. When enabled a statement pool will be created for each Connection and PreparedStatements created by one of the following methods will be pooled:
public PreparedStatement prepareStatement(String sql)
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)

NOTE - Make sure your connection has some resources left for the other statements. Pooling PreparedStatements may keep their cursors open in the database, causing a connection to run out of cursors, especially if maxOpenPreparedStatements is left at the default (unlimited) and an application opens a large number of different PreparedStatements per connection. To avoid this problem, maxOpenPreparedStatements should be set to a value less than the maximum number of cursors that can be open on a Connection.

https://commons.apache.org/proper/commons-dbcp/configuration.html

 
결론.
현재 프로젝트 소스 설정파일에 보면 poolPreapredStatements가 true로 설정되어있다. 즉 커서가 열려있는 상태인데, 이때 maxOpenPreparedStatements라는 값을 설정해주지 않으면 default로 unlimited값이 된다.
따라서 대량으로 sql문을 수행하는 동작을 할 경우 커서가 다량으로 열리고 닫히지 않은 상태가 되므로 관련 Exception이 발생하게 된다. 따라서 해결방법은 poolPreparedStatemets 값을 false로 설정해주거나, true로 설정해줄 경우 maxOpenPreparedStatements값을 설정해주어야 한다.
* 문제는 이 maxOpenPreparedStatements값을 어느정도로 설정해주어야 적절한지 모르겠음..