ORACLE/TroubleShooting2010. 4. 1. 15:10

ORA-01000: maximum open cursors exceeded" 조사

반응형
문제 설명
Oracle은OPEN_CURSORS매개변수를 사용하여 세션이 동시에 취할 수 있는 최대 열린 커서 개수를 지정합니다. 최대 개수를 초과하면 Oracle은ORA-01000오류를 보고합니다. 이 오류가 WebLogic Server로 전송되면SQLException이 발생합니다.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

이 패턴은 WebLogic Server를 사용할 때 오류를 발생시키는 원인과 해결 방법에 대해 설명합니다.

문제 해결
다음 항목을 모두 수행해야 하는 것은 아닙니다. 어떤 경우에는 다음 중 일부만 수행하여도 해결할 수 있습니다.

항목 바로가기

진단 조회
다음 SQL 조회는ORA-01000문제를 진단하는 데 유용합니다. 이런 조회를 실행하려면 데이터베이스에 관리자로 로그인하거나 데이터베이스 관리자가 사용자에게v$뷰에서 SELECT 명령문을 실행할 수 있는 권한을 승인해야 합니다.

1. 데이터베이스의 OPEN_CURSORS 매개변수 값을 확인합니다.
Oracle 은OPEN_CURSORS초기 화 매개변수를init.ora에 사용하여 세션이 동시에 취할 수 있는 최대 커서 개수를 지정합니다. 디폴트값은 50이지만, WebLogic Server와 같은 시스템에는 너무 작습니다. 다음 조회를 사용하여 데이터베이스에서OPEN_CURSORS매개변수 값을 찾을 수 있습니다.
 
SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1000

 

OPEN_CURSORS값을 충분히 큰 값으로 설정하여 응용 프로그램에서 열린 커서가 부족하지 않도록 해야 합니다. 이 수는 응용 프로그램에 따라 다릅니다. 세션이OPEN_CURSORS에 지정된 커서 개수만큼 열지 않는 경우 이 값을 실제 필요한 값보다 크게 설정해도 오버헤드가 추가되지 않습니다.

2. 열린 커서의 수를 확인합니다.
아래 조회는 사용자 'SCOTT'가 각 세션에 대해 연 커서 개수를 내림차순으로 표시합니다.
 
SQL> select o.sid, osuser, machine, count(*) num_curs
  2  from v$open_cursor o, v$session s
  3  where user_name = 'SCOTT' and o.sid=s.sid
  4  group by o.sid, osuser, machine
  5 order by  num_curs desc;       SID OSUSER               MACHINE                                              NUM_CURS
---------- ---------------- ------------------------------------------------- ----------
       217                                m1                                                           1000
        96                                 m2                                                            10
       411                                m3                                                             10
        50                                test                                                              9

WebLogic Server에서 커넥션 풀을 사용할 때 커넥션을 커넥션 풀에서 가져온 경우 이 조회의user_name은 커넥션을 생성하는데 사용한user_name이 어야 합니다. 조회 결과는 시스템 이름도 출력합니다. 조회 결과를 통해 열린 커서의 개수가 큰SID와 WebLogic Server를 실행하는 시스템 이름을 식별할 수 있습니다.

v$open_cursor는dbms_sql.open_cursor()를 사용하여 연 동적 커서인PARSED및NOT CLOSED를 세션에 대해 추적할 수 있습니다. 구문 분석 하지 않은 열린 동적 커서는 추적하지 않습니다. 응용 프로그램에서 동적 커서의 사용은 흔하지 않습니다. 이 패턴은 동적 커서가 사용되지 않는 것으로 간주합니다.

3. 커서에 대해 실행 중인 SQL을 확인합니다.
위의 조회 결과에서 식별한 SID를 취하고 다음 조회를 실행합니다.

SQL> select q.sql_text
  2  from v$open_cursor o, v$sql q
  3  where q.hash_value=o.hash_value and o.sid = 217;

SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from empdemo where empid='212'
select * from empdemo where empid='321'
select * from empdemo where empid='947'
select * from empdemo where empid='527'
...

 

결과는 커넥션 상에서 실행 중인 조회를 표시합니다. 이를 기준으로 열린 커서의 출처를 역추적할 수 있습니다.

페 이지 맨 위

일 반적인 원인과 문제 해결
다음 단계는 문제의 원인을 파악하고 가능한 해결 방법을 모색하는 절차입니다.

코드 연습
이 문제의 가장 일반적인 원인은 JDBC Object가 정상적으로 닫히지 않은 경우입니다. 모든 JDBC Object가 정상적으로 닫혔는지 확인하기 위해 응용 프로그램 코드에서 역추적하려면진 단 조회에 타사의 조회 결과를 사용합니다. 모든 JDBC Object가 정상적인 상태나 예외 조건에서 닫히도록 하려면finally블록에서 Connections, Statements 및 ResultSets 같은 JDBC Object를 명시적으로 닫는 것이 좋습니다. 다음은 일반적인 예제입니다.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
    conn = getConnection(); //Method getConnection will return a JDBC Connection
    stmt = conn.createStatement();
    rs = stmt.executeQuery("select * from empdemo");
    // do work
} catch (Exception e) {
    // handle any exceptions
} finally {
    try {
        if(rs != null)
            rs.close();
    } catch (SQLException rse) {}
    try {
        if(stmt != null)
            stmt.close();
    } catch (SQLException sse) {}
    try {
        if(conn != null)
            conn.close();
    } catch (SQLException cse) {}
}

 

JDBC Object를 버리는 코딩 습관을 피하십시오. 다음 연습은 각 루프 반복에서 새 Connection, Statement 및 ResultSet를 얻지만 각 반복에 대해 JDBC Object를 닫지는 않습니다. 그러므로 JDBC Object leak이 발생합니다.

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String[] queries = new String[10];
//Define queries

try {
    for(int i = 0; i < 10; i++) {
        conn = getConnection();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(queries[i]);
        // do work
    }
} catch (Exception e) {
    // handle any exceptions
} finally {
    try {
        if(rs != null) 
            rs.close();
    } catch (SQLException rse) {}
    try {
        if(stmt != null) 
            stmt.close();
    } catch (SQLException sse) {}
    try {
        if(conn != null) 
            conn.close();
    } catch (SQLException cse) {}
}


Connection을 닫을 때 Statement와 ResultSet를 닫아야 하지만 JDBC 사양에 따라 하나의 Connection Object에 여러 Statement를 작성한 경우에는 사용한 직후 Statement와 ResultSet를 명시적으로 닫는 것이 좋습니다. Statement와 ResultSet를 명시적으로 즉시 닫지 않으면 커서가 누적되어 Connection이 닫히기 전에 데이터베이스에 허용된 최대 개수를 초과할 수 있습니다. 예를 들어, 아래 코드 부분에서는finally블록에 서 Connection을 닫을 때 ResultSet와 Statement도 닫아야 합니다. 그러나 이 코드 부분은 하나의 Connection에 여러 Statement와 ResultSet를 생성합니다. 루프가 끝나기 전에 "maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제가 발생했을 수 있습니다.


Connection conn = null;

try{
    conn = getConnection();

    for(int i = 0; i < NUM_STMT; i++) {
        Statement stmt = null;
        ResultSet rs = null;
 
         stmt = conn.createStatement();
         rs = stmt.executeQuery(/*some query*/);
        //do work
    }
} catch(SQLException e) {
     // handle any exceptions
} finally {
    try{
        if(conn != null)
            conn.close();
    } catch(SQLException ignor) {}
}


페 이지 맨 위

명령문 캐시
성능 향상을 위해 WebLogic Server는 커넥션 풀을 사용할 때 prepared statements와 callable statements를 캐시하는 기능을 제공합니다. WebLogic Server가 prepared statements나 callable statements를 캐시할 때 많은 경우에 DBMS는 열린 각 명령문에 대해 커서를 유지합니다. 그러므로 명령문 캐시 기능은 "maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제의 원인이 될 수 있습니다. 명령문 캐시 크기 속성은 커넥션 풀의 각 인스턴스에서 각 커넥션에 대해 캐시할 prepared statements와 callable statements의 전체 개수를 결정합니다. 명령문을 너무 많이 캐시하면 데이터베이스 서버의 열린 커서 개수가 최대 개수를 초과할 수 있습니다.

WebLogic Server에서 디폴트 명령문 캐시 크기는 버전마다 다를 수 있습니다. 예를 들면 다음과 같습니다. 

""maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제가 명령문 캐시와 관련이 있는지 확인하려면 명령문 캐시 크기를 0으로 설정하여 이 기능을 해제하거나 캐시 크기를 줄여 오류가 계속 발생하는지 확인할 수 있습니다. 캐시 크기를 줄여도 문제가 발생하지 않으면 커넥션 풀의 원래 명령문 캐시 크기가 너무 크거나 DBMS의 최대 열린 커서 개수 제한이 너무 적은 것이므로, 이 중 하나의 값을 조정해야 할 수 있습니다. 커넥션에 열린 커서의 개수가 계속 증가하다가 명령문 캐시 크기를 0으로 설정했을 때 이런 동작이 나타나지 않으면 커서 leak 문제일 수 있습니다. 사용 중인 JDBC 드라이버가 원인이거나 WebLogic Server 버그일 수도 있습니다. 다른 JDBC 드라이버를 사용해 보십시오. 다른 JDBC 드라이버를 사용할 때도 동일한 문제가 발생하면 기술 지원 엔지니어가 이 문제를 세부적으로 조사하여 WebLogic Server 버그인지 확인할 수 있도록 BEA에 보고하십시오.

페 이지 맨 위

데이터베이스 드라이버
""maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제는 JDBC 드라이버가 원인일 수 있습니다. 드라이버가 문제인지 WebLogic 커넥션 풀이 문제인지 파악하기 위해 재현 가능한 테스트 케이스가 있으면 다음을 시도해 볼 수 있습니다.

1. 드라이버에서 커넥션을 직접 가져옵니다.
테스트 케이스에서 JDBC 커넥션은 드라이버에서 직접 가져오고 WebLogic 커넥션 풀은 우회합니다. 커넥션을 닫지 말고 배열이나 일부 다른 구조에 열어 두고 커서 leak 문제가 계속 발생하는지 확인합니다. 커넥션을 닫지 않는 이유는 커넥션 풀을 사용할 때의 동작을 시뮬레이션하기 위해서 입니다. 커넥션 풀을 사용할 때connection.close()는 커넥션을 실제로 닫지는 않으나 대신에 커넥션을 풀로 반환합니다.

2. 다른 JDBC 드라이버로 시도합니다.
타사의 JDBC 드라이버나 드라이버의 업데이트 버전으로 시도하여 문제가 계속 발생하는지 확인합니다. 메타 데이터를 사용하여 올바른 드라이버가 사용되었는지 확인할 수 있습니다. 샘플 코드는 다음과 같습니다.

Connection conn = getConnection();
DatabaseMetaData dmd = conn.getMetaData();
System.out.println("JDBC Driver Name is " + dmd.getDriverName()); 
System.out.println("JDBC Driver Version is " + dmd.getDriverVersion());

3. XA 드라이버 버그.
Oracle XA 드라이버를 사용 중이고 데이터베이스에 "SELECT count (*) FROM SYS.DBA_PENDING_TRANSACTIONS" 같은 쿼리가 많은 경우 Oracle XA 드라이버에 커서 leak 문제가 발생할 수 있습니다. 이 문제는 MetaLink Case 3151681에 설명되어 있으며 버전 10.1.0.2에서 수정되었습니다.
또한 XA 드라이버를 사용할 때http://e-docs.bea.com/wls/docs81/jta/thirdpartytx.html#1075181에 설명된 대로 데이터베이스 서버에 XA 사용을 설정해야 합니다. 즉,grant select on dba_pending_transactions to public명 령을 실행해야 합니다.

JDBC 드라이버가 문제이고 이 드라이버를 사용해야 할 경우 커서 leak 문제의 해결 방법은 WebLogic 커넥션을 가끔씩 재설정하거나 커넥션 풀을 축소하는 것입니다. 재설정하는 방법이나 커넥션 풀을 축소하는 방법은 WebLogic 설명서를 참조하십시오. 버전 8.1의 경우http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcconnectionpool_control.html에 설명되어 있습니다.


페 이지 맨 위

알려진 문제
사용하고 있는 WLS 버전의 릴리스 정보를 주기적으로 검토하여 서비스 팩에서 알려진 문제나 해결된 문제를 확인하고 ORA-01000 / 커서 Leak 관련 문제를 검색할 수 있습니다.다음을 참조하십시오.
특별 정보의 경우, 각 버전의 서비스 팩 릴리스 정보에서 해결된 것으로 표시된 다음 CR를 참고하십시오. 

검색하면 릴리스 정보뿐 아니라추 가 도움말에서 언급된 기타 지원 솔루션 및 CR 관련 정보도 알 수 있습니다. 계약 고객은http://support.bea.com/에 로그인한 다음 Browse 포틀릿에서 Solutions 및 Bug Central을 검색하여 제품 버전별로 사용 가능한 최신 CR을 찾을 수 있습니다.

페 이지 맨 위

추 가 도움말이 필요하십니까?
패턴대로 작업했지만 추가 도움말이 필요한 경우 다음과 같이 할 수 있습니다.
  1. http://support.bea.com/의 AskBEA에서"ORA-01000: maximum open cursors exceeded" 등으로 문제를조회하여 게시된 다른 해결 방법을 찾아봅니다. 계약 지원 고객: 제공되는 CR 관련 정보에 액세스할 수 있는 권한으로 로그온합니다
  2. http://forums.bea.com에 서 BEA 뉴스그룹에 보다 자세한 내용을 질문합니다.
이렇게 해도 문제를 해결할 수 없는 경우 유효한 유지 보수 계약이 되어 있다면http://support.bea.com/에 로그인하여 지원요청할 수 있습니다.
반응형
저작자표시 비영리

'ORACLE > TroubleShooting' 카테고리의 다른 글

ORA-01031 오류 해결방법  (0) 2010.08.12
Oracle 패치셋 업그레이드 후 ORA-01092 발생할 경우  (0) 2010.05.26
WARNING: Subscription for node down event still pending  (0) 2009.06.09
DATABASE CRASH WITH SGA_TARGET 문서 ID: 747812.1  (0) 2009.03.03
RAC 환경에서 ORA-29740 문제 진단  (0) 2009.03.02
Posted by [PineTree]

티스토리툴바