Behavior Of DETERMINISTIC & RESULT_CACHE Function In Oracle 19c
- Performance Tuning
- April 7, 2021
- 7 Comments
Oracle Database Cache improves the scalability and performance of applications that access Oracle databases by caching frequently used data on a middle-tier system. With Oracle Database Cache, your applications can process several times as many requests as their original capacity. In addition, you do not need to modify your existing applications to use Oracle Database Cache. Oracle Database Cache is transparent to your end users.
DETERMINISTIC Function
In computer science, a DETERMINISTIC algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.
• A function is considered to be DETERMINISTIC if it returns the same result value whenever it is called with the same values for its IN and IN OUT arguments.
• DETERMINISTIC functions have no side effects. If you pass a certain set of arguments for the parameters, you will always get the same results – no matter when, where or how often you run it.
• DETERMINISTIC caching is done only for the duration of the function call.Once the function gets executed,all the cached values are wiped out from PGA.
FUNCTION betwnstr (string_in IN VARCHAR2,start_in IN INTEGER,end_in IN INTEGER)
RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;It is possible to declare a procedure as DETERMINISTIC, but it NOT widely used.
• The Oracle documentation claims that defining PIPELINED table functions as DETERMINISTIC allows Oracle to buffer their rows, thereby preventing multiple executions.
• You cannot define a NESTED function as DETERMINISTIC.
• Function-Based Indexes can only be implemented on functions that are marked as DETERMINISTIC.
• The decision to use a saved copy of the function’s return result is made by the Oracle query optimizer. Saved copies can come from a materialized view, a function-based index, or a repetitive call to the same function in the same SQL statement.
SQL> CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
2 RETURN NUMBER
3 DETERMINISTIC
4 IS
5 BEGIN
6 DBMS_OUTPUT.put_line ('pass_number executed');
7 RETURN 0;
8 END;
9 /
Function created.
SQL> SELECT pass_number (1) result FROM all_objects WHERE ROWNUM < 6;
RESULT
----------
0
0
0
0
0
pass_number executed
SQL> DECLARE
2 n NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT pass_number (1) FROM all_objects WHERE ROWNUM < 6)
5 LOOP
6 n := n + 1;
7 END LOOP;
8
9 DBMS_OUTPUT.put_line (n + 1);
10 END;
11 /
--OUTPUT :::
pass_number executed
6
--Notice that even though the function was invoked 5 times, the body of the function was executed just once. Oracle Database created a tiny, little, short-lived cache, just for this function and just for the server call (PL/SQL block or SQL statement) in which it was invoked.--In the following example, labelling the function as DETERMINISTIC does improve performance, but the caching is limited to a single fetch, so it is affected by the array size. In this example, the array size is varied using the SQL*Plus "SET ARRAYSIZE n" command.
SQL> CREATE OR REPLACE FUNCTION slow_function (p_in IN NUMBER)
2 RETURN NUMBER DETERMINISTIC
3 AS
4 BEGIN
5 FOR indx IN 1..10000000
6 LOOP
7 FOR indx IN 1..10000000
8 LOOP
9 FOR indx IN 1..10000000
10 LOOP
11 NULL;
12 END LOOP;
13 END LOOP;
14 END LOOP;
15 RETURN p_in;
16 END;
17 /
Function created.
SQL> SET TIMING ON
SQL> SET ARRAYSIZE 15
SQL> SELECT slow_function(id) FROM func_test;
SLOW_FUNCTION(ID)
-----------------
1
2
1
2
1
2
1
2
1
3
Elapsed: 00:00:00.96
SQL> SET TIMING ON
SQL> SET ARRAYSIZE 2
SQL> SELECT slow_function(id) FROM func_test;
SLOW_FUNCTION(ID)
-----------------
1
2
1
2
1
2
1
2
1
3
Elapsed: 00:00:02.35
--The difference in array size produced drastically different performance, showing that caching is only available for the lifetime of the fetch. Subsequent queries (or fetches) have no access to the cached values of previous runs.SQL> CREATE TABLE employees2
2 (
3 emp_id NUMBER,
4 emp_name VARCHAR2(30 BYTE),
5 dept VARCHAR2(10 BYTE)
6 );
Table created.
SQL> INSERT ALL
2 INTO employees2 (emp_id,emp_name,dept) VALUES(100,'Steven King','515')
3 INTO employees2 (emp_id,emp_name,dept) VALUES(101,'Neena Kochhar','515')
4 INTO employees2 (emp_id,emp_name,dept) VALUES(102,'Lex De Haan','515')
5 INTO employees2 (emp_id,emp_name,dept) VALUES(103,'Alexander Hunold','590')
6 INTO employees2 (emp_id,emp_name,dept) VALUES(104,'Bruce Ernst','590')
7 INTO employees2 (emp_id,emp_name,dept) VALUES(105,'David Austin','A90')
8 INTO employees2 (emp_id,emp_name,dept) VALUES(106,'Valli Pataballa','590')
9 INTO employees2 (emp_id,emp_name,dept) VALUES(107,'Diana Lorentz','590')
10 INTO employees2 (emp_id,emp_name,dept) VALUES(108,'Nancy Greenberg','515')
11 INTO employees2 (emp_id,emp_name,dept) VALUES(109,'Daniel Faviet','515')
12 INTO employees2 (emp_id,emp_name,dept) VALUES(110,'John Chen','515')
13 INTO employees2 (emp_id,emp_name,dept) VALUES(111,'Ismael Sciarra','515')
14 INTO employees2 (emp_id,emp_name,dept) VALUES(112,'Jose Manuel Urman','A1A')
15 INTO employees2 (emp_id,emp_name,dept) VALUES(113,'Luis Popp','515')
16 INTO employees2 (emp_id,emp_name,dept) VALUES(114,'Den Raphaely','515')
17 INTO employees2 (emp_id,emp_name,dept) VALUES(115,'Alexander Khoo','515')
18 INTO employees2 (emp_id,emp_name,dept) VALUES(116,'Shelli Baida','515')
19 INTO employees2 (emp_id,emp_name,dept) VALUES(117,'Sigal Tobias','515')
20 INTO employees2 (emp_id,emp_name,dept) VALUES(118,'Guy Himuro','515')
21 INTO employees2 (emp_id,emp_name,dept) VALUES(119,'Karen Colmenares','A1A')
22 SELECT * FROM dual;
20 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM employees2;
EMP_ID EMP_NAME DEPT
---------- ------------------------------ ----------
100 Steven King 515
101 Neena Kochhar 515
102 Lex De Haan 515
103 Alexander Hunold 590
104 Bruce Ernst 590
105 David Austin A90
106 Valli Pataballa 590
107 Diana Lorentz 590
108 Nancy Greenberg 515
109 Daniel Faviet 515
110 John Chen 515
111 Ismael Sciarra 515
112 Jose Manuel Urman A1A
113 Luis Popp 515
114 Den Raphaely 515
115 Alexander Khoo 515
116 Shelli Baida 515
117 Sigal Tobias 515
118 Guy Himuro 515
119 Karen Colmenares A1A
20 rows selected.
--NON DETERMINISTIC
SQL> CREATE OR REPLACE FUNCTION is_number (p_value IN VARCHAR2)
2 RETURN CHAR
3 IS
4 p_num NUMBER;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('is_number ('||p_value||') Called');
7 p_num := TO_NUMBER(p_value);
8 RETURN 'Y';
9 EXCEPTION
10 WHEN value_error THEN
11 RETURN 'N';
12 END;
13 /
Function created.
SQL> SET TIMING ON
SQL> SELECT emp_id,emp_name,dept,is_number(dept) is_dept_numeric FROM employees2;
EMP_ID EMP_NAME DEPT IS_DEPT_NUMERIC
---------- ------------------------------ ---------- -----------------
100 Steven King 515 Y
101 Neena Kochhar 515 Y
102 Lex De Haan 515 Y
103 Alexander Hunold 590 Y
104 Bruce Ernst 590 Y
105 David Austin A90 N
106 Valli Pataballa 590 Y
107 Diana Lorentz 590 Y
108 Nancy Greenberg 515 Y
109 Daniel Faviet 515 Y
110 John Chen 515 Y
111 Ismael Sciarra 515 Y
112 Jose Manuel Urman A1A N
113 Luis Popp 515 Y
114 Den Raphaely 515 Y
115 Alexander Khoo 515 Y
116 Shelli Baida 515 Y
117 Sigal Tobias 515 Y
118 Guy Himuro 515 Y
119 Karen Colmenares A1A N
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (590) Called
is_number (590) Called
is_number (A90) Called
is_number (590) Called
is_number (590) Called
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (A1A) Called
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (515) Called
is_number (A1A) Called
Elapsed: 00:00:00.15
--DETERMINISTIC
SQL> CREATE OR REPLACE FUNCTION is_number (p_value IN VARCHAR2)
2 RETURN CHAR DETERMINISTIC
3 IS
4 p_num NUMBER;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('is_number ('||p_value||') Called');
7 p_num := TO_NUMBER(p_value);
8 RETURN 'Y';
9 EXCEPTION
10 WHEN value_error THEN
11 RETURN 'N';
12 END;
13 /
Function created.
SQL> SET TIMING ON
SQL> SELECT emp_id,emp_name,dept,is_number(dept) is_dept_numeric FROM employees2;
EMP_ID EMP_NAME DEPT IS_DEPT_NUMERIC
---------- ------------------------------ ---------- -------------------
100 Steven King 515 Y
101 Neena Kochhar 515 Y
102 Lex De Haan 515 Y
103 Alexander Hunold 590 Y
104 Bruce Ernst 590 Y
105 David Austin A90 N
106 Valli Pataballa 590 Y
107 Diana Lorentz 590 Y
108 Nancy Greenberg 515 Y
109 Daniel Faviet 515 Y
110 John Chen 515 Y
111 Ismael Sciarra 515 Y
112 Jose Manuel Urman A1A N
113 Luis Popp 515 Y
114 Den Raphaely 515 Y
115 Alexander Khoo 515 Y
116 Shelli Baida 515 Y
117 Sigal Tobias 515 Y
118 Guy Himuro 515 Y
119 Karen Colmenares A1A N
is_number (515) Called
is_number (515) Called
is_number (590) Called
is_number (A90) Called
is_number (A1A) Called
is_number (515) Called
is_number (A1A) Called
Elapsed: 00:00:00.10RESULT_CACHE
• Tells Oracle Database that you want to use some memory in the SGA or Shared Global Area to cache argument values and returned values.
• From that point on, whenever the function is invoked by any session in the database instance, the body of the function will only be executed if it has not already been called with those same input values.
• If there is a “hit” in the cache for that combination of arguments, the return value(s) will simply be grabbed from the cache and returned to the calling block.
• If there is a “miss” in the cache for that combination of arguments, the function will be executed and return the value (s) to the calling block.
If the function relies on (references) any database tables, when any user commits changes to that table, the cache for the function will be automatically wiped out.
• It is NOT possible to declare a procedure as RESULT_CACHE.
• A function can have both of these keywords (DETERMINISTIC & RESULT_CACHE) in its header. It can, in other words, be deterministic and result-cached.
RESULT_CACHE [ RELIES_ON (table_or_view [, table_or_view2 … table_or_viewN] ]
The RELIES_ON clause tells Oracle which tables or views the contents of the cache rely upon. This clause can only be added to the headers of schema-level functions and the implementation of a packaged function (that is, in the package body). As of Oracle Database 11g Release 2, it is deprecated.
FUNCTION betwnstr (string_in IN VARCHAR2,start_in IN INTEGER,end_in IN INTEGER)
RETURN VARCHAR2 RESULT_CACHE
IS
BEGIN
RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;SQL> CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
2 RETURN NUMBER
3 RESULT_CACHE
4 IS
5 BEGIN
6 DBMS_OUTPUT.put_line ('pass_number executed for ' || i);
7 RETURN 0;
8 END;
9 /
Function created.
SQL> DECLARE
2 n NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT pass_number (100)
5 FROM all_objects
6 WHERE ROWNUM < 6)
7 LOOP
8 n := n + 1;
9 END LOOP;
10
11 DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1));
12 END;
13 /
--OUTPUT :::
pass_number executed for 100
All done 6
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
3 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
4 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
5 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
6 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
7 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
8 END;
9 /
--OUTPUT :::
Returned 0
pass_number executed for 200
Returned 0
pass_number executed for 300
Returned 0
Returned 0
Returned 0
Returned 0
--I call pass_number a total of three times with an argument value of 100. Notice that the function body is executed just once, out of those three times - even though the calls came from two different blocks.
--And I call pass_number twice each with argument values of 200 and 300, but the function body is executed just once for each of those.
--If I disconnect from my session and reconnect, then run those same two blocks (without recompiling the function), I will see only :-
SQL> DECLARE
2 n NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT pass_number (100)
5 FROM all_objects
6 WHERE ROWNUM < 6)
7 LOOP
8 n := n + 1;
9 END LOOP;
10
11 DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1));
12 END;
13 /
--OUTPUT :::
All done 6
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
3 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
4 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
5 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
6 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
7 DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
8 END;
9 /
--OUTPUT :::
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
--The cache for a result-cached function persists across blocks, across sessions, across users. It is a complex feature that can have a ripple effect, both good and bad, across your application.SQL> CREATE OR REPLACE FUNCTION slow_function (p_in IN NUMBER)
2 RETURN NUMBER
3 RESULT_CACHE
4 AS
5 BEGIN
6 FOR indx IN 1..10000000
7 LOOP
8 FOR indx IN 1..10000000
9 LOOP
10 FOR indx IN 1..10000000
11 LOOP
12 NULL;
13 END LOOP;
14 END LOOP;
15 END LOOP;
16 RETURN p_in;
17 END;
18 /
Function created.
SQL> SET TIMING ON
SQL> SELECT slow_function(id) FROM func_test;
SLOW_FUNCTION(ID)
-----------------
1
2
1
2
1
2
1
2
1
3
Elapsed: 00:00:00.73
--The advantage of this method is the cached information can be reused by any session and dependencies are managed automatically. If we run the query again we get even better performance because we can used the cached values without calling the function at all.
SQL> SELECT slow_function(id) FROM func_test;
SLOW_FUNCTION(ID)
-----------------
1
2
1
2
1
2
1
2
1
3
Elapsed: 00:00:00.04SQL> CREATE TABLE res_cache_test_tab
2 (
3 id NUMBER,
4 value NUMBER
5 );
Table created.
SQL> BEGIN
2 FOR i IN 1 .. 10
3 LOOP
4 INSERT INTO res_cache_test_tab VALUES (i, i*10);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM res_cache_test_tab;
ID VALUE
---------- ----------
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
10 rows selected.
SQL> CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
2 RETURN NUMBER
3 RESULT_CACHE
4 AS
5 l_value res_cache_test_tab.value%TYPE;
6 BEGIN
7 SELECT value
8 INTO l_value
9 FROM res_cache_test_tab
10 WHERE id = p_in;
11
12 FOR indx IN 1..10000000
13 LOOP
14 FOR indx IN 1..10000000
15 LOOP
16 FOR indx IN 1..10000000
17 LOOP
18 NULL;
19 END LOOP;
20 END LOOP;
21 END LOOP;
22
23 RETURN l_value;
24 END get_value;
25 /
Function created.
SQL> CREATE OR REPLACE PROCEDURE run_test AS
2 l_start NUMBER;
3 l_loops NUMBER := 10;
4 l_value res_cache_test_tab.value%TYPE;
5 BEGIN
6 l_start := DBMS_UTILITY.get_time;
7
8 FOR i IN 1 .. l_loops
9 LOOP
10 l_value := get_value(i);
11 END LOOP;
12
13 DBMS_OUTPUT.PUT_LINE('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
14
15 l_start := DBMS_UTILITY.get_time;
16
17 FOR i IN 1 .. l_loops
18 LOOP
19 l_value := get_value(i);
20 END LOOP;
21
22 DBMS_OUTPUT.PUT_LINE('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
23 END run_test;
24 /
Procedure created.
Run - 1 (Session : 1):-
SQL> EXEC run_test;
First Loop: 133 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.34
Run – 2 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
--If we modify the contents of the table, the cache will be wiped out as shown below :::
SQL> UPDATE res_cache_test_tab SET value = value * 10;
10 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM res_cache_test_tab;
ID VALUE
---------- ----------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
10 rows selected.
Run – 3 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 78 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.78
Run – 4 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
Run – 1 (Session : 2) :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
--The optional RELIES_ON clause is used to specify dependent tables and views so the result cache can be invalidated if the dependent objects are modified.The RELIES ON clause is depricated in 11.2. Post 11.2,it automatically tracks dependencies and invalidates the cached results when necessary.
SQL> CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
2 RETURN NUMBER
3 RESULT_CACHE RELIES_ON (res_cache_test_tab)
4 AS
5 l_value res_cache_test_tab.value%TYPE;
6 BEGIN
7 SELECT value
8 INTO l_value
9 FROM res_cache_test_tab
10 WHERE id = p_in;
11
12 FOR indx IN 1..10000000
13 LOOP
14 FOR indx IN 1..10000000
15 LOOP
16 FOR indx IN 1..10000000
17 LOOP
18 NULL;
19 END LOOP;
20 END LOOP;
21 END LOOP;
22
23 RETURN l_value;
24 END get_value;
25 /
Function created.
Run – 1 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 78 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.77
Run – 2 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> UPDATE res_cache_test_tab SET value = value * 10;
10 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM res_cache_test_tab;
ID VALUE
---------- ----------
1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000
8 8000
9 9000
10 10000
10 rows selected.
Run – 3 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 81 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.81
Run – 4 (Session : 1) :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01DBMS_RESULT_CACHE
--The STATUS function displays the current status of the result cache.
SQL> SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
--------
ENABLED
--The MEMORY_REPORT procedure displays information about memory usage of the result cache.
SQL> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 4160K bytes (4160 blocks)
Maximum Result Size = 208K bytes (208 blocks)
[Memory]
Total Memory = 163936 bytes [0.025% of the Shared Pool]
... Fixed Memory = 10704 bytes [0.002% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... Cache Mgr = 5560 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 153232 bytes [0.023% of the Shared Pool]
....... Overhead = 120464 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 18064 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 21 blocks
........... Used Memory = 11 blocks
............... DepENDencies = 1 blocks (1 count)
............... Results = 10 blocks
................... PLSQL = 10 blocks (10 count)
PL/SQL procedure successfully completed.
--The INVALIDATE procedure invalidates all result-set objects for a specific object, specified using an OWNER and NAME (OBJECT_NAME) or OBJECT_ID from the %_OBJECTS views.
SQL> EXEC DBMS_RESULT_CACHE.invalidate('COURSE', 'GET_VALUE');
PL/SQL procedure successfully completed.
Run – 1 :-
SQL> EXEC run_test;
First Loop: 75 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.75
Run – 2 :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> EXEC DBMS_RESULT_CACHE.invalidate('COURSE', 'RES_CACHE_TEST_TAB');
PL/SQL procedure successfully completed.
Run – 1 :-
SQL> EXEC run_test;
First Loop: 78 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.78
Run – 2 :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
--The INVALIDATE_OBJECT procedure invalidates a specific result-set object in the result cache, specified using an ID or CACHE_ID from the V$RESULT_CACHE_OBJECTS view.
SQL> SELECT id,type,status,cache_id from V$RESULT_CACHE_OBJECTS where id = 11;
ID TYPE STATUS CACHE_ID
---------- ---------- --------- ------------
11 Result Published bygu1wr27uhx55nm5q7za3kwuu
SQL> EXEC DBMS_RESULT_CACHE.invalidate_object(11);
PL/SQL procedure successfully completed.
SQL> SELECT id,type,status,cache_id from V$RESULT_CACHE_OBJECTS where id = 11;
ID TYPE STATUS CACHE_ID
---------- ---------- --------- ------------
11 Result Invalid bygu1wr27uhx55nm5q7za3kwuu
Run – 1 :-
SQL> exec run_test;
First Loop: 8 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
Run – 2 :-
SQL> exec run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
--The BYPASS procedure determines if the result cache is bypassed or not.
SQL> EXEC DBMS_RESULT_CACHE.bypass(true);
PL/SQL procedure successfully completed.
Run – 1 :-
SQL> EXEC run_test;
First Loop: 75 hsecs
Second Loop: 78 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.52
Run – 2 :-
SQL> EXEC run_test;
First Loop: 75 hsecs
Second Loop: 75 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.51
Run – 3 :-
SQL> EXEC run_test;
First Loop: 75 hsecs
Second Loop: 73 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.49
SQL> EXEC DBMS_RESULT_CACHE.bypass(false);
PL/SQL procedure successfully completed.
Run – 1 :-
SQL> EXEC run_test;
First Loop: 76 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.77
Run – 2 :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Run – 3 :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
--The FLUSH procedure and function remove all objects from the result cache and optionally release all memory and clear existing cache statistics.
SQL> EXEC DBMS_RESULT_CACHE.flush;
PL/SQL procedure successfully completed.
Run – 1 :-
SQL> EXEC run_test;
First Loop: 75 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.77
Run – 2 :-
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00SQL> CREATE TABLE qrc_tab
2 (
3 id NUMBER
4 );
Table created.
SQL> INSERT INTO qrc_tab VALUES (1);
1 row created.
SQL> INSERT INTO qrc_tab VALUES (2);
1 row created.
SQL> INSERT INTO qrc_tab VALUES (3);
1 row created.
SQL> INSERT INTO qrc_tab VALUES (4);
1 row created.
SQL> INSERT INTO qrc_tab VALUES (5);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM qrc_tab;
ID
----------
1
2
3
4
5
SQL> CREATE OR REPLACE FUNCTION slow_function(p_id IN qrc_tab.id%TYPE)
2 RETURN qrc_tab.id%TYPE DETERMINISTIC AS
3 BEGIN
4 FOR indx IN 1..10000000
5 LOOP
6 FOR indx IN 1..10000000
7 LOOP
8 FOR indx IN 1..10000000
9 LOOP
10 NULL;
11 END LOOP;
12 END LOOP;
13 END LOOP;
14 RETURN p_id;
15 END;
16 /
Function created.
SQL> SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
Elapsed: 00:00:00.39
SQL> SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
Elapsed: 00:00:00.41
--Adding the RESULT_CACHE hint to the query tells the server to attempt to retrieve the information from the result cache. If the information is not present, it will cache the results of the query provided there is enough room in the result cache. Since we have no cached results, we would expect the first run to take approximately five seconds, but subsequent runs to be much quicker.
SQL> SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
Elapsed: 00:00:00.41
SQL> SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
Elapsed: 00:00:00.00
--The default action of the result cache is controlled by the RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint must be used for a query to access the result cache.
SQL> SHOW PARAMETER RESULT_CACHE_MODE
NAME TYPE VALUE
------------------------------------ ----------- ------
result_cache_mode string MANUAL
--If we set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.
SQL> ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
Session altered.
SQL> SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
Elapsed: 00:00:00.01
SQL> SELECT /*+ no_result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
Elapsed: 00:00:00.38
--The query result cache hint does not work with scalar subquery caching.
SQL> SELECT (SELECT /*+ result_cache */ slow_function(id) FROM dual) AS result FROM qrc_tab;
RESULT
----------
1
2
3
4
5
Elapsed: 00:00:00.42
SQL> SELECT (SELECT /*+ result_cache */ slow_function(id) FROM dual) FROM qrc_tab;
(SELECT/*+RESULT_CACHE*/SLOW_FUNCTION(ID)FROMDUAL)
--------------------------------------------------
1
2
3
4
5
Elapsed: 00:00:00.40How DETERMINISTIC & RESULT_CACHE are similar?
Both DETERMINISTIC and RESULT_CACHE cause caching of data that can improve performance by sidestepping the need to execute the body of a function.
How DETERMINISTIC & RESULT_CACHE are different?
The caching that the DETERMINISTIC keyword leads to has a narrow scope (only your session) and short lifespan (the caching occurs for the duration of the SQL statement that executes the function). So the overall performance impact will likely not be too great.
Data cached by the result cache feature are available to all users of a database instance. And that data remains cached until the cache is invalidated or flushed. It has much greater potential for improving performance of your application – but it also presents more of a danger of having a negative impact as well.
When to use DETERMINISTIC?
You should add the DETERMINISTIC keyword to your function header whenever it actually is deterministic. By doing so, you give the PL/SQL and SQL engines more information that they can use to optimize performance and make the features available (such as a function-based index).
Even if it does not help now, it might in the future, and it can do no harm to put it there.But you should never use that keyword with a function that is not truly deterministic. Sometimes this kind of “lying” will be caught by Oracle and rejected, sometimes it could cause problems in your application.
When to use RESULT_CACHE?
This is trickier to answer. Adding the RESULT_CACHE keyword to your function has a ripple effect through the entire database instance and overall application performance.
You need to work closely with your DBA to ensure that your dev, test and production instances are configured properly (the SGA memory area for all the result caches needs to be properly sized; you need to do everything you can to avoid latch contention).
And you need to very carefully select which functions should have the RESULT_CACHE keyword added to them. Some basic criteria include :-
• It is invoked with the same argument values repeatedly?
• If the function relies on a table, is the data in the table or view static (example: materialized view)? That’s a good candidate.
• If the function relies on a table, is the data queried much more frequently than it is updated? Also a good possibility then. Remember: the cache will be invalidated when changes to a dependent table are committed.
• Does the function have any session-specific dependencies, such as reliance on NLS parameter settings? If so, then the cached values might not match the session-dependent values. A good example is using TO_CHAR in your function without a format mask. VPD and reliance on system context values is another. Generally the solution for this is to move all such dependencies to the parameter list.
Conclusion
Any DETERMINISTIC function is a good candidate for the RESULT_CACHE keyword, but not every RESULT_CACHE function is DETERMINISTIC.
https://waterfallmagazine.com/
Hi, i think that i saw you visited my weblog thus i came to “return the favor”.I am trying to find things to enhance my
site!I suppose its ok to use a few of your ideas!!