Using Oracle RESULT_CACHE "not for its intended purpose"
As many people know in Oracle DBMS, starting from version 11g, there appeared a technology for storing the result of executing a function for a given set of parameters in a dedicated cache, similar to buffer cache for data blocks.
In this article I am going to consider the option of using the "side effect" of this technology.
The essence of the use of result_cache is simple: if you access the function marked with the result_cache tag for the first time (for example: v: = foo ('X')), the result that this function returns for the 'X' parameter will be cached (remembered) and when you try to re-access foo ('X'), you just get the result from the cache.
Is it always like this? Not really. The result can be pushed out of the cache due to the fact that there is not enough space for new results; if a DML operation was invoked on an object that was linked at compile-time to the foo function with the RELISE_ON tag (for example: function foo (dept_id number) return number result_cache relies_on (employees)); if there was a call to the DDL operation that resulted in the function being recompilated; well, and finally, the result can be pushed out of the cache if the dbms_result_cache.invalidate function call has occurred, which will indicate the need to clear the cache for a given function explicitly or by using other results cache administration operations.
But in the general case, we can rely on the fact that with a constant function call, the desired set of function / parameter / value will be “hot” in the cache and this will not lead to unnecessary calls.
')
The “side effect” of this technology is that when calling a function, we rarely go through its code. Next I will give an example of how this can be used.
Let you have some kind of buffer table, which is constantly filled with data. And let there be a process that processes the incoming data and clears the table, but the data do not come at a constant speed, but with periods of high intensity and, on the contrary, periods of no new data.
In general, it is possible to create an application for each record in Oracle AQ for its processing and for the Oracle Scheduler process to process these applications. When the Scheduler is idle, the process is terminated, and when Event occurs, records appear in the queue again. But this is not the most effective way to implement the task, both in terms of the load on the base and the CPU and IO, and response time operations.
Another option is to constantly keep a JOB process that scans the table for the appearance of a record and does useful work when a record appears, but - for example - the number of simultaneously running job jobs in the DBMS is limited and to keep such a process in the absence of useful work, and just turn constantly empty cycles are also not the most effective solution. This is where we can use the “properties” of the result_cache functions.
Those. we create a certain function that checks the presence of the running task and, in the case of absence, starts it. We mark this function as result_cache. In the task for processing the table itself, we enter the completion of the task in case of long idle time, and at completion (even in the case of exception) we reset the result_cache for the function that starts the task. Well and - finally - we hang a trigger on the table that is triggered when a DML operation is performed on it (for example, when inserting a record) at the statement level.
Now, in the absence of a work order, when an entry is inserted into the table, it will be launched, and if there is a job, just check the “flag” in the cache, which is fast enough. Completion of the processing process will result in clearing the cache according to the function, and the next time the trigger is triggered, the process handler will rise again.
PS> It should be borne in mind that this script is only a fish. For full-fledged work, at least you need to add a lock via DBMS_LOCK and at the point of completion of the processing (i.e., at the cache reset point).
PPS> This example does not take into account the specifics of the result_cache operation on Oracle RAC, namely, the result cache for each instance is different, i.e. the function will run at least once on each of the instances; resetting the cache on any of the instances will reset the entire database.
PPPS> This is just an example. an easy implementation of a finite state machine of two states, and not a recommendation for action on solving a specific problem
In this article I am going to consider the option of using the "side effect" of this technology.
The essence of the use of result_cache is simple: if you access the function marked with the result_cache tag for the first time (for example: v: = foo ('X')), the result that this function returns for the 'X' parameter will be cached (remembered) and when you try to re-access foo ('X'), you just get the result from the cache.
Is it always like this? Not really. The result can be pushed out of the cache due to the fact that there is not enough space for new results; if a DML operation was invoked on an object that was linked at compile-time to the foo function with the RELISE_ON tag (for example: function foo (dept_id number) return number result_cache relies_on (employees)); if there was a call to the DDL operation that resulted in the function being recompilated; well, and finally, the result can be pushed out of the cache if the dbms_result_cache.invalidate function call has occurred, which will indicate the need to clear the cache for a given function explicitly or by using other results cache administration operations.
But in the general case, we can rely on the fact that with a constant function call, the desired set of function / parameter / value will be “hot” in the cache and this will not lead to unnecessary calls.
')
The “side effect” of this technology is that when calling a function, we rarely go through its code. Next I will give an example of how this can be used.
Let you have some kind of buffer table, which is constantly filled with data. And let there be a process that processes the incoming data and clears the table, but the data do not come at a constant speed, but with periods of high intensity and, on the contrary, periods of no new data.
In general, it is possible to create an application for each record in Oracle AQ for its processing and for the Oracle Scheduler process to process these applications. When the Scheduler is idle, the process is terminated, and when Event occurs, records appear in the queue again. But this is not the most effective way to implement the task, both in terms of the load on the base and the CPU and IO, and response time operations.
Another option is to constantly keep a JOB process that scans the table for the appearance of a record and does useful work when a record appears, but - for example - the number of simultaneously running job jobs in the DBMS is limited and to keep such a process in the absence of useful work, and just turn constantly empty cycles are also not the most effective solution. This is where we can use the “properties” of the result_cache functions.
Those. we create a certain function that checks the presence of the running task and, in the case of absence, starts it. We mark this function as result_cache. In the task for processing the table itself, we enter the completion of the task in case of long idle time, and at completion (even in the case of exception) we reset the result_cache for the function that starts the task. Well and - finally - we hang a trigger on the table that is triggered when a DML operation is performed on it (for example, when inserting a record) at the statement level.
Now, in the absence of a work order, when an entry is inserted into the table, it will be launched, and if there is a job, just check the “flag” in the cache, which is fast enough. Completion of the processing process will result in clearing the cache according to the function, and the next time the trigger is triggered, the process handler will rise again.
Below is the code that implements this method.
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> It should be borne in mind that this script is only a fish. For full-fledged work, at least you need to add a lock via DBMS_LOCK and at the point of completion of the processing (i.e., at the cache reset point).
PPS> This example does not take into account the specifics of the result_cache operation on Oracle RAC, namely, the result cache for each instance is different, i.e. the function will run at least once on each of the instances; resetting the cache on any of the instances will reset the entire database.
PPPS> This is just an example. an easy implementation of a finite state machine of two states, and not a recommendation for action on solving a specific problem
Source: https://habr.com/ru/post/196360/
All Articles