[java] java.sql.SQLException :-ORA-01000 : 최대 열린 커서를 초과했습니다.

최대 개방 커서 오류 인 ORA-01000은 Oracle 데이터베이스 개발에서 매우 일반적인 오류입니다. Java 컨텍스트에서 애플리케이션이 데이터베이스 인스턴스에 구성된 커서보다 더 많은 ResultSet을 열려고 할 때 발생합니다.

일반적인 원인은 다음과 같습니다.

1. 구성 실수

   • 데이터베이스의 커서보다 데이터베이스를 쿼리하는 응용 프로그램의 스레드가 더 많습니다. 한 가지 경우는 데이터베이스의 커서 수보다 큰 연결 및 스레드 풀이있는 경우입니다.
   • 동일한 DB 인스턴스 (아마도 많은 스키마를 포함 할 것임)에 많은 개발자 또는 애플리케이션이 연결되어 있고 함께 너무 많은 연결을 사용하고 있습니다.

   • 해결책:

     • 데이터베이스 에서 커서 수 늘리기 (자원이 허용하는 경우) 또는
     • 응용 프로그램의 스레드 수를 줄입니다.

2. 커서 누수

   • 애플리케이션이 ResultSet (JDBC) 또는 커서 (데이터베이스의 스토어드 프로 시저에서)를 닫지 않습니다.
   • 솔루션 : 커서 누수는 버그입니다. DB에서 커서 수를 늘리면 불가피한 실패가 지연됩니다. 누수는 정적 코드 분석 , JDBC 또는 애플리케이션 수준 로깅, 데이터베이스 모니터링을 사용하여 찾을 수 있습니다 .

배경

이 섹션에서는 커서 뒤에있는 몇 가지 이론과 JDBC를 사용하는 방법에 대해 설명합니다. 배경을 알 필요가 없으면이 단계를 건너 뛰고 바로 ‘Eliminating Leaks’로 이동할 수 있습니다.

커서 란?

커서는 쿼리의 상태, 특히 판독기가 ResultSet에있는 위치를 보유하는 데이터베이스의 리소스입니다. 각 SELECT 문에는 커서가 있으며 PL / SQL 저장 프로시 저는 필요한만큼 많은 커서를 열고 사용할 수 있습니다. Orafaq에서 커서에 대해 자세히 알아볼 수 있습니다 .

데이터베이스 인스턴스는 일반적으로 여러 가지 역할을 스키마 , 많은 다른 사용자 와 각각의 여러 세션을 . 이를 위해 모든 스키마, 사용자 및 세션에 사용할 수있는 고정 된 수의 커서가 있습니다. 모든 커서가 열려 (사용 중) 새 커서가 필요한 요청이 들어 오면 ORA-010000 오류와 함께 요청이 실패합니다.

커서 수 찾기 및 설정

번호는 일반적으로 설치시 DBA가 구성합니다. 현재 사용중인 커서 수, 최대 수 및 구성은 Oracle SQL Developer 의 관리자 기능에서 액세스 할 수 있습니다 . SQL에서 다음과 같이 설정할 수 있습니다.

ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;

JVM의 JDBC를 DB의 커서에 연결

아래의 JDBC 개체는 다음 데이터베이스 개념과 밀접하게 연결되어 있습니다.

• JDBC 연결 은 데이터베이스 세션 의 클라이언트 표현이며 데이터베이스 트랜잭션을 제공합니다 . 연결은 한 번에 하나의 트랜잭션 만 열 수 있지만 트랜잭션은 중첩 될 수 있습니다.
• JDBC ResultSet 은 데이터베이스 의 단일 커서 에서 지원됩니다 . ResultSet에서 close ()가 호출되면 커서가 해제됩니다.
• JDBC CallableStatement 는 종종 PL / SQL로 작성된 데이터베이스에서 저장 프로 시저 를 호출합니다 . 스토어드 프로시 저는 0 개 이상의 커서를 작성할 수 있으며 커서를 JDBC ResultSet으로 리턴 할 수 있습니다.

JDBC는 스레드로부터 안전합니다. 스레드간에 다양한 JDBC 개체를 전달하는 것은 괜찮습니다.

예를 들어, 하나의 스레드에서 연결을 작성할 수 있습니다. 다른 스레드는이 연결을 사용하여 PreparedStatement를 만들 수 있고 세 번째 스레드는 결과 집합을 처리 할 수 ​​있습니다. 하나의 주요 제한 사항은 하나의 PreparedStatement에서 한 번에 둘 이상의 ResultSet을 열 수 없다는 것입니다. Oracle DB는 연결 당 여러 (병렬) 작업을 지원합니까?를 참조하십시오 .

데이터베이스 커밋은 Connection에서 발생하므로 해당 연결의 모든 DML (INSERT, UPDATE 및 DELETE)은 함께 커밋됩니다. 따라서 동시에 여러 트랜잭션을 지원하려면 각 동시 트랜잭션에 대해 하나 이상의 연결이 있어야합니다.

JDBC 객체 닫기

ResultSet 실행의 일반적인 예는 다음과 같습니다.

Statement stmt = conn.createStatement();
try {
    ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" );
    try {
        while ( rs.next() ) {
            System.out.println( "Name: " + rs.getString("FULL_NAME") );
        }
    } finally {
        try { rs.close(); } catch (Exception ignore) { }
    }
} finally {
    try { stmt.close(); } catch (Exception ignore) { }
}

finally 절이 close ()에 의해 발생한 예외를 무시하는 방법에 유의하십시오.

• try {} catch {}없이 단순히 ResultSet을 닫으면 실패하고 Statement가 닫히지 않을 수 있습니다.
• 호출자에게 전파하려는 시도의 본문에서 발생한 모든 예외를 허용하려고합니다. 예를 들어 문을 만들고 실행하는 것과 같이 루프가있는 경우 루프 내에서 각 문을 닫아야합니다.

Java 7에서 Oracle은 대부분의 Java 6 상용구를 멋진 구문 설탕으로 대체 하는 AutoCloseable 인터페이스 를 도입했습니다 .

JDBC 객체 보유

JDBC 객체는 로컬 변수, 객체 인스턴스 및 클래스 멤버에 안전하게 보관할 수 있습니다. 일반적으로 다음을 수행하는 것이 더 좋습니다.

• 객체 인스턴스 또는 클래스 멤버를 사용하여 Connections 및 PreparedStatements와 같이 장기간에 걸쳐 여러 번 재사용되는 JDBC 객체를 보유합니다.
• ResultSets에 로컬 변수를 사용하십시오. 이는 일반적으로 단일 함수의 범위 내에서 확보되고 반복 된 다음 닫힙니다.

그러나 한 가지 예외가 있습니다. EJB 또는 Servlet / JSP 컨테이너를 사용하는 경우 엄격한 스레딩 모델을 따라야합니다.

• Application Server 만 스레드를 작성합니다 (수신 요청을 처리하는 데 사용)
• 응용 프로그램 서버 만 연결을 만듭니다 (연결 풀에서 가져옴).
• 통화 사이에 값 (상태)을 저장할 때 매우주의해야합니다. 자신의 캐시 나 정적 멤버에 값을 저장하지 마십시오. 이것은 클러스터 및 기타 이상한 조건에서 안전하지 않으며 Application Server가 데이터에 끔찍한 일을 할 수 있습니다. 대신 Stateful Bean 또는 데이터베이스를 사용하십시오.
• 특히, 서로 다른 원격 호출에 대해 JDBC 객체 (연결, ResultSets, PreparedStatements 등)를 보유 하지 마십시오 . Application Server가이를 관리하도록하십시오. Application Server는 연결 풀을 제공 할뿐만 아니라 PreparedStatements도 캐시합니다.

누출 제거

JDBC 누출을 감지하고 제거하는 데 도움이되는 여러 프로세스와 도구가 있습니다.

1. 개발 중-버그를 조기에 발견하는 것이 가장 좋은 방법입니다.

   1. 개발 관행 : 좋은 개발 관행은 소프트웨어가 개발자의 책상을 떠나기 전에 소프트웨어의 버그 수를 줄여야합니다. 구체적인 사례는 다음과 같습니다.

      1. 충분한 경험이없는 사람들을 교육하기위한 페어 프로그래밍
      2. 많은 눈이 하나보다 낫기 때문에 코드 검토
      3. 단위 테스트 는 누수 재현을 사소하게 만드는 테스트 도구에서 모든 코드베이스를 실행할 수 있음을 의미합니다.
      4. 자신 만의 라이브러리 를 구축하는 대신 연결 풀링에 기존 라이브러리 사용

   2. 정적 코드 분석 : 뛰어난 Findbugs 와 같은 도구 를 사용하여 정적 코드 분석을 수행합니다. 이것은 close ()가 올바르게 처리되지 않은 많은 곳을 선택합니다. Findbugs에는 Eclipse 용 플러그인이 있지만 일회성으로 독립형으로 실행되며 Jenkins CI 및 기타 빌드 도구에 통합됩니다.

2. 런타임시 :

   1. 유지성 및 커밋

      1. ResultSet 보유 가능성이 ResultSet.CLOSE_CURSORS_OVER_COMMIT이면 Connection.commit () 메소드가 호출 될 때 ResultSet이 닫힙니다. Connection.setHoldability ()를 사용하거나 오버로드 된 Connection.createStatement () 메서드를 사용하여 설정할 수 있습니다.

   2. 런타임에 로깅.

      1. 코드에 좋은 로그 문을 넣으십시오. 고객, 지원 직원 및 팀원이 교육없이 이해할 수 있도록 명확하고 이해할 수 있어야합니다. 그것들은 간결해야하며 처리 로직을 추적 할 수 있도록 키 변수 및 속성의 상태 / 내부 값 인쇄를 포함해야합니다. 좋은 로깅은 특히 배포 된 애플리케이션을 디버깅하기위한 기본입니다.

      2. 프로젝트에 디버깅 JDBC 드라이버를 추가 할 수 있습니다 (디버깅을 위해-실제로 배포하지 마십시오). 한 가지 예 (사용하지 않았 음)는 log4jdbc 입니다. 그런 다음이 파일에 대해 간단한 분석을 수행하여 해당 종료가없는 실행을 확인해야합니다. 잠재적 인 문제가있는 경우 시가 및 시가를 세어 강조해야합니다.

         3. 데이터베이스 모니터링. SQL Developer ‘Monitor SQL’기능 또는 Quest의 TOAD 와 같은 도구를 사용하여 실행중인 애플리케이션을 모니터링합니다 . 이 문서에서는 모니터링에 대해 설명 합니다. 모니터링 중에 열린 커서 (예 : v $ sesstat 테이블에서)를 쿼리하고 해당 SQL을 검토합니다. 커서 수가 증가하고 (가장 중요한 것은) 하나의 동일한 SQL 문에 의해 지배되는 경우 해당 SQL에 누수가 있음을 알 수 있습니다. 코드를 검색하고 검토하십시오.

다른 생각들

WeakReferences를 사용하여 연결 닫기를 처리 할 수 ​​있습니까?

약한 참조와 소프트 참조는 JVM이 적합하다고 판단 할 때 언제든지 참조 대상을 가비지 수집 할 수있는 방식으로 개체를 참조 할 수 있도록하는 방법입니다 (해당 개체에 대한 강력한 참조 체인이 없다고 가정).

생성자의 ReferenceQueue를 소프트 또는 약한 참조로 전달하면 객체가 발생했을 때 GC 될 때 객체가 ReferenceQueue에 배치됩니다 (아마도 발생하는 경우). 이 접근 방식을 사용하면 개체의 마무리와 상호 작용할 수 있으며 그 순간 개체를 닫거나 마무리 할 수 ​​있습니다.

팬텀 참조는 좀 더 이상합니다. 그들의 목적은 종료를 제어하는 ​​것 뿐이지 만 원래 객체에 대한 참조를 얻을 수 없으므로 close () 메서드를 호출하기가 어려울 것입니다.

그러나 GC가 실행되는시기를 제어하는 ​​것은 좋은 생각이 아닙니다 (Weak, Soft 및 PhantomReferences 는 객체가 GC를 위해 대기열에 추가 되었다는 사실 을 알려줍니다 ). 실제로 JVM의 메모리 양이 큰 경우 (예 : -Xmx2000m) 객체를 GC 하지 않을 수 있으며 ORA-01000이 계속 발생합니다. JVM 메모리가 프로그램의 요구 사항에 비해 작 으면 ResultSet 및 PreparedStatement 객체가 생성 직후 (읽기 전에) GC 처리되어 프로그램이 실패 할 수 있습니다.

요약 : 약한 참조 메커니즘은 Statement 및 ResultSet 개체를 관리하고 닫는 좋은 방법이 아닙니다.

답변

  [oracle@db01 ~]$ sqlplus / as sysdba

 alter session set session_cached_cursors=0
 select * from V$PARAMETER where name='session_cached_cursors'

 SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# AND b.name = 'opened cursors current' AND p.name= 'open_cursors'  GROUP BY p.value;

 SELECT a.value, s.username, s.sid, s.serial#
 FROM v$sesstat a, v$statname b, v$session s
 WHERE a.statistic# = b.statistic#  AND s.sid=a.sid 
 AND b.name = 'opened cursors current' AND username = 'SCHEMA_NAME_IN_CAPS'

 SELECT oc.sql_text, s.sid 
 FROM v$open_cursor oc, v$session s
 WHERE OC.sid = S.sid
 AND s.sid=1604
 AND OC.USER_NAME ='SCHEMA_NAME_IN_CAPS'

try
{ //method try starts  
  String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
  pStmt = obj.getConnection().prepareStatement(sql);
  pStmt.setLong(1, subscriberID);
  for (String language : additionalLangs) {
    pStmt.setInt(2, Integer.parseInt(language));
    pStmt.execute();
  }
} //method/try ends
finally
{ //finally starts
   pStmt.close()
} 

    class obj{ 

    public Connection getConnection(){
    return new ConnectionDelegator(...here create your connection object and put it into ...);

    } 
}


class ConnectionDelegator implements Connection{
    Connection delegates;

    public ConnectionDelegator(Connection con){
       this.delegates = con;
    }

    public Statement prepareStatement(String sql){
        return delegates.prepareStatement(sql);
    }

    public void close(){
        try{
           delegates.close();
        }finally{
           log.debug(delegates.toString() + " was closed");
        }
    }
}

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

{ //method try starts  
    String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
    Connection conn = obj.getConnection()
    pStmt = conn.prepareStatement(sql);

    for (String language : additionalLangs) {
        pStmt.setLong(1, subscriberID);
        pStmt.setInt(2, Integer.parseInt(language));
        pStmt.execute();
        conn.commit();
    }
} //method/try ends { 
    //finally starts
    pStmt.close()
} //finally ends