Behavior Of DETERMINISTIC & RESULT_CACHE Function In Oracle 19c

deterministic_result_cache

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.10

RESULT_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.04
SQL> 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.01

DBMS_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.00
SQL> 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.40

How 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.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

4 thoughts on “Understanding INVISIBLE Index”

  1. Keep going to write so useful articles and also good that you have a lot of people who visits your website I can say it basede on the reviews you have here.

Leave a Reply

Your email address will not be published. Required fields are marked *