Использование Oracle RESULT_CACHE 
«не по прямому назначению»

в 12:52, , рубрики: Без рубрики

Как многие знают в СУБД Oracle начиная с версии 11g появилась технология сохранения результата выполнения функции для заданного набора параметров в специально выделенном для этого cache по аналогии с buffer cache для блоков данных.
В данной статье я собираюсь рассмотреть вариант использования «побочного эффекта» данной технологии.

Суть применени result_cache проста: если вы обратились к функции, помеченной тегом result_cache, в первый раз ( например: v := foo('X') ), то результат, который данная функция вернёт для параметра 'X', будет прокэширован (запомнен) и при попытке повторного обращении к foo('X') вы просто получите результат из кэша.

Всегда ли это так? Не совсем. Результат может быть вытеснен из кэша по факту нехватки там места под новые результаты; если произошёл вызов операции DML над объектом, который был связан в момент компиляции с функцией foo тегом RELISE_ON (например: function foo (dept_id number) return number result_cache relies_on(employees) ); если произошёл вызов операции DDL, приведший к рекомпиляции функции; ну и — наконец — результат может быть вытеснен из кэша если произошёл вызов функции dbms_result_cache.invalidate, которая укажет на необходимость сброса кэша для заданной функции явно или другими операциями администрирования кэша результатов.

Но в общем случае мы можем полагаться на то, что при постоянном вызове функции нужный набор функция/параметр/значение будут «горячими» в кэше и это не будет приводить к излишним вызовам.

«Побочный» эффект данной технологии заключается в том, что при вызове функции мы крайне редко проходим по её коду. Далее я приведу пример того, как это можно использовать.

Пусть у Вас есть некая буферная таблица, которая постоянно заполняется данными. И пусть есть некий процесс, который обрабатывает вновь приходящие данные и очищает за собой таблицу, но данные поступают не с постоянной скоростью, а с периодами большой интенсивности и — наоборот — периодами отсутствия поступления новых данных.

В общем случае можно на каждую запись создавать заявку в Oracle AQ на её обработку и Oracle Scheduler процессом эти заявки обрабатывать. В момент простоя Scheduler процесс завершается, а по Event на появление записей в очереди вновь поднимается. Но это не самый эффективный способ реализации задачи как с точки зрения нагрузки на базу и по CPU и по IO, так и по response time операций.
Другой вариант — постоянно держать JOB процесс, который сканирует таблицу на появление записи и производит полезную работу при появлении записи, но — например — количество одновременно работающих job заданий в СУБД ограничено и держать такой процесс в случае отсутствия полезной работы, да и просто крутить постоянно пустые циклы тоже не самое эффективное решение. Вот тут мы и можем использовать «свойства» result_cache функций.
Т.е. мы создаём некую функцию, которая проверяет наличие запущенного задания и в случае отсутствия — запускает его. Эту функцию мы отмечаем как result_cache. В самом задании на обработку таблицы мы вводим завершение задания в случае длительного простоя, а при завершении (даже в случае exception) производим сброс result_cache для функции, запускающей задание. Ну и — наконец — на таблицу вешаем триггер, срабатывающий в случае выполнения над ней DML операции (например — при вставке записи) уровня statement.
Теперь в случае отсутствия рабочего задания при вставке записи в таблицу произойдёт его запуск, а при наличии задания — просто проверка «флага» в кэше, что достаточно быстрая операция. Завершение же процесса обработки приведёт к очистке кэша по функции и при следующем срабатывании триггера вновь подымется процесс-обработчик.

Ниже привожу код, реализующий данный метод

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;
    V_LOCKHANDLE VARCHAR2(100) := NULL;
  BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE (
      lockname        => 'EVT_CHECK_JOB'
     ,lockhandle      => V_LOCKHANDLE
     ,expiration_secs => 5
    );
    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;
    END IF;
    IF V_LOCKHANDLE IS NOT NULL THEN
      V_LOCKHANDLE := DBMS_LOCK.release(lockhandle => V_LOCKHANDLE);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      IF V_LOCKHANDLE IS NOT NULL AND
         DBMS_LOCK.RELEASE(lockhandle => V_LOCKHANDLE) IS NOT NULL
      THEN
        NULL;
      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 E.N
         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 успешно завершена.


Функция создана.


Процедура создана.


Триггер создан.


         N V
---------- --------------------------------------------------
         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 успешно завершена.


         N V                                                  D
---------- -------------------------------------------------- --------
       -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

         N V                                                  D
---------- -------------------------------------------------- --------
       -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

         N V                                                  D
---------- -------------------------------------------------- --------
         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 строк выбрано.

P.S.> Стоит иметь в виду, что данный скрипт это только рыба. Для полноценной работы как минимум необходимо добавить блокировку через DBMS_LOCK и в точке завершения процесса обработки (т.е. в точке сброса кэша).
P.P.S.> Данный пример не учитывает специфику работы result_cache на Oracle RAC, а именно — кэш результатов у каждого экземпляра свой, т.е. функция отработает как минимум раз на каждом из экземпляров; сброс кэша на любом из экземпляров произведёт сброс на всей базе.
P.P.P.S.> Это всего лишь пример, т.е. лёгкая реализация конечного автомата двух состояний, а не рекомендация к действию по решению конкретной задачи.

Автор: softwind

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js