🤯 🍑 🥛 Oracle RESULT_CACHE「オフラベル」の使用 ☃️ 🏚️ ↖️

Oracle DBMSで多くの人が知っているように、バージョン11g以降、データブロックのバッファーキャッシュと同様に、このために特別に割り当てられたキャッシュに、特定のパラメーターセットの関数を実行した結果を保存するテクノロジーが登場しました。
この記事では、このテクノロジーの「副作用」を使用するオプションを検討します。

result_cacheの適用の本質は簡単です：result_cacheタグでマークされた関数に初めてアクセスした場合（例：v：= foo（ 'X'））、この関数が 'X'パラメーターに対して返す結果はキャッシュされます（記憶されます） foo（ 'X'）に再度アクセスしようとすると、キャッシュから結果が得られます。

これは常にそうですか？そうでもない。新しい結果のためのスペースが不足しているため、結果をキャッシュからプッシュできます。コンパイル時にRELISE_ONタグを持つfoo関数と関連付けられたオブジェクトに対してDML操作が呼び出された場合（例：function foo（dept_id number）return number result_cache relies_on（employees））; 関数の再コンパイルにつながるDDL操作呼び出しが発生した場合。最後に、dbms_result_cache.invalidate関数の呼び出しが発生した場合、結果をキャッシュからプッシュできます。これは、特定の関数のキャッシュを明示的に、または結果キャッシュを管理する他の操作によってクリアする必要があることを示します。

しかし、一般的なケースでは、関数の定数呼び出しでは、必要な関数/パラメーター/値のセットがキャッシュ内で「ホット」になり、これにより不必要な呼び出しが行われないという事実に依存できます。

このテクノロジーの「副作用」は、関数を呼び出すときに、そのコードをほとんど使用しないことです。次に、これを使用する方法の例を示します。

常にデータが入力されているバッファテーブルがあるとします。そして、着信データを処理してテーブルをクリアする特定のプロセスがありますが、データは一定の速度で到着するのではなく、高強度の期間とは反対に、新しいデータが不足する期間があります。

一般に、Oracle AQの各レコードの処理のためにリクエストを作成し、これらのリクエストをOracle Schedulerプロセスで処理できます。スケジューラがアイドル状態になった瞬間、プロセスは終了し、イベントに従って、キュー内のエントリの外観が再び上昇します。ただし、これは、ベースとCPUおよびIOの負荷と応答時間操作の両方の観点から、タスクを実装する最も効果的な方法ではありません。
もう1つのオプションは、JOBプロセスを常に保持することです。これにより、レコードの外観をテーブルでスキャンし、レコードが表示されたときに有用な作業を行います。サイクルも最も効果的なソリューションではありません。ここでは、result_cache関数の「プロパティ」を使用できます。
つまり実行中のタスクの存在を確認し、存在しない場合は開始する特定の関数を作成します。この関数をresult_cacheとしてマークします。テーブルを処理するタスクでは、長いダウンタイムの場合にタスクの完了を入力し、最後に（例外の場合でも）、タスクを開始する関数のresult_cacheをリセットします。最後に、ステートメントレベルのDML操作が（たとえば、レコードが挿入されたときに）実行されたときに起動するトリガーをテーブルにハングアップします。
現在、作業タスクがない場合、テーブルにエントリを挿入すると、タスクが開始されます。タスクがある場合は、キャッシュ内の「フラグ」をチェックします。これは非常に迅速な操作です。処理プロセスが完了すると、機能ごとにキャッシュがクリアされ、次にトリガーが起動されると、プロセッサプロセスが再び立ち上がります。

以下は、このメソッドを実装するコードです

script.sql

set echo off set verify off set linesize 192 set trim on set trims on spool script.log DROP TABLE EVT_TBL PURGE; DROP TABLE EVT_LOG PURGE; DROP SEQUENCE EVT_SEQ; DROP FUNCTION EVT_CHECK_JOB; DROP PROCEDURE EVT_CREATE_RECORD; DROP PROCEDURE EVT_PRECESS_JOB; -- CREATE TABLE EVT_TBL ( N NUMBER PRIMARY KEY ,V VARCHAR2(100) ); CREATE SEQUENCE EVT_SEQ; CREATE TABLE EVT_LOG ( N NUMBER ,V VARCHAR2(100) ,D DATE DEFAULT SYSDATE ); CREATE OR REPLACE PROCEDURE EVT_CREATE_RECORD IS BEGIN INSERT INTO EVT_TBL SELECT EVT_SEQ.NEXTVAL, 'ID: '||EVT_SEQ.CURRVAL FROM DUAL; END EVT_CREATE_RECORD; / BEGIN FOR I IN 1..9 LOOP EVT_CREATE_RECORD; END LOOP; COMMIT; END; / CREATE OR REPLACE FUNCTION EVT_CHECK_JOB RETURN BOOLEAN RESULT_CACHE IS V_JOB INTEGER; PROCEDURE START_JOB IS PRAGMA AUTONOMOUS_TRANSACTION; C_LOCKHANDLE CONSTANT NUMBER := 13617637; V_RESULT NUMBER; BEGIN V_RESULT := DBMS_LOCK.REQUEST ( id => C_LOCKHANDLE ,lockmode => DBMS_LOCK.X_MODE ,release_on_commit => true ); SELECT MAX(J.JOB) INTO V_JOB FROM DBA_JOBS J WHERE J.LOG_USER = USER AND J.WHAT LIKE '%EVT_PRECESS_JOB;%'; IF V_JOB IS NULL THEN DBMS_JOB.submit(job => V_JOB, what => 'EVT_PRECESS_JOB;',next_date => SYSDATE+2/24/3600); INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'START JOB: "EVT_PRECESS_JOB;"', SYSDATE FROM DUAL; COMMIT; V_RESULT := NULL; END IF; IF V_RESULT = 0 THEN V_RESULT := DBMS_LOCK.RELEASE(lockhandle => C_LOCKHANDLE); END IF; EXCEPTION WHEN OTHERS THEN IF V_RESULT = 0 THEN V_RESULT := DBMS_LOCK.RELEASE(lockhandle => C_LOCKHANDLE); END IF; RAISE; END; PROCEDURE LOG_EXECUTE IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'EXECUTE: "EVT_CHECK_JOB;"', SYSDATE FROM DUAL; COMMIT; END; BEGIN LOG_EXECUTE; START_JOB; RETURN TRUE; END EVT_CHECK_JOB; / CREATE OR REPLACE PROCEDURE EVT_PRECESS_JOB IS C_MAX_INTERVAL CONSTANT INTEGER := 5; V_INTERVAL PLS_INTEGER := 0; V_REC EVT_TBL%ROWTYPE := NULL; V_ROWID UROWID := NULL; BEGIN WHILE V_ROWID IS NOT NULL OR V_INTERVAL < C_MAX_INTERVAL LOOP V_ROWID := NULL; BEGIN SELECT E.*, E.ROWID INTO V_REC.N, V_REC.V, V_ROWID FROM EVT_TBL E WHERE ROWNUM = 1 ORDER BY EN FOR UPDATE NOWAIT; V_INTERVAL := 1; EXCEPTION WHEN NO_DATA_FOUND THEN V_REC := NULL; V_INTERVAL := V_INTERVAL + 1; END; IF V_ROWID IS NOT NULL THEN INSERT INTO EVT_LOG (N, V, D) VALUES (V_REC.N, V_REC.V, SYSDATE); DELETE FROM EVT_TBL WHERE ROWID = V_ROWID; ELSE INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'SLEEP('||V_INTERVAL||'): "EVT_PRECESS_JOB;"', SYSDATE FROM DUAL; END IF; COMMIT; --      DBMS_LOCK.SLEEP(seconds => V_INTERVAL); END LOOP; INSERT INTO EVT_LOG SELECT EVT_SEQ.NEXTVAL * (-1), 'EXIT JOB: "EVT_PRECESS_JOB;"', SYSDATE FROM DUAL; DBMS_RESULT_CACHE.Invalidate(owner => USER, name => 'EVT_CHECK_JOB'); COMMIT; END EVT_PRECESS_JOB; / CREATE OR REPLACE TRIGGER EVT_TBL_TRG AFTER INSERT OR UPDATE ON EVT_TBL BEGIN IF EVT_CHECK_JOB THEN NULL; END IF; END; / select * from EVT_TBL; select N, V, TO_CHAR(D, 'HH24:MI:SS') AS D from EVT_LOG order by D desc; select job, what from user_jobs; begin EVT_CREATE_RECORD; commit; DBMS_LOCK.SLEEP(20); EVT_CREATE_RECORD; EVT_CREATE_RECORD; commit; DBMS_LOCK.SLEEP(30); EVT_CREATE_RECORD; commit; DBMS_LOCK.SLEEP(30); end; / select N, V, TO_CHAR(D, 'HH24:MI:SS') AS D from EVT_LOG order by D desc; spool off

script.log

  .  .  .  .  .  .  .  .  .  .  PL/SQL  .  .  .  . NV ---------- -------------------------------------------------- 1 ID: 1 2 ID: 2 3 ID: 3 4 ID: 4 5 ID: 5 6 ID: 6 7 ID: 7 8 ID: 8 9 ID: 9 9  .        PL/SQL  . NVD ---------- -------------------------------------------------- -------- -30 EXIT JOB: "EVT_PRECESS_JOB;" 15:06:45 -29 SLEEP(5): "EVT_PRECESS_JOB;" 15:06:40 -28 SLEEP(4): "EVT_PRECESS_JOB;" 15:06:36 -27 SLEEP(3): "EVT_PRECESS_JOB;" 15:06:33 -26 SLEEP(2): "EVT_PRECESS_JOB;" 15:06:31 23 ID: 23 15:06:30 -25 START JOB: "EVT_PRECESS_JOB;" 15:06:27 -24 EXECUTE: "EVT_CHECK_JOB;" 15:06:27 -22 EXIT JOB: "EVT_PRECESS_JOB;" 15:06:15 -21 SLEEP(5): "EVT_PRECESS_JOB;" 15:06:10 -20 SLEEP(4): "EVT_PRECESS_JOB;" 15:06:06 NVD ---------- -------------------------------------------------- -------- -19 SLEEP(3): "EVT_PRECESS_JOB;" 15:06:03 -18 SLEEP(2): "EVT_PRECESS_JOB;" 15:06:01 17 ID: 17 15:06:00 16 ID: 16 15:05:59 -15 SLEEP(4): "EVT_PRECESS_JOB;" 15:05:55 -14 SLEEP(3): "EVT_PRECESS_JOB;" 15:05:52 -13 SLEEP(2): "EVT_PRECESS_JOB;" 15:05:50 10 ID: 10 15:05:49 9 ID: 9 15:05:48 8 ID: 8 15:05:47 7 ID: 7 15:05:46 NVD ---------- -------------------------------------------------- -------- 6 ID: 6 15:05:45 5 ID: 5 15:05:44 4 ID: 4 15:05:43 3 ID: 3 15:05:42 2 ID: 2 15:05:41 1 ID: 1 15:05:40 -12 START JOB: "EVT_PRECESS_JOB;" 15:05:37 -11 EXECUTE: "EVT_CHECK_JOB;" 15:05:36 30  .

PS>このスクリプトは単なる魚であることに留意してください。適切に操作するには、少なくともDBMS_LOCKを介して、処理プロセスの完了時点（つまり、キャッシュリセットの時点）でロックを追加する必要があります。
PPS>この例では、Oracle RACでのresult_cacheの操作の詳細は考慮していません。つまり、各インスタンスには結果の独自のキャッシュがあります。関数は各インスタンスで少なくとも1回機能します。インスタンスのいずれかのキャッシュをフラッシュすると、データベース全体がフラッシュされます。
PPPS>これは単なる例です。特定の問題を解決するためのアクションの推奨ではなく、2つの状態の有限状態マシンの簡単な実装。

Oracle RESULT_CACHE「オフラベル」の使用

More articles: