>
> {커서가 내려가지 않는군요}
> {손님(guest)}
>
> 오라클 8i을 사용하고 있는데 커서의 수가 계속 증가 하다가 내려 가지를 않습니다.
> 그래서 코딩이 잘못 되었나 해서 전부 확인을 해서 finally안에 ResultSet과 Statement,
> Connection 부분의 close()을 확실히 했으면 몇개의 페이지만 가지고도 테스팅을 해
> 보았습니다. 하지만 여전히 커서는 내려오지 않습니다.
> 그래서 Connection Pool을 쓰지 않고 직접 Oracle Driver(type 4, thin Driver)을 사용하서
> 하나의 쿼리를 날리면 정상적으로 커서가 내려 오는데 이건 Connection Pool만 사용하면
> 커서의 수가 내려 오지 않는군요.
>
> ..(중간생략)...
>
> 제가 궁금한것은 WebSphere에서도 똑같은 문제가 발생하는데 과연 이렇게 하면 성공하는지
> 그리고 왜 이런 현상이 발생 하는지 이런 문제에 대해서 고민하시다가 해결하신분
> 있으면 답변 부탁드립니다.
>
------------------------------------------------------------------------------
>
> {Connection Pool 과 prepareStatement ...}
> {문장용(guest), mjdragon@chollian.net}
>
> 안녕하세요.
> 항상 자바서비스넷을 애용하는 사람입니다.
>
> 저의 질문은 prepareStatement에 관한 것입니다.
>
> finally에서 정상적으로 close를 하고 있습니다.
> 그런데 statement resource가 하나씩 줄어들고 있습니다.
>
> 제가 찾은 결론을 말씀드리면
> prepareStatement의 query문장이 최초로 한번 실행되어질 때
> 그 statement가 하나씩 줄어드는 것 같습니다.
> 그다음에 똑같은 서블릿을 실행했을 때는 statement가 줄어들지 않습니다.
>
> was의 connection pool을 사용했을 때 발생하는 문제인것 같은데...
> 다음 소스를 보시고 틀린 곳이 있으면 찾아주세요... 제발~~~
>
> ...(소스 생략)...
>
다음과 같은 소스로 위 증상을 테스트 해 보았습니다.
-------------------------------------------------------------------------------
<%@ page contentType="text/html; charset=euc-kr" %>
<%@ page import="java.util.*,java.sql.*,javax.sql.*,
javax.naming.*" %>
<%!
private static String source = "jdbc/ORA8i_THIN";
//private static String source = "jdbc/ORA8i_OCI";
private static String user = "scott";
private static String password = "tiger";
private static DataSource ds = null;
private static int MAX_COUNT = 250;
private static int count = 0;
private static String[] queries = new String[MAX_COUNT];
static {
StringBuffer blanks = new StringBuffer();
for(int i=0;i<MAX_COUNT;i++){
queries[i] = "select count(*) " + blanks.toString() + "from tab";
blanks.append(' ');
}
}
public int getNumOfStmt(Connection conn) {
int n = -1;
Vector stmts = new Vector();
try {
for(n=0;n<5000;n++){
Statement stmt = conn.createStatement();
stmts.addElement(stmt);
}
}
catch(Exception e){}
finally{
Enumeration enum = stmts.elements();
while(enum.hasMoreElements()){
Statement stmt = (Statement)enum.nextElement();
try{stmt.close();}catch(Exception e){}
}
}
return n;
}
%>
<html><body><xmp>
<%
try {
java.util.Hashtable props = new java.util.Hashtable();
props.put(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.ejs.ns.jndi.CNInitialContextFactory");
Context ctx = null;
try {
ctx = new InitialContext(props);
ds = (DataSource)ctx.lookup(source);
}
finally {
if ( ctx != null ) ctx.close();
}
}
catch (Exception e) {
out.println(e.toString());
}
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ds.getConnection(user, password);
out.println("before:" + getNumOfStmt(conn));
try {
pstmt = conn.prepareStatement(queries[count++]);
if ( count >= MAX_COUNT ) count = 0;
ResultSet rs = pstmt.executeQuery();
if( rs.next() ){
out.println("query : ok (" + rs.getString(1) + ")");
}
rs.close();
}
finally {
if ( pstmt != null ) try { pstmt.close();}catch(Exception e){}
}
out.println("after:" + getNumOfStmt(conn));
}
catch(Exception e){
out.println("ERROR: " + e.toString());
}
finally {
if ( conn != null ) try { conn.close();}catch(Exception e){}
}
%>
</xmp></body></html>
-------------------------------------------------------------------------------
NOTE: 이 샘플은 반드시 Oracle에서만 테스트하세요.
Oracle 8.1.6 JDBC Thin Driver 를 이용하여 Windows2000, WebSphere 3.5.4 에서 테스
트한 결과 앞서 질문한 분이 얘기한 증상이 재현되었습니다.
그러나, 이 부분은 질문한 분이 우려한 것과 같은 제품의 버그는 아닙니다. 어플리케이션
서버 제품군들에서는 대부분 자사 제품의 성능향상을 위해 갖가지 노력을 하기 마련입니다.
"PrepareStatement 에 대한 캐싱기능"이 대표적이 예가 될 것입니다.
WebSphere 어플리케이션서버는 DB Vendor 가 제공하는 JDBC Driver 를 이용하지만,
그것을 그대로 사용치 않고 추가적인 기능을 제공하기 위해, 별도의 Proxy 클래스를 구현
하여 동작하고 있습니다.
개발자의 프로그램 소스로 runtime 시에 넘어 오는 java.sql.PreparedStatement Interface 의
instance 는 DB Vendor 가 제공하는 구현클래스의 instance 일 것 같지만, 사실은
com.ibm.ejs.cm.proxy.OraclePreparedStatementProxy 와 같이 DB 에 따라 미리 준비된,
WebSphere 에서 한번 더 Wrapping 된 클래스의 instance 인 것입니다.
여기에 중요한 기능 중의 하나가 PreparedStatement 에 대한 캐싱기능입니다.
동일한 형태의 SQL 문장이 한번 수행되면, 그 문장에 해당하는 PreparedStatement 는
개발자가 명시적으로 pstmt.close() 를 수행하여도, 실제 release 되지 않고, 내부적으로
캐싱을 위한 Hashtable 에 남아 있습니다. 이렇게 함으로써, 동일한 SQL문장을 다시
요청할 경우에 DB 와의 파싱단계가 생략됨으로 인해 성능을 높이려는 것이지요...
(물론 PreparedStatement 자체가 그러한 기능이 있지만, 그것은 그 PreparedStatement가
실질적으로 close() 되지 않고 반복적으로 사용될 때의 경우에만 그렇습니다. 그러나
실 프로젝트의 개발패턴은 PreparedStatement 를 사용하고 곧바로 pstmt.close()를
할 수 밖에 없는 개발구조상의 형태와 상충되고 있기에 실질적인 PreparedStatement의
캐싱기능은 DB Vendor 의 JDBC Driver 만으로는 전혀 효과가 없었습니다.)
이러한 내용은 TheServerSide.com 의 아래 글에서 아주 상세히 설명되고 있습니다.
http://www.theserverside.com/resources/article.jsp?l=Prepared-Statments
특별한 지정이 없는 한, WebSphere V3 의 경우, PreparedStatement 에 대한 캐시 사이즈
(statementCacheSize)는 100 입니다.
이 문제는 다소 깊이 있게 고민해 보실 법한 사항인데, 예를 들어, Oracle 의
init???.ora 파일에서 "open_cursors = 50" 이 defualt 값입니다. (실 프로젝트에서는 이
수치를 최소한 200 이상으로 올리곤 합니다.) 만약, 서로다른 PreparedStatement 의 갯수가
50 개가 넘게 존재하고 있다면,(대부분의 프로젝트에서 50개 이상은 되겠지요), 운영중에
다음과 같은 에러를 만나실 수 있습니다.
-------------------------------------------------------------------------------
before:1
ERROR: java.sql.SQLException: ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-01000: 최대 열기 커서 수를 초과했습니다
before:0
ERROR: com.ibm.ejs.cm.portability.ResourceAllocationException:
ORA-01000: 최대 열기 커서 수를 초과했습니다
-------------------------------------------------------------------------------
즉, PreparedStatement에 대한 Cache Size의 default 가 100 이기 때문에, 서로다른
SQL 문장을 50개 이상 PreparedStatement 를 통해 수행할 경우, 100 개 이하의 모든
PreparedStatement 는 실제 close() 되지 않고 캐싱에 남아 있게 되는데, 50개를
넘는 순간 위와 같이 "open cursor exceed" 에러를 만나게 되는 것입니다.
(이 같은 현상은 Oracle 의 open_cursor 를 10 정도로 낮추어 놓으면 쉽게 재현할
수 있습니다.)
WebSphere 3.5.x 에서 PreparedStatement 에 대한 캐시 사이즈는 당연히 조정할 수
있습니다. 이것을 지정하는 방법은 $WAS_HOME/properties/ 디렉토리에 "datasources.xml"
이라는 파일을 (존재하지 않으면 새로 만들고) 수정해 줌으로 가능합니다.
[$WAS_HOME/properties/datasources.xml]
-----------------------------------------------------
<?xml version="1.0"?>
<data-sources>
<data-source name="DB2_61_APP">
<attribute name="CURSORHOLD" value="1" />
</data-source>
<data-source name="ORA8i_THIN">
<attribute name="statementCacheSize" value="50" />
</data-source>
<data-source name="ORA8i_OCI">
<attribute name="statementCacheSize" value="200" />
</data-source>
</data-sources>
-----------------------------------------------------
개별적으로 웹스피어 관리콘솔에서 정의한 데이타소스 이름을 각각 기술하고, 각각
지정해 주어야 합니다. 따라서, 데이타 소스의 이름을 변경한 경우, 이 파일의 내용도
함께 변경해야 됨을 주지하십시요.
(수치를 5정도로 낮게 준 후, 앞의 테스트JSP를 다시 돌려 보십시요. 5개 이상은
캐싱되지 않을 겁니다.)
권장하는 방법은 WebSphere의 PreparedStatement 캐싱기능은 독특하면서도,기능적으로
우수하며, defualt 100 은 대부분의 경우 적절한 수치입니다. 오히려, Oracle 의
default "open_cursors=50" 을 200 이상으로 올리실 것을 권합니다.
WebSphere V3 각종 Performance Tuning 자료에도 약방의 감초처럼 나오는 문구로서,
캐시의 사이즈는 보통 해당 프로젝트에서 자주 사용되는 PreparedStatement의 서로다른
SQL 문장의 갯수로 마추시면 됩니다.
NOTE: 캐시 사이즈는 각 Connection 별로 적용됩니다. 예를 들어 Connection Pool 에
총 2개의 DB연결이 물리적으로 맺어져 있고, 그 중 하나의 Connection 에 특정 SQL문장이
PrepareStatement 를 통해 캐시되어 있을 지라도, 동일한 SQL문장이 다른 Connection 을
통해 다시 실행될 때, 그 Connection 에 대해 또다시 캐싱하게 됩니다.
NOTE: java.sql.Statement 를 사용할 경우는 캐싱기능이 동작하지 않습니다. 오로지
java.sql.PreparedStatement 만 사용할 경우에 적용받습니다.
NOTE: WebSphere 3.0.2.x 에서는 관리콘솔의 "매개변수"에 다음과 같은 옵션을 지정하면
됩니다.
-Dcom.ibm.ejs.dbm.PrepStmtCacheSize=200
NOTE: 2001.10.31
Oracle 의 경우, 현재 열려진 총 Statement의 개수를 보는 방법이 있습니다.
-------------------------------------------
select sid, count(*) cnt from v$open_cursor
where user_name = 'username'
group by sid
order by cnt desc
-------------------------------------------
(도움주신분: 정헌식, vblove@buttle.co.kr)
NOTE: UDB DB2 의 경우는 매우 신중하셔야 합니다. 마찬가지로 PreparedStatement는
default의 경우 100 개까지는 서로다른 SQL 문장에 대해서 release 되지 않고 캐싱
됩니다. 그러나 DB2 는 Oracle 과 같은 cursor 수치를 제어하는 곳이 없습니다.
유일하게 APPLHEAPSZ 값을 높여 줌으로써 더 많은 Statement 를 만들 수 있습니다.
만약, H/W 적인 메모리가 충분치 않은 상황에서 APPLHEAPSZ 만 무턱대고 올려 놓을
경우 서로 다른 다량의 PreparedStatement 를 수행할 때, SIGSEGV 를 야기할 수 있으며
혹은 아래와 같은 에러를 만나실 수 있습니다.
1) CLI0129E 핸들(handle)이 더이상 없습니다. SQLSTATE=HY014
2) SQL0954C 응용프로그램 힙(heap)에 명령문을 처리하기 위해 사용 가능한 저장영역이
충분하지 않습니다. SQLSTATE=57011
3) CLI0601E 유효하지 않은 명령문 핸들 또는 명령문이 닫혔습니다. SQLSTATE=S1000
그래서, DB2의 APPLHEAPSZ 는 default 126 에서 256 으로 올리도록 설치시에 권장하고
있으며,메모리가 2GB가 안되는 시스템에서는 캐시사이즈를 10-50 정도로 줄이십시요.
그러나, 위 에러가 발생하는 대부분의 원인은 (경험상) 개발자의 코드에서 Statement
를 제때에 close() 하지 않았을 경우가 더욱 많았다는 점도 간과하시면 안됩니다.
91 Servlet & JDF & DB2 CLI0129E no more handles
http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=servlet&c=r_p&n=977247084
PS: "CURSORHOLD" 에 대한 내용은 아래 글을 참고 하세요.
345 WebSpere V3, DataSource + DB2 6.1(fixpack4)
http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=990527303
350 Re: Function sequence error
http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=991154615
----------------
2002.01.21 추가
NOTE: The datasources.xml file has been removed in Version 4.0. The functionality
provided through the use of this file is now part of the administrative console.
For example, the statement cache size can now be set during datasource
configuration on the administrative console.
-------------------------------------------------------
본 문서는 자유롭게 배포/복사 할 수 있으나 반드시
이 문서의 저자에 대한 언급을 삭제하시면 안됩니다
================================================
자바서비스넷 이원영
E-mail: javaservice@hanmail.net
PCS:011-898-7904
================================================
|
