【PL/SQL】カーソルキャッシュとメモリ管理｜OPEN_CURSORS・ORA-01000対策

PL/SQLで大量トランザクション、バッチ、動的SQLを扱うときは、SQLそのものの実行時間だけでなく、パース回数、カーソル再利用、開きっぱなしカーソル、PGA使用量も性能と安定性に直結します。同じSQLを何度も実行しているのに毎回ハードパースしている、例外時にカーソルを閉じ忘れている、BULK COLLECTで全件をメモリに載せている、という状態は本番障害になりやすいです。

この記事では、SESSION_CACHED_CURSORS、OPEN_CURSORS、V$OPEN_CURSOR、V$SQLAREA、EXECUTE IMMEDIATE、DBMS_SQL、BULK COLLECT LIMIT を使いながら、PL/SQLのカーソルキャッシュとメモリ管理を実務向けに整理します。カーソルの基本は PL/SQLカーソル完全ガイド、明示カーソルとの使い分けは カーソルFORループ vs 明示的カーソル完全ガイド もあわせて確認してください。

最初に結論：カーソル管理で見るべきこと

カーソル管理の目的は、単に CLOSE を忘れないことだけではありません。同じSQLを同じ字面で実行し、値はバインド変数で渡し、必要以上にカーソルを開かず、必要な行数だけメモリへ取り込むことが重要です。

ハードパースとソフトパースの違い

SQL実行時、OracleはSQL文の解析、権限チェック、実行計画の生成または再利用を行います。実行計画を新しく作る処理が多いほどハードパースが増え、CPU、ライブラリキャッシュ、共有プールに負荷がかかります。一方、同じSQL文を再利用できればソフトパースで済み、負荷を抑えられます。

SQLの字面を安定させる

-- NG: 値を文字列連結すると、値ごとに別SQLになりやすい
v_sql := 'SELECT * FROM orders WHERE order_id = ' || p_order_id;

-- OK: SQLの骨格を固定し、値だけバインドする
v_sql := 'SELECT * FROM orders WHERE order_id = :order_id';
OPEN c FOR v_sql USING p_order_id;

バインド変数の考え方は Oracleバインド変数完全ガイド、PL/SQLの動的SQLでの実装は 動的SQLとBIND変数の最適設計 が関連します。

SESSION_CACHED_CURSORSの役割

SESSION_CACHED_CURSORS は、セッション内で繰り返し使うカーソルをキャッシュし、再パースの負荷を下げるための設定です。同一SQLが何度も実行されるPL/SQLバッチや接続プール環境では、適切な値にすることで session cursor cache hits が増えます。

現在値とヒット数を確認する

SELECT name, value
  FROM v$parameter
 WHERE name IN ('session_cached_cursors', 'open_cursors');

SELECT n.name,
       s.value
  FROM v$sesstat s
  JOIN v$statname n
    ON n.statistic# = s.statistic#
 WHERE s.sid = SYS_CONTEXT('USERENV', 'SID')
   AND n.name IN (
         'parse count (total)',
         'parse count (hard)',
         'session cursor cache hits',
         'opened cursors current'
       );

ヒット数が伸びない場合、設定値を上げる前にSQL生成を確認します。値を文字列連結してSQL文が毎回変わっている場合、キャッシュ設定を上げても効果は出にくいです。

OPEN_CURSORSは上限であって対策そのものではない

OPEN_CURSORS は、1セッションが同時に開けるカーソル数の上限です。ORA-01000: maximum open cursors exceeded が出たとき、単に値を上げるだけでは根本対策になりません。CLOSE漏れ、REF CURSORの返却後放置、動的SQLの多重オープン、接続プール側のステートメントキャッシュを確認します。

開いているカーソルを確認する

SELECT sid,
       sql_id,
       cursor_type,
       COUNT(*) AS open_count,
       MIN(sql_text) AS sample_sql
  FROM v$open_cursor
 WHERE sid = SYS_CONTEXT('USERENV', 'SID')
 GROUP BY sid, sql_id, cursor_type
 ORDER BY open_count DESC;

ORA-01000 の具体的な調査手順は ORA-01000最大オープン・カーソル数超過の完全ガイド を参照してください。類似エラーとして、閉じたカーソルを再利用した場合の ORA-01001 invalid cursorの直し方、すでに開いているカーソルを再OPENした場合の ORA-06511 cursor already openの直し方 も関連します。

診断フロー：何を見てどう直すか

カーソル周りの性能問題は、症状によって見る場所が変わります。次の順で確認すると、設定不足なのか、SQL生成の問題なのか、CLOSE漏れなのかを切り分けやすくなります。

暗黙カーソルを使える場面では任せる

SELECT INTO やDMLでは、Oracleが暗黙カーソルを管理します。複数行のSELECTを順に処理するだけなら、FOR r IN (SELECT ...) のカーソルFORループが簡潔で安全です。OPEN、FETCH、CLOSEをPL/SQL側で書かないため、CLOSE漏れの余地が減ります。

カーソルFORループの基本形

BEGIN
  FOR r IN (
    SELECT employee_id, department_id
      FROM employees
     WHERE status = 'ACTIVE'
     ORDER BY employee_id
  ) LOOP
    update_employee_summary(r.employee_id, r.department_id);
  END LOOP;
END;
/

単純な順次処理ならこの形で十分なことが多いです。カーソルFORループと明示カーソルの判断は カーソルFORループ vs 明示的カーソル完全ガイド も参考になります。

明示カーソルは例外時CLOSEまで書く

フェッチ単位を細かく制御したい、パラメータ付きカーソルを再利用したい、BULK COLLECT LIMITを使いたい場合は明示カーソルを使います。この場合は、正常系だけでなく例外経路でも確実に閉じる構造にします。

例外時も閉じるテンプレート

DECLARE
  CURSOR c_emp(p_dept_id NUMBER) IS
    SELECT employee_id, employee_name
      FROM employees
     WHERE department_id = p_dept_id;

  v_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp(10);
  BEGIN
    LOOP
      FETCH c_emp INTO v_emp;
      EXIT WHEN c_emp%NOTFOUND;

      process_employee(v_emp.employee_id);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      IF c_emp%ISOPEN THEN
        CLOSE c_emp;
      END IF;
      RAISE;
  END;

  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
END;
/

明示カーソルの基本構文や %FOUND、%NOTFOUND、%ROWCOUNT の扱いは Oracle明示的カーソル完全ガイド が詳しいです。

REF CURSORは所有者を明確にする

SYS_REFCURSOR は、結果セットを呼び出し元へ返すときに便利です。ただし、誰が開き、誰が閉じるのかを曖昧にすると、クライアント側や中間層でカーソルが残りやすくなります。返却するAPIでは、呼び出し元が必ずクローズする規約を明示します。

REF CURSORを返す例

CREATE OR REPLACE PROCEDURE search_orders(
  p_customer_id IN  NUMBER,
  p_result      OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_result FOR
    SELECT order_id, order_date, amount
      FROM orders
     WHERE customer_id = p_customer_id
     ORDER BY order_date DESC;
END;
/

REF CURSORの実務パターンは REF CURSORで柔軟なデータ取得を実装する方法 を参照してください。

動的SQLは骨格固定とバインドを徹底する

EXECUTE IMMEDIATE は便利ですが、値を文字列連結するとSQLインジェクションだけでなく、カーソル共有の阻害にもなります。文の骨格を固定し、値は USING で渡します。

EXECUTE IMMEDIATEの安全な形

DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'UPDATE orders
               SET status = :status,
                   updated_at = SYSTIMESTAMP
             WHERE order_id = :order_id';

  EXECUTE IMMEDIATE v_sql
    USING p_status, p_order_id;
END;
/

動的SQLの網羅的な使い方は Oracle EXECUTE IMMEDIATE完全ガイド、列数や型まで動的に変わる場合は Oracle DBMS_SQL完全ガイド が関連します。

V$SQLAREAで再利用状況を見る

同じようなSQLが値違いで大量に並んでいる場合、バインド不足の可能性があります。V$SQLAREA では、実行回数、ロード回数、パース回数、SQL本文を確認できます。

再利用されていないSQLを探す

SELECT sql_id,
       executions,
       parse_calls,
       loads,
       SUBSTR(sql_text, 1, 120) AS sample_sql
  FROM v$sqlarea
 WHERE parsing_schema_name = USER
 ORDER BY parse_calls DESC
 FETCH FIRST 30 ROWS ONLY;

executions に対して parse_calls が多すぎるSQLは、SQL生成とバインドの見直し候補です。実行計画の確認には Oracle DBMS_XPLAN完全ガイド も使います。

BULK COLLECT LIMITでPGAを守る

大量行を一度に BULK COLLECT すると、PGAを大きく消費します。件数が多い処理では、LIMIT を付けて小分けにフェッチし、処理後にコレクションを使い捨てます。

LIMIT付きフェッチの例

DECLARE
  CURSOR c_src IS
    SELECT order_id, amount
      FROM orders
     WHERE status = 'WAITING'
     ORDER BY order_id;

  TYPE t_src IS TABLE OF c_src%ROWTYPE;
  l_rows t_src;
BEGIN
  OPEN c_src;
  LOOP
    FETCH c_src BULK COLLECT INTO l_rows LIMIT 1000;
    EXIT WHEN l_rows.COUNT = 0;

    FORALL i IN 1 .. l_rows.COUNT
      UPDATE orders
         SET status = 'DONE'
       WHERE order_id = l_rows(i).order_id;
  END LOOP;
  CLOSE c_src;
END;
/

BULK COLLECTとFORALLの設計は Oracle BULK COLLECT / FORALL完全ガイド、PL/SQL側のバルク処理実装は PL/SQLバルク処理完全ガイド が関連します。PGA不足の切り分けは ORA-04030の原因と解決方法 も参考になります。

依存オブジェクトの変更でカーソルは無効化される

参照先の表、ビュー、パッケージ、シノニム、権限、統計情報、DDL変更の影響で、既存カーソルは無効化されることがあります。リリース直後にハードパースが増える、初回実行だけ遅い、INVALID化したPL/SQLが再コンパイルされる、といった現象は依存関係も疑います。

INVALID再コンパイルの制御は 依存オブジェクトとINVALID再コンパイルの制御、パッケージ設計は PL/SQLパッケージ設計 が関連します。

使うべき設計・避けたい設計

本番前チェックリスト

まとめ

PL/SQLのカーソルキャッシュとメモリ管理では、SQLを速くするだけでなく、パースを減らし、カーソルを漏らさず、PGAを膨らませないことが重要です。まずはSQLの字面を固定し、値はバインド変数で渡します。暗黙カーソルで十分な処理は任せ、明示カーソルやREF CURSORを使う場合は所有者とCLOSE責任を明確にします。

問題が起きたら、V$SESSTAT、V$OPEN_CURSOR、V$SQLAREA で、パース回数、キャッシュヒット、開きっぱなしカーソルを確認します。設定値を上げる前に、SQL生成、CLOSE漏れ、BULK COLLECTの使い方を見直すのが、本番で安定するカーソル管理の基本です。