Вопрос-ответ

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

java.sql.SQLException: - ORA-01000: превышено максимальное количество открытых курсоров

Я получаю исключение ORA-01000 SQL. Поэтому у меня есть несколько запросов, связанных с этим.


  1. Максимальное количество открытых курсоров точно связано с количеством подключений JDBC, или они также связаны с объектами statement и resultset, которые мы создали для одного подключения? (Мы используем пул подключений)

  2. Есть ли способ настроить количество объектов statement / resultset в базе данных (например, connections)?

  3. Целесообразно ли использовать оператор переменной экземпляра / объект результирующего набора вместо локального оператора метода / объекта результирующего набора в однопоточной среде?

  4. Вызывает ли эту проблему выполнение подготовленного оператора в цикле? (Конечно, я мог бы использовать sqlBatch) Примечание: pStmt закрывается после завершения цикла.


    { //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 starts
       pStmt.close()
    } //finally ends

  5. Что произойдет, если conn.createStatement() и conn.prepareStatement(sql) будут вызваны несколько раз для одного объекта подключения?


Правка1: 6. Поможет ли использование объекта Weak / Soft reference statement предотвратить утечку?

Правка 2: 1. Могу ли я каким-либо образом найти все отсутствующие "statement.close ()" в моем проекте? Я понимаю, что это не утечка памяти. Но мне нужно найти ссылку на инструкцию (где close () не выполняется), подходящую для сборки мусора? Какой-либо доступный инструмент? Или я должен проанализировать это вручную?

Пожалуйста, помогите мне разобраться в этом.

Решение

Чтобы найти открытый курсор в Oracle DB по имени пользователя -VELU

Перейдите на компьютер ORACLE и запустите sqlplus как sysdba.

[oracle@db01 ~]$ sqlplus / as sysdba

Затем запустите

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     = 'VELU';

Если возможно, пожалуйста, прочтите мой ответ, чтобы лучше понять мое решение

Переведено автоматически
Ответ 1

ORA-01000, ошибка максимального количества открытых курсоров, является чрезвычайно распространенной ошибкой при разработке баз данных Oracle. В контексте Java это происходит, когда приложение пытается открыть больше наборов результатов, чем настроенных курсоров в экземпляре базы данных.

Распространенными причинами являются:


  1. Ошибка конфигурации



    • В вашем приложении больше потоков, запрашивающих базу данных, чем курсоров в базе данных. В одном случае пул подключений и потоков превышает количество курсоров в базе данных.

    • У вас много разработчиков или приложений, подключенных к одному экземпляру базы данных (который, вероятно, будет включать множество схем), и вместе вы используете слишком много подключений.

    • Решение:




  2. Cursor leak



    • The applications is not closing ResultSets (in JDBC) or cursors (in stored procedures on the database)

    • Solution: Cursor leaks are bugs; increasing the number of cursors on the DB simply delays the inevitable failure. Leaks can be found using static code analysis, JDBC or application-level logging, and database monitoring.


Background

This section describes some of the theory behind cursors and how JDBC should be used. If you don't need to know the background, you can skip this and go straight to 'Eliminating Leaks'.

What is a cursor?

A cursor is a resource on the database that holds the state of a query, specifically the position where a reader is in a ResultSet. Each SELECT statement has a cursor, and PL/SQL stored procedures can open and use as many cursors as they require. You can find out more about cursors on Orafaq.

A database instance typically serves several different schemas, many different users each with multiple sessions. To do this, it has a fixed number of cursors available for all schemas, users and sessions. When all cursors are open (in use) and request comes in that requires a new cursor, the request fails with an ORA-010000 error.

Finding and setting the number of cursors

The number is normally configured by the DBA on installation. The number of cursors currently in use, the maximum number and the configuration can be accessed in the Administrator functions in Oracle SQL Developer. From SQL it can be set with:

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

Relating JDBC in the JVM to cursors on the DB

The JDBC objects below are tightly coupled to the following database concepts:


  • JDBC Connection is the client representation of a database session and provides database transactions. A connection can have only a single transaction open at any one time (but transactions can be nested)

  • A JDBC ResultSet is supported by a single cursor on the database. When close() is called on the ResultSet, the cursor is released.

  • A JDBC CallableStatement invokes a stored procedure on the database, often written in PL/SQL. The stored procedure can create zero or more cursors, and can return a cursor as a JDBC ResultSet.

JDBC is thread safe: It is quite OK to pass the various JDBC objects between threads.

For example, you can create the connection in one thread; another thread can use this connection to create a PreparedStatement and a third thread can process the result set. The single major restriction is that you cannot have more than one ResultSet open on a single PreparedStatement at any time. See Does Oracle DB support multiple (parallel) operations per connection?

Note that a database commit occurs on a Connection, and so all DML (INSERT, UPDATE and DELETE's) on that connection will commit together. Therefore, if you want to support multiple transactions at the same time, you must have at least one Connection for each concurrent Transaction.

Closing JDBC objects

A typical example of executing a ResultSet is:

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) { }
}

Note how the finally clause ignores any exception raised by the close():


  • If you simply close the ResultSet without the try {} catch {}, it might fail and prevent the Statement being closed

  • We want to allow any exception raised in the body of the try to propagate to the caller.
    If you have a loop over, for example, creating and executing Statements, remember to close each Statement within the loop.

In Java 7, Oracle has introduced the AutoCloseable interface which replaces most of the Java 6 boilerplate with some nice syntactic sugar.

Holding JDBC objects

JDBC objects can be safely held in local variables, object instance and class members. It is generally better practice to:


  • Use object instance or class members to hold JDBC objects that are reused multiple times over a longer period, such as Connections and PreparedStatements

  • Use local variables for ResultSets since these are obtained, looped over and then closed typically within the scope of a single function.

There is, however, one exception: If you are using EJBs, or a Servlet/JSP container, you have to follow a strict threading model:


  • Only the Application Server creates threads (with which it handles incoming requests)

  • Only the Application Server creates connections (which you obtain from the connection pool)

  • When saving values (state) between calls, you have to be very careful. Never store values in your own caches or static members - this is not safe across clusters and other weird conditions, and the Application Server may do terrible things to your data. Instead use stateful beans or a database.

  • In particular, never hold JDBC objects (Connections, ResultSets, PreparedStatements, etc) over different remote invocations - let the Application Server manage this. The Application Server not only provides a connection pool, it also caches your PreparedStatements.

Eliminating leaks

There are a number of processes and tools available for helping detect and eliminating JDBC leaks:


  1. During development - catching bugs early is by far the best approach:



    1. Development practices: Good development practices should reduce the number of bugs in your software before it leaves the developer's desk. Specific practices include:



      1. Pair programming, to educate those without sufficient experience

      2. Code reviews because many eyes are better than one

      3. Unit testing which means you can exercise any and all of your code base from a test tool which makes reproducing leaks trivial

      4. Use existing libraries for connection pooling rather than building your own


    2. Static Code Analysis: Use a tool like the excellent Findbugs to perform a static code analysis. This picks up many places where the close() has not been correctly handled. Findbugs has a plugin for Eclipse, but it also runs standalone for one-offs, has integrations into Jenkins CI and other build tools



  2. At runtime:



    1. Holdability and commit



      1. If the ResultSet holdability is ResultSet.CLOSE_CURSORS_OVER_COMMIT, then the ResultSet is closed when the Connection.commit() method is called. This can be set using Connection.setHoldability() or by using the overloaded Connection.createStatement() method.


    2. Logging at runtime.



      1. Put good log statements in your code. These should be clear and understandable so the customer, support staff and teammates can understand without training. They should be terse and include printing the state/internal values of key variables and attributes so that you can trace processing logic. Good logging is fundamental to debugging applications, especially those that have been deployed.

      2. You can add a debugging JDBC driver to your project (for debugging - don't actually deploy it). One example (I have not used it) is log4jdbc. You then need to do some simple analysis on this file to see which executes don't have a corresponding close. Counting the open and closes should highlight if there is a potential problem



        1. Monitoring the database. Monitor your running application using the tools such as the SQL Developer 'Monitor SQL' function or Quest's TOAD. Monitoring is described in this article. During monitoring, you query the open cursors (eg from table v$sesstat) and review their SQL. If the number of cursors is increasing, and (most importantly) becoming dominated by one identical SQL statement, you know you have a leak with that SQL. Search your code and review.




Other thoughts

Can you use WeakReferences to handle closing connections?

Weak and soft references are ways of allowing you to reference an object in a way that allows the JVM to garbage collect the referent at any time it deems fit (assuming there are no strong reference chains to that object).

If you pass a ReferenceQueue in the constructor to the soft or weak Reference, the object is placed in the ReferenceQueue when the object is GC'ed when it occurs (if it occurs at all). With this approach, you can interact with the object's finalization and you could close or finalize the object at that moment.

Phantom references are a bit weirder; their purpose is only to control finalization, but you can never get a reference to the original object, so it's going to be hard to call the close() method on it.

However, it is rarely a good idea to attempt to control when the GC is run (Weak, Soft and PhantomReferences let you know after the fact that the object is enqueued for GC). In fact, if the amount of memory in the JVM is large (eg -Xmx2000m) you might never GC the object, and you will still experience the ORA-01000. If the JVM memory is small relative to your program's requirements, you may find that the ResultSet and PreparedStatement objects are GCed immediately after creation (before you can read from them), which will likely fail your program.

TL;DR: The weak reference mechanism is not a good way to manage and close Statement and ResultSet objects.

Ответ 2

Я добавляю еще немного понимания.


  1. Курсор относится только к объекту инструкции objectct; Он не является ни результирующим набором, ни объектом подключения.

  2. Но все равно мы должны закрыть результирующий набор, чтобы освободить часть памяти oracle. Тем не менее, если вы не закроете результирующий набор, это не будет учитываться для курсоров.

  3. Объект Closing Statement также автоматически закроет объект resultset.

  4. Курсор будет создан для всех инструкций SELECT / INSERT / UPDATE / DELETE .

  5. Каждый экземпляр ORACLE DB может быть идентифицирован с помощью oracle SID; аналогично ORACLE DB может идентифицировать каждое соединение с помощью connection SID. Оба SID различны.

  6. Таким образом, сеанс ORACLE - это не что иное, как соединение jdbc (tcp); которое представляет собой не что иное, как один SID.

  7. Если мы установим максимальное количество курсоров равным 500, то это только для одного сеанса JDBC / подключения / SID.

  8. Таким образом, у нас может быть много подключений JDBC с соответствующим количеством курсоров (операторов).

  9. После завершения работы JVM все соединения / курсоры будут закрыты, ИЛИ JDBCConnection будет закрыто, КУРСОРЫ, относящиеся к этому соединению, будут закрыты.


Вход в систему как sysdba.

В Putty (вход в Oracle):

  [oracle@db01 ~]$ sqlplus / as sysdba

В SqlPlus:

Имя пользователя: sys as sysdba

Установите значение session_cached_cursors равным 0, чтобы в нем не было закрытых курсоров.

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

Выберите значение существующих OPEN_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;

Ниже приведен запрос для поиска списка SID / подключений со значениями открытых курсоров.

 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'

Используйте приведенный ниже запрос для определения sql в открытых курсорах

 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'

Теперь отладьте код и наслаждайтесь !!! :)

Ответ 3

Исправьте свой код следующим образом:

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()
}

Вы уверены, что действительно закрываете свои pStatements, соединения и результаты?

Для анализа открытых объектов вы можете внедрить шаблон делегатора, который оборачивает код вокруг ваших объектов statemant, connection и result . Таким образом, вы увидите, будет ли объект успешно закрыт.

Пример для: pStmt = obj.getConnection().prepareStatement(sql);

    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");
        }
    }
}
Ответ 4

Если ваше приложение является приложением Java EE, работающим на Oracle WebLogic в качестве сервера приложений, возможной причиной этой проблемы является параметр Размер кэша инструкций в WebLogic.

Если параметр размера кэша инструкций для определенного источника данных примерно равен или превышает значение максимального количества открытых курсоров базы данных Oracle Database, то все открытые курсоры могут использоваться кэшированными инструкциями SQL, которые WebLogic поддерживает открытыми, что приводит к ошибке ORA-01000.

Чтобы решить эту проблему, уменьшите параметр размера кэша инструкций для каждого источника данных WebLogic, который указывает на базу данных Oracle, чтобы он был значительно меньше параметра максимального количества курсоров в базе данных.

В консоли администратора WebLogic 10 параметр размера кэша инструкций для каждого источника данных можно найти на сервисах (левая навигация)> Источники данных> (отдельный источник данных) > Вкладка Пул подключений.

2023-05-15 08:15 java jdbc