またまた、いまさらOracle9i

Oracle9i　カーソル・エラーについて

■カーソル数不足によるエラー

【エラーの内容】

[エラー番号] ORA-01000
[エラーメッセージ] 最大オープン・カーソル数を超えました。
[エラー原因] ホスト言語プログラムがオープンしようとしているカーソルの数が多すぎます。1ユーザー当たりのカーソルの最大数は、初期化パラメータOPEN_CURSORSによって決定されています。
[エラー処置] プログラムを変更して、使用するカーソルの数を減らしてください。繰り返しエラーが発生する場合は、Oracleを停止して、OPEN_CURSORSの値を大きくしてから、Oracleを再起動してください。

PL/SQLストアド・プロシージャで使用するカーソルは、検索結果のみをクライアント側で取得できるので、非常に効率的です。
但し、プログラミング時にカーソルの使い方を正しく意識していないと、「ORA-01000」エラーとなるケースがあります。

OPEN_CURSORSの値を増やせば、確かに上記のエラーが発生する確率を減らすことはできます。しかしながら、逆にリソースを多く消費するようになり、Oracleのパフォーマンスが全体的に低下させてしまいます。

カーソルというもの自体リソースを多く消費するものなので、大量に使用するというのはアプリケーションの仕組み的にやはり好ましくありません。
上記のエラーを回避するにはOPEN_CURSORSの値を増やすよりも、むしろアプリケーションで必要以上にカーソルを使用するようなロジックになっていないかどうかをまず見直すことのほうが大事です。

ただ、現状長年動作しているプログラムですので、スレッドなどを使用しているマルチタスクのアプリケーションや、アプリケーション修正時の2次障害を考えると、アプリケーション側での対応は難しい状況と認識しています。ですので、初期化パラメータファイル（init.ora）の 「OPEN_CURSORS」の値を変更することになるかと思います。その際に変更値の指針が必要です。

初期化パラメータ「OPEN_ CURSORS」のデフォルト値はOracle8i以前では「50」、Oracle9iとOracle 10gでは「300」がデフォルトの値です。


＜以下確認用＞

SQL>SHOW PARAMETER OPEN_ CURSORS

リスト1　初期化パラメータ「OPEN_ CURSORS」を確認

変更に当たって、カーソルがどの程度開かれているかを確認

「V$OPEN_CURSOR」で確認できます。このビューは、各ユーザー・セッションが
現在すでにオープンして解析しているカーソルを示します。

SQL>DESC V$OPEN_CURSOR

列名 データ型 格納されているデータの内容
SADDR RAW(4 | 8) セッション・アドレス
SID NUMBER セッション識別子
USER_NAME VARCHAR2(30) セッションにログインしているユーザー
ADDRESS RAW(4 | 8) HASH_VALUE とともに使用され、セッションで実行されているSQL 文を一意に識別する
SQL_TEXT VARCHAR2(60) オープン・カーソルに解析されるSQL文の最初の60文字

V$OPEN_CURSOR動的パフォーマンスビュー
セッションごとに使用されるカーソルの数をユーザー名をキーにして検索します。
「OPEN_CURSORS」の値近くまで増大している場合、値を変更する必要があります。

SQL>
SELECT
  SID AS セッションID,
  USER_NAME AS ユーザ名,
  COUNT(SID) AS カーソル数
FROM V$OPEN_CURSOR
WHERE USER_NAME = '[ユーザ名]'
GROUP BY SID,USER_NAME;
    
SELECT s.sid,username,
 (SUM(DECODE(name,'opened cursors cumulative',value,0))) "OPENED CURSOR", --セッション開始以降のオープン・カーソル合計数
 (SUM(DECODE(name,'opened cursors current',value,0))) "CURRENT CURSOR" --現行オープン・カーソル数
FROM v$session s,v$sesstat se,v$statname sn
WHERE se.sid = s.sid
 AND se.statistic# = sn.statistic#
 AND name in ('opened cursors cumulative','opened cursors current')
 AND username is not null
 --and s.sid=142
GROUP BY s.sid,username
ORDER BY "OPENED CURSOR" desc,"CURRENT CURSOR" desc;

＜カーソルクローズについての参考＞

次のSQLを実行した場合，その時点で開いているカーソルはすべて閉じられます。

また，暗黙的ロールバックありのエラーが発生した場合にも，カーソルはすべて閉じられます。

・定義系SQL（クライアント環境定義PDCMMTBFDDLにYESを指定している場合）

・PURGE TABLE文

・COMMIT文

・DISCONNECT文

・ROLLBACK文

・PREPARE文（クライアント環境定義PDPRPCRCLSにYESを指定している場合）

・内部DISCONNECT（DISCONNECT文を実行しないでUAPを終了する）

ただし，ホールダブルカーソルは，COMMIT文を実行した場合は閉じられません。

PURGE TABLE文を実行し，ホールダブルカーソルで開いている表が検査保留状態に設定された場合，ホールダブルカーソルは閉じられます。

ALLOCATE CURSOR文 形式2で手続きが返却した結果集合の組に割り当てられたカーソルに対してCLOSE文を実行した場合，

現在参照している結果集合の次の結果集合が存在するときは，現在参照している結果集合は閉じられます。カーソルは次の結果集合を参照し，

次のリターンコードが設定されます。

SQL連絡領域のSQLCODE領域に121

SQLCODE変数に121

SQLSTATE変数に'0100D'

また，このときカーソルは開いた状態となります。

一方，次の結果集合が存在しない場合は，現在参照している結果集合は閉じられ，次のリターンコードが設定されます。

SQL連絡領域のSQLCODE領域に100

SQLCODE変数に100

SQLSTATE変数に'02001'

また，このとき拡張カーソル名はどのカーソルも識別しなくなります。