Как многие знают в СУБД 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.
Теперь в случае отсутствия рабочего задания при вставке записи в таблицу произойдёт его запуск, а при наличии задания — просто проверка «флага» в кэше, что достаточно быстрая операция. Завершение же процесса обработки приведёт к очистке кэша по функции и при следующем срабатывании триггера вновь подымется процесс-обработчик.
P.S.> Стоит иметь в виду, что данный скрипт это только рыба. Для полноценной работы как минимум необходимо добавить блокировку через DBMS_LOCK и в точке завершения процесса обработки (т.е. в точке сброса кэша).
P.P.S.> Данный пример не учитывает специфику работы result_cache на Oracle RAC, а именно — кэш результатов у каждого экземпляра свой, т.е. функция отработает как минимум раз на каждом из экземпляров; сброс кэша на любом из экземпляров произведёт сброс на всей базе.
P.P.P.S.> Это всего лишь пример, т.е. лёгкая реализация конечного автомата двух состояний, а не рекомендация к действию по решению конкретной задачи.
В данной статье я собираюсь рассмотреть вариант использования «побочного эффекта» данной технологии.
Суть применени 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;
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 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.> Это всего лишь пример, т.е. лёгкая реализация конечного автомата двух состояний, а не рекомендация к действию по решению конкретной задачи.
