[解決済み] java.sql.SQLException: - ORA-01000: 開いているカーソルの最大数を超えました。

2022-03-09 23:12:02

質問

ORA-01000 SQL例外が発生します。そこで、それに関連するいくつかのクエリを持っています。

最大オープン・カーソルは、JDBC 接続の数と正確に関連していますか、それとも 1 つの接続に対して作成したステートメントと結果セット・オブジェクトにも関連していますか？(私たちは接続のプールを使用しています)
データベース内のステートメント/結果オブジェクトの数を設定する方法はありますか（接続のように）？
シングルスレッド環境で、メソッドローカルのステートメント/結果セットオブジェクトの代わりに、インスタンス変数のステートメント/結果セットオブジェクトを使用することは推奨されますか？

ループ内でプリペアドステートメントを実行すると、この問題が発生しますか？(もちろん、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

1つの接続オブジェクトに対して、conn.createStatement() と conn.prepareStatement(sql) を複数回呼び出した場合、どうなりますか？

Edit1: 6. Weak/Soft参照文オブジェクトを使用することで、漏洩を防ぐことができるのでしょうか？

Edit2: 1. 私のプロジェクトに欠けている "statement.close()"s をすべて見つける方法はありますか？私はそれがメモリリークでないことを理解しています。しかし、私はガベージコレクションの対象となるステートメント参照（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';

できれば私の解決策をより深く理解するために、私の回答

どのように解決するのですか？

ORA-01000（maximum-open-cursorsエラー）は、Oracleデータベースの開発で非常によく発生するエラーです。Java の文脈では、アプリケーションがデータベースインスタンス上で設定されたカーソル数よりも多くの結果セットを開こうとしたときに発生します。

一般的な原因としては

設定ミス
- アプリケーションでデータベースへの問い合わせを行うスレッドが、DB上のカーソル数よりも多い場合。1つのケースは、データベース上のカーソルの数よりも大きな接続とスレッドプールを持つ場合です。
- 多くの開発者やアプリケーションが同じDBインスタンス（おそらく多くのスキーマを含む）に接続しており、一緒になって多くの接続を使用している場合。
- 解決策
  - カーソル数の増加をデータベース上で実行するか (リソースが許すなら)
  - アプリケーションのスレッド数を減少させる。
カーソル漏れ
- アプリケーションがResultSets（JDBCの場合）またはカーソル（データベースのストアドプロシージャの場合）を閉じていない。
- 解決方法 : カーソルリークはバグです。DB上のカーソル数を増やすと、必然的に障害が発生するのを遅らせるだけです。リークを見つけるには静的コード解析 , JDBC またはアプリケーションレベルのロギング、およびデータベース監視 .

背景

このセクションでは、カーソルの背後にある理論やJDBCをどのように使用すべきかを説明します。もし、背景を知る必要がなければ、これをスキップして、「漏れをなくす」に直接進むことができます。

カーソルとは何ですか？

カーソルは、クエリの状態を保持するデータベース上のリソースで、具体的には、ResultSet内のリーダーの位置を保持します。各SELECT文はカーソルを持ち、PL/SQLストアドプロシージャは必要なだけのカーソルを開いて使用することができます。カーソルについてより詳しく知るにはオラファック .

データベースインスタンスは通常、複数の異なる スキーマ また、多くの異なる ユーザー それぞれを 複数セッション . このため、すべてのスキーマ、ユーザー、セッションで利用可能なカーソルの数は固定されています。すべてのカーソルがオープン（使用中）であり、新しいカーソルを必要とするリクエストが来た場合、リクエストはORA-010000エラーで失敗します。

カーソル数の検索と設定

この数は通常、インストール時にDBAによって設定されます。現在使用されているカーソルの数、最大数、および構成は、管理者機能でオラクル SQL Developer . SQLから設定することができます。

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

JVMのJDBCとDB上のカーソルの関連付け

以下のJDBCオブジェクトは、以下のデータベース概念と緊密に結合しています。

JDBC 接続方法 は、データベースのクライアント表現 セッション を提供し、データベース トランザクション . 接続は一度に1つのトランザクションしか開くことができません（ただし、トランザクションは入れ子にすることができます）。
JDBC 結果セット がサポートするのは、1つの カーソル をデータベース上で実行します。ResultSet上でclose()が呼び出されると、カーソルは解放されます。
JDBC callableStatement を呼び出します。 ストアドプロシージャ をデータベース上で実行します。多くの場合、PL/SQL で記述されます。ストアドプロシージャは、0個以上のカーソルを作成することができ、カーソルをJDBC ResultSetとして返すことができます。

JDBCはスレッドセーフです。スレッド間で様々なJDBCオブジェクトを渡すことは全く問題ありません。

例えば、あるスレッドで接続を作成し、別のスレッドでこの接続を使用してPreparedStatementを作成し、3番目のスレッドで結果セットを処理することが可能です。唯一の大きな制限は、1つのPreparedStatementで複数のResultSetを常に開くことができないということです。参照 Oracle DBは1つの接続で複数（並列）操作をサポートしていますか？

データベースのコミットはコネクション上で行われるため、そのコネクション上のすべてのDML（INSERT、UPDATE、DELETE）が一緒にコミットされることに注意してください。したがって、複数のトランザクションを同時にサポートしたい場合は、同時実行するトランザクションごとに少なくとも1つの接続を持つ必要があります。

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 が閉じられなくなる可能性があります。
tryのボディで発生した例外は、呼び出し側に伝搬するようにしたい。例えばStatementの作成と実行を繰り返すループがある場合、ループ内の各Statementを閉じることを忘れないでください。

Java 7 では、Oracle は AutoCloseable インターフェースこれは、Java 6 の定型文のほとんどを、すばらしい構文上の糖分で置き換えたものです。

JDBCオブジェクトの保持

JDBCオブジェクトは、ローカル変数、オブジェクトインスタンス、クラスメンバに安全に保持することができます。一般的には、次のようにするのがよいでしょう。

Connections や PreparedStatements など、長期間にわたって何度も再利用される JDBC オブジェクトを保持するには、オブジェクトのインスタンスまたはクラス・メンバーを使用します。
ResultSetsは、通常1つの関数の範囲内で取得、ループ処理、クローズされるため、ローカル変数を使用する。

しかし、1つだけ例外があります。EJBやServlet/JSPコンテナを使用している場合は、厳密なスレッドモデルに従わなければなりません。

アプリケーションサーバーだけがスレッドを作成する（このスレッドで入ってくるリクエストを処理する）。
アプリケーションサーバーだけが接続を作成する (接続プールから取得する)
呼び出しの間に値（状態）を保存する場合、非常に注意する必要があります。これはクラスタやその他の奇妙な条件下では安全ではありませんし、Application Serverがあなたのデータに対して恐ろしいことをするかもしれません。代わりにステートフルビーンかデータベースを使いましょう。
特に 決して JDBCオブジェクト(Connections, ResultSets, PreparedStatementsなど)を異なるリモート呼び出しに渡って保持する - アプリケーションサーバに管理を任せる。アプリケーションサーバーは接続プールを提供するだけでなく、PreparedStatementsのキャッシュも行います。

漏れをなくす

JDBCのリークを検出し、排除するのに役立つプロセスやツールが多数あります。

開発中 - バグを早期に発見することは、断然最善の方法です。
1. 開発プラクティス。良い開発プラクティスは、ソフトウェアが開発者の机を離れる前に、バグの数を減らす必要があります。具体的なプラクティスは以下の通りです。
  1. ペアプログラミング十分な経験を積んでいない人を教育するために
  2. コードレビュー一人より多人数の方が良いから
  3. ユニットテストつまり、テストツールからコードベースのあらゆる部分を実行することができ、リークの再現が簡単になります。
  4. 使用方法既存のライブラリコネクションプーリングは自分で作るより
2. 静的コード解析。優れたファインドバグズを使用して静的コード解析を行います。これにより、close()が正しく処理されていない箇所が多数検出されます。FindbugsはEclipse用のプラグインを持っているが、単発でスタンドアローンでも動くし、Jenkins CIや他のビルドツールにも統合されている。
ランタイム時に
1. ホールド性・コミット性
  1. ResultSet の holdability が ResultSet.CLOSE_CURSORS_OVER_COMMIT の場合、Connection.commit() メソッドが呼ばれると ResultSet がクローズされる。これは、Connection.setHoldability() を使用するか、オーバーロードされた Connection.createStatement() メソッドを使用することで設定可能です。
2. 実行時にログを記録する。
  1. コードに良いログ文を入れてください。これらは、顧客、サポートスタッフ、チームメイトがトレーニングなしで理解できるように、明確でわかりやすいものであるべきです。簡潔で、主要な変数や属性の状態や内部値を表示し、処理ロジックをトレースできるようにする必要があります。優れたログは、アプリケーションのデバッグ、特にデプロイされたアプリケーションのデバッグの基本です。
  2. デバッグ用のJDBCドライバをプロジェクトに追加することができます（デバッグ用 - 実際にはデプロイしないでください）。一例として（私は使ったことがありませんが）、以下のようなものがあります。 log4jdbc . そして、このファイルに対して簡単な分析を行い、どの実行が対応するクローズを持っていないのかを確認する必要があります。オープンとクローズをカウントすることで、潜在的な問題があるかどうかが明らかになります。
    1. データベースを監視する。SQL Developerの「Monitor SQL」機能などのツールを使って、実行中のアプリケーションを監視する。クエストのTOAD . モニタリングについてはこの記事 . 監視中に、開いているカーソルを照会し（例：v$sesstatテーブルから）、そのSQLを確認します。カーソルの数が増え、（最も重要なことですが）1つの同じSQL文に支配されつつある場合、そのSQLでリークしていることがわかります。コードを検索し、レビューしてください。

その他の感想

WeakReferences を使用して接続を閉じる処理を行うことはできますか？

弱参照とソフト参照は、JVMが適切と考えるときにいつでも参照先をガベージコレクションできるようにする方法です（そのオブジェクトへの強参照チェーンがないと仮定して）。

ソフトリファレンスや弱いリファレンスのコンストラクタでReferenceQueueを渡すと、そのオブジェクトがGCされるときに（発生した場合）ReferenceQueueに入れられる。この方法では、オブジェクトの最終化と対話することができ、その時点でオブジェクトを閉じたり最終化したりすることができます。

ファントムリファレンスは少し変です。その目的は最終化を制御することだけですが、元のオブジェクトへの参照を得ることはできないので、そのオブジェクトに対してclose()メソッドを呼び出すことは難しいでしょう。

しかし、GCがいつ実行されるかをコントロールしようとするのは、ほとんど良いアイデアではありません（Weak、Soft、PhantomReferencesは、それを教えてくれます）。事後オブジェクトがGCのためにキューに入れられたこと)。実際、JVMのメモリ量が大きい場合（例：-Xmx2000m）には、以下のようになるかもしれません。 決して オブジェクトをGCしても、ORA-01000が発生します。もしJVMのメモリがプログラムの要求に対して小さい場合、ResultSetとPreparedStatementオブジェクトが生成後すぐに（それらから読むことができる前に）GCされることがわかり、それはおそらくあなたのプログラムを失敗させることでしょう。

TL;DR。 弱い参照メカニズムは、StatementとResultSetオブジェクトを管理し、閉じるための良い方法とは言えません。