Oracle12c PROCEDURE/FUNCTION COMPILE時のORA-04068回避方法
概要
SESSION-AでFUNCTIONをRECOMPILEすると、その以前から接続していたSESSION-Bで、
ORA-04068が発生するというので、調査した際のメモです。
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production CentOS Linux release 7.3.1611 (Core)
検証
ORA-04068の再現テスト
まずSESSION-AでFUNCTIONを作成します。
create table TAB_4068 ( pid number(1), wstr varchar2(256) ); insert into TAB_4068 values ( 1, 'hoge' ); commit;
CREATE OR REPLACE PACKAGE PACK_4068 AS ret varchar2(256); FUNCTION P4068 RETURN VARCHAR2; END PACK_4068; / CREATE OR REPLACE PACKAGE BODY PACK_4068 AS FUNCTION P4068 RETURN VARCHAR2 IS BEGIN select wstr into ret from TAB_4068 where pid = 1; RETURN ret; END; END PACK_4068; /
次にSESSION-B側でFUNCTIONを実行してみます。
ここでは正常に実行できることを確認しているだけです。
SQL> DECLARE str varchar2(256); BEGIN str := PACK_4068.P4068; END; / 2 3 4 5 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL>
SESSION-Aで、テーブル定義を変更して、BODYがINVALIDになることを確認し、
SQL> ALTER TABLE TAB_4068 MODIFY ( wstr char(10) ); Table altered. Elapsed: 00:00:00.13 SQL> SQL> @object Enter value for object_name: PACK_4068 OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS -------------------- ------------------------------ ------------------ ------------------- ------------------- ------- TEST01 PACK_4068 PACKAGE 2017/12/18 19:19:17 2017/12/18 19:19:17 VALID TEST01 PACK_4068 PACKAGE BODY 2017/12/18 19:19:24 2017/12/18 19:19:24 INVALID 2 rows selected. Elapsed: 00:00:00.25 SQL>
recompileを実行して、VALIDに戻します。
SQL> ALTER PACKAGE PACK_4068 COMPILE BODY; Package body altered. Elapsed: 00:00:00.03 SQL> SQL> @object Enter value for object_name: PACK_4068 OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS -------------------- ------------------------------ ------------------ ------------------- ------------------- ------- TEST01 PACK_4068 PACKAGE 2017/12/18 19:19:17 2017/12/18 19:19:17 VALID TEST01 PACK_4068 PACKAGE BODY 2017/12/18 19:19:24 2017/12/18 19:20:58 VALID
そして、SESSION-Bから再度FUNCTIONを実行すると、ORA-04068が発生します。
SQL> DECLARE str varchar2(256); BEGIN str := PACK_4068.P4068; END; / 2 3 4 5 6 DECLARE * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "TEST01.PACK_4068" has been invalidated ORA-04065: not executed, altered or dropped package body "TEST01.PACK_4068" ORA-06508: PL/SQL: could not find program unit being called: "TEST01.PACK_4068" ORA-06512: at line 4
ドキュメント確認
https://docs.oracle.com/cd/E18283_01/server.112/e17128/chapter1_2.htm 2.1.1.5 Improvements to Reduce the Occurrences of "Existing State of Packages Discarded" Errors
DBMS_SESSION.RESET_SELFをCALLすると、04068の問題が解決されるようになりました、
とのお話のようですが、
When the body of a stateful package is recompiled by session A, then a different session B which has already instantiated that package will experience the ORA-04068 existing state of packages… has been discarded family of errors on its next reference to that package following the recompilation. The result is severe disruption for the user of session B. Two improvements in this release now reduce the likelihood of these ORA-04068 errors: A package, whose global variables is marked constant; and where the initial value can be computed at compile time, is now considered to be stateless. The new procedure DBMS_SESSION.RESET_SELF(), whose invocation must be in the package of interest, allows you to preempt the problem when it is safe to reinitialize the package state. The advantage is that packages (and especially those that are not editioned) can be "hot patched" (that is, recompiled) when other sessions are using them with a greatly reduced likelihood of disrupting those sessions.
探してもRESET_SELFというメンバーは存在しません。
代わりに?RESET_PACKAGEというメンバーが存在しますが。
select owner, object_name, OBJECT_TYPE from dba_objects where object_name like '%DBMS_SESSION%' OWNER OBJECT_NAME OBJECT_TYPE -------------------- ------------------------------ ------------------ SYS DBMS_SESSION PACKAGE SYS DBMS_SESSION_STATE PACKAGE SYS DBMS_SESSION PACKAGE BODY SYS DBMS_SESSION_STATE PACKAGE BODY SQL> select * from dba_source where name = 'DBMS_SESSION' and text like '%RESET%'; OWNER NAME TYPE LINE TEXT ORIGIN_CON_ID -------------------- ---------------- ------------ ---------- ------------------------------------------------------------ ------------- SYS DBMS_SESSION PACKAGE 73 -- same as DBMS_SESSION.RESET_PACKAGE() interface. 1
RESET_PACKAGE
再現テストからの変更点は、BODYに下記を１行追加しただけです。
DBMS_SESSION.RESET_PACKAGE();
どうなったか。
SQL> create table TAB_4068 ( pid number(1), wstr varchar2(256) ); insert into TAB_4068 values ( 1, 'hoge' ); commit; Table created. Elapsed: 00:00:00.01 SQL> 1 row created. Elapsed: 00:00:00.00 SQL> Commit complete. Elapsed: 00:00:00.01 SQL> SQL> CREATE OR REPLACE PACKAGE PACK_4068 AS ret varchar2(256); FUNCTION P4068 RETURN VARCHAR2; END PACK_4068; / 2 3 4 5 Package created. Elapsed: 00:00:00.02 SQL> SQL> CREATE OR REPLACE PACKAGE BODY PACK_4068 AS FUNCTION P4068 RETURN VARCHAR2 IS BEGIN DBMS_SESSION.RESET_PACKAGE(); select wstr into ret from TAB_4068 where pid = 1; RETURN ret; END; END PACK_4068; / 2 3 4 5 6 7 8 9 10 Package body created. Elapsed: 00:00:00.03 SQL> SQL> SQL> ALTER TABLE TAB_4068 MODIFY ( wstr char(10) ); Table altered. Elapsed: 00:00:00.02 SQL> SQL> @object Enter value for object_name: PACK_4068 OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS -------------------- ------------------------------ ------------------ ------------------- ------------------- ------- TEST01 PACK_4068 PACKAGE 2017/12/18 19:24:36 2017/12/18 19:24:36 VALID TEST01 PACK_4068 PACKAGE BODY 2017/12/18 19:24:46 2017/12/18 19:24:46 INVALID 2 rows selected. Elapsed: 00:00:00.25 SQL> SQL> ALTER PACKAGE PACK_4068 COMPILE BODY; Package body altered. Elapsed: 00:00:00.02 SQL> SQL> @object Enter value for object_name: PACK_4068 OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS -------------------- ------------------------------ ------------------ ------------------- ------------------- ------- TEST01 PACK_4068 PACKAGE 2017/12/18 19:24:36 2017/12/18 19:24:36 VALID TEST01 PACK_4068 PACKAGE BODY 2017/12/18 19:24:46 2017/12/18 19:25:33 VALID 2 rows selected. Elapsed: 00:00:00.08 SQL>
同じ操作でエラー回避されました。
SQL> DECLARE str varchar2(256); BEGIN str := PACK_4068.P4068; END; / 2 3 4 5 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL>
ただ、DBMS_SESSION.RESET_PACKAGEの詳しいドキュメントが見当たらず、
実際に問題なく使用出来るかは難しいところです。
追記：ドキュメントありました。
RESET_PACKAGEプロシージャ このプロシージャは、このセッションのすべてのパッケージのインスタンス化を解除します。 これは、パッケージ状態を解放します。 実行状態をキャッシュするために使用するメモリーは、セッションで実行された PL/SQLファンクション、プロシージャおよびパッケージに関連付けられています。 パッケージに関して、このメモリーのコレクションはパッケージ変数の現行の値を保持し、 各PL/SQLプログラムによってオープンされたカーソルのキャッシュを制御します。 RESET_PACKAGEへのコールは、以前実行した各PL/SQLプログラムに関連付けられていたメモリーをセッションから解放し、 この結果、グローバルなパッケージの現行の値は消去され、キャッシュされたカーソルはクローズされます。 RESET_PACKAGEは、セッションで失敗したプログラムを確実に再起動するためにも使用できます。 パッケージ変数を含んだプログラムが失敗すると、どの変数を初期化しなおす必要があるかを判別することは困難です。 RESET_PACKAGEは、すべてのパッケージ変数が初期値に再設定されることを保証します。 使用上の注意 すべての実行PL/SQLが消費するメモリーは大量になるため、RESET_PACKAGEを使用して、 データベース・アプリケーション内のある時点でセッション・メモリー・フットプリントを削減できます。 ただし、パッケージ変数値の再設定がアプリケーションに影響を与えないことを確認してください。 また、キャッシュしたメモリーとカーソルのないプログラムを後で実行すると、 解放されたメモリーとカーソルを再作成する必要があるため、実行速度が遅くなることに留意してください。 RESET_PACKAGEは、メモリー、カーソルおよびパッケージ変数をコール直後に解放しません。 たとえば、PL/SQLプロシージャP1がPL/SQLプロシージャP2をコールし、P2がRESET_PACKAGEをコールしたとします。 プロシージャP1の実行が完了するまで(PL/SQLコールが終了するまで)、RESET_PACKAGEの処理は行われません。
単純なローカルテストでは問題無くとも、オンラインで常にCALLされる場合はやはり難しいところがありそうです。
