e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第81回 「再帰コールではオープン・カーソル数に注意、およびその対策」

2013.12.16

こんにちは。インストラクターの蓑島です。

前回まで、3回にわたり再帰コールについて解説してきました。
今回は再帰コールの解説の最後として、再帰コールの注意点について説明します。

再帰コールの注意点はリソースを多く消費しがちであることです。
つまり、再帰コールは、いわば自分自身のコピーをコールすることなので、コールが連鎖することでコピーの数が増えて、リソースを大量に消費します。特に明示カーソル処理を行っている場合、カーソルをクローズする前に再帰コールを行う内容だと、コールが連鎖するにつれてオープンされているカーソルの数が増えていくので、いずれ上限を超えると「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーとなります。したがって、明示カーソル処理と再帰コールの組み合わせは要注意です。

以下、そのような再帰コールにおいてオープン・カーソル数が上限を超えるケースの再現、およびその回避策について実演しながら解説します。

まず再帰コール+明示カーソル処理の例として、バックナンバー第79回「再帰コールで階層問い合わせをしてみる」の例を使います。
この回で取り上げたPROC79プロシージャは、社員表で起点となる社員を指定すると、その部下、さらにその部下の部下、さらにその部下の部下の部下・・・・といったように、どんどんと部下の階層に下りて問い合わせを行います。明示カーソルをクローズせずに再帰コールをしているので、階層が深いと、「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーとなります。

では、「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーを実際に起こしてみます。

まず最大オープン・カーソル数の制限値を調べるために、データベースの初期化パラメータ「OPEN_CURSORS」を確認します。

1
2
3
4
5
6
7
8
show user
ユーザーは "SYSTEM" です。    -- 管理者ユーザで、
 
SHOW PARAMETER OPEN_CURSORS     -- OPEN_CURSORSパラメータの値を調べる
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

私の環境では、OPEN_CURSORS = 300 となっています。つまり1セッションでオープンできるカーソルの上限は300です。
とういことは、300階層よりも深い階層を含む表をPROC79プロシージャのやり方で再帰コールすると、「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーとなるはずです。

そのエラーを再現するためには、300階層以上の階層を含む表と、その表に対してPROC79プロシージャと同じロジックで再帰コールをするプロシージャが必要です。なるべく簡単にしたいので、その表をTEST81表という名前にして、そのプロシージャをPROC81という名前でそれぞれ作成します。

まず、TEST81表を作成します。簡単な社員表です。ここに、上司、部下の関連のある社員をロードします。必要最低限の列だけにしています。

1
2
3
4
5
6
7
8
9
CREATE TABLE TEST81
(
ID  NUMBER CONSTRAINT TEST81_PK PRIMARY KEY ,
NAME   VARCHAR2(100),
MGR_ID   NUMBER CONSTRAINT  TEST81_SELF_FK REFERENCES TEST81(ID)
)
/
 
表が作成されました。

このTEST81表のID列は社員番号で主キーです。MGR_ID列がID列を参照します。いわばMGR_ID列は社員表の上司番号に相当します。NAME列は社員名です。
MGR_ID列はその社員の上司のID列の値を参照します。MGR_ID列によって、上司と部下の関係付けがなされます。

続いて、TEST81表のID列(社員番号列)の値ですが、主キーですから一意な値が必要です。今回は順序オブジェクトからその値を取得したいと思います。名前をSEQ_TEST81にして、順序オブジェクトを作成します。

1
2
3
CREATE SEQUENCE SEQ_TEST81;
 
順序が作成されました。

では、SEQ_TEST81順序オブジェクトを使って、TEST81表に300階層以上の上司・部下の関係をもつデータをロードします。1人の上司の部下は1人とします。そうでないと300階層では天文学的な行数となってしまいます。仮に1人の上司に2人の部下だとすると、300階層なら社員の総数は、(2の300乗 - 1)という、とんでもない数になります。1人の上司に部下が1人なら社員の総数は、300人ですみます。
ここで問題なのは表の行数(社員の数)ではなく階層の深さです。階層の数だけのカーソルがオープンし、オープンカーソルの上限を超える場面を再現できます。

以下はTEST81表に1人の上司に1人の部下として、301階層の社員をロードするPL/SQLブロックです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
    V_ID   NUMBER;
BEGIN
    -- 最上位の階層を作成する(社員表の社長に相当する行)
    INSERT INTO TEST81(ID, NAME , MGR_ID) VALUES (SEQ_TEST81.NEXTVAL, 'AAA' , NULL )
    RETURNING ID INTO V_ID;
    -- その下に300階層を作成する(一人の上司に一人の部下で300階層)
    FOR I IN 1..300  LOOP
       INSERT INTO TEST81(ID, NAME , MGR_ID)
       VALUES (SEQ_TEST81.NEXTVAL, 'AAA' || TRIM(TO_CHAR(V_ID, '0000' )),V_ID)
       RETURNING ID INTO V_ID;
    END LOOP;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

これにより、301階層の上司・部下の連鎖がTEST81表にロードできました。 簡単に解説すると、5-6行目のINSERT文は一番上の階層の行(社長の行)をINSERTしています。ID列の値は、SEQ_TEST81.NEXTVALなので、順序オブジェクトSEQ_TEST81から取得した一意な番号です。また一番上の階層なので、上司はおらず、MGR_ID列の値はNULLとなっています。
ここで注目すべきは、6行目の「RETURNING ID INTO V_ID」という記述です。これはINSERTに使われたID列の値を変数V_IDに代入するという意味です。これにより、INSERTのID列の値は、変数V_IDに格納されます。この変数の値は、次の行のMGR_ID列の値として使われます。

8-12行目で300回のループ処理を行っていますが、1回目のループのとき、ここでのINSERT文のMGR_ID列の値は、上の6行目で取得されたV_ID変数です。
そしてそのINSERT文にも、「RETURNING ID INTO V_ID」の記述があります(11行目)。このV_ID変数の値はループの2回目以降のINSERT文のMGR_ID列として使われるわけです。

このように次にINSERTされる行は常にその一つ前の行の部下という形でデータがロードされていきます。

このようにして、全体で、最上位の階層を含めて、301行の上司・部下の連鎖がロードできました。この表には301階層が含まれているわけです。

実際にこの表を問い合わせてみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT * FROM TEST81 ORDER BY ID;
 
         ID NAME                     MGR_ID
---------- -------------------- ----------
          1 AAA
          2 AAA0001                       1
          3 AAA0002                       2
          4 AAA0003                       3
          5 AAA0004                       4
          6 AAA0005                       5
               ~途中、省略 ~
        299 AAA0298                     298
        300 AAA0299                     299
        301 AAA0300                     300
 
301行が選択されました。

ご覧のように、社員番号(ID列)が1番から301番まで301行の社員が登録されていますね。MGR_ID列の値が、一つ前の行のID列となっています。

では次に、上司、部下の表示を再帰コールで行うプロシージャPROC81を作成します。このプロシージャの基本的なロジックは、バックナンバー第79回のPROC79と同じです。

ではそのPROC81プロシージャを作成します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE
PROCEDURE PROC81( P_EMPNO IN TEST81.ID%TYPE, P_LEVEL IN NUMBER DEFAULT 1)
/************************************************************************/
-- 再帰コールで階層問い合わせを行うプロシージャ(カーソルオープン中に再帰コール)
/************************************************************************/
IS
     REC  TEST81%ROWTYPE;
BEGIN
/**************************************************************/
--  指定された社員の行を取得する
/**************************************************************/
     SELECT * INTO REC FROM TEST81 WHERE ID = P_EMPNO;
/**************************************************************/
--  その社員の情報を表示する
/**************************************************************/
     DBMS_OUTPUT.PUT_LINE( TO_CHAR(P_LEVEL, '0000' ) || ' '
             || TO_CHAR(REC.ID, '0000' ) || ' ' || REC. NAME || ' ' || REC.MGR_ID);
/***********************************************************************/
--  その社員の部下それぞれに対して、階層を一つ深くして同じ処理を行う
/**********************************************************************/
     FOR REC IN ( SELECT * FROM TEST81 WHERE MGR_ID = P_EMPNO ORDER BY NAME )  LOOP
         PROC81(REC.ID, P_LEVEL+1);     -- 再帰コール
     END LOOP;
END PROC81;
/
 
プロシージャが作成されました。

上のソースコードで注目していただきたいのは、22行目です。カーソルFORループ文(21~23行目)の中で、再帰コールを行っています。
つまり、カーソルがオープンしてる最中に再帰コールをしています。

よってこのプロシージャを一番上の行の社員番号1を指定して実行すると、一番したの階層まで300階層あるので、300個のカーソルが同時にオープンしますが、途中でオープンカーソルの上限に達して、エラーとなります。

実際に実行してみます。
SQL> SET SERVEROUTPUT ON -- DBMS_OUTPUTの画面出力有効にする

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
EXEC PROC81(1)
0001  0001 AAA
0002  0002 AAA0001 1
0003  0003 AAA0002 2
0004  0004 AAA0003 3
    ~途中、省略 ~
0293  0293 AAA0292 292
0294  0294 AAA0293 293
0295  0295 AAA0294 294
0296  0296 AAA0295 295
0297  0297 AAA0296 296
BEGIN PROC81(1); END ;
 
*
行1でエラーが発生しました。:
ORA-01000: 最大オープン・カーソル数を超えました。

ご覧のように、私の環境では297階層目でエラーとなっています。
これは、297の社員の部下を問い合わすカーソルFORループ文を実行しようとしたときに、オープン・カーソルの最大数300を超えたために、エラーとなったものです。若干、数が合わないように思われますが、内部的にバックグラウンドでオープンされているカーソルもあるためです。
ですから、おおむね予定通りの結果としてエラーになりました。

では、この処理をエラーなく最後まで完了させるには、どうすればよいかというと、カーソルをクローズしてから再帰コールするようなロジックに書き換えればいいわけです。そのためには、カーソルからの取得行をいったん配列(コレクション)変数に格納して、すみやかにカーソルをクローズします。次にその配列を使って、一件ずつ再帰コールを行えばいいわけです。

ではそのような形に書き換えてみましょう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE OR REPLACE
PROCEDURE PROC81( P_EMPNO IN TEST81.ID%TYPE, P_LEVEL IN NUMBER DEFAULT 1)
/************************************************************************/
-- 再帰コールで階層問い合わせを行うプロシージャ(カーソルをクローズしてから再帰コール)
/***********************************************************************/
IS
     REC  TEST81%ROWTYPE;
     TYPE REC_TAB_TYPE IS TABLE OF TEST81%ROWTYPE;
     REC_TAB    REC_TAB_TYPE;        -- 部下の情報を格納する配列変数(コレクション)
BEGIN
/**************************************************************/
--  指定された社員の行を取得する
/**************************************************************/
     SELECT * INTO REC FROM TEST81 WHERE ID = P_EMPNO;
/**************************************************************/
--  その社員の情報を表示する
/**************************************************************/
     DBMS_OUTPUT.PUT_LINE( TO_CHAR(P_LEVEL, '0000' ) || ' '
             || TO_CHAR(REC.ID, '0000' ) || ' ' || REC. NAME || ' ' || REC.MGR_ID);
/***********************************************************************/
--  その社員の部下それぞれに対して、階層を一つ深くして同じ処理を行う
/**********************************************************************/
     -- 部下の情報を配列変数に取得(取得後、明示カーソルはつかっていない)
     SELECT * BULK COLLECT INTO REC_TAB FROM TEST81 WHERE MGR_ID = P_EMPNO ORDER BY NAME ;
     IF REC_TAB. COUNT > 0 THEN      -- 部下が存在するときは、以下の処理
        FOR I IN  REC_TAB. FIRST ..REC_TAB. LAST  LOOP     -- コレクションのループ処理で
             PROC81(REC_TAB(I).ID, P_LEVEL+1);            -- 各部下に対して再帰コール
        END LOOP;
     END IF;
END PROC81;
/
 
プロシージャが作成されました。

上記のソースコードで注目していただきたいのは、24行目です。
ここで、従来はカーソルFORループ文で処理していたSELECT文を、「SELECT BULK COLLECT INTO文」で先に配列変数(REC_TAB)に一括代入します。この「SELECT BULK COLLECT INTO文」では処理の間だけ内部的なカーソルを使用しますが、文の実行後、そのカーソルはすみやかにクローズしていますのでこれ以降の処理で明示的なカーソルはオープンしてません。そして、その配列変数(REC_TAB)の一件ずつに対してループ処理で、再帰コールを行っています。

では実際に実行してみます。

1
2
3
4
5
6
7
8
9
10
11
SQL> EXEC PROC81(1)
0001  0001 AAA
0002  0002 AAA0001 1
0003  0003 AAA0002 2
    ~途中、省略 ~
0298  0298 AAA0297 297
0299  0299 AAA0298 298
0300  0300 AAA0299 299
0301  0301 AAA0300 300
 
PL/SQLプロシージャが正常に完了しました。

今度は、最後の301行目までエラーなく、表示できましたね。
どこまで連鎖しても、オープンカーソルの数は増えないので、エラーとはならないわけです。

このように、再帰コールの連鎖の数が多い場合は、明示カーソルをクローズしてから、再帰コールするように修正することで、オープン・カーソル数を減らすことができます。

それでは再帰コールについてはここまでにしたいと思います。
また次回、ご期待ください。

先頭へ戻る