Using Result Cache in Oracle 11g Database.

From Oracle 11g we can use result cache in function and query, these results is a faster response time. The cached results stored become invalid when data in the dependent database objects is changed. Result cache is instance specific. The Result Cache Memory pool consists of the SQL Query Result Cache and PL/SQL Function Result Cache, which stores the values returned by PL/SQL functions.

The RESULT_CACHE_MODE parameter determines the SQL query result cache behavior. This parameter contain MANUAL and FORCE. If you set manual you need to specify result_cache hint in your query. If set FORCE  all results use the cache, you can use no_result_cache hint to bypass the cache.

SQL> SET TIMING ON;
SQL> CREATE OR REPLACE FUNCTION RESULT_CASHE_TEST
  2  RETURN NUMBER
  3  RESULT_CACHE
  4  IS
  5  V_RETVALUE NUMBER:=0;
  6  BEGIN
  7
  8  FOR I IN 1 .. 5 LOOP
  9  DBMS_LOCK.sleep(1);
 10  V_RETVALUE:=V_RETVALUE*I;
 11  END LOOP;
 12
 13  RETURN V_RETVALUE;
 14  END ;
 15  /
Function created.
Elapsed: 00:00:00.01
SQL> SELECT RESULT_CASHE_TEST FROM DUAL;
RESULT_CASHE_TEST
-----------------
                0
Elapsed: 00:00:05.02
SQL> SELECT RESULT_CASHE_TEST FROM DUAL;
RESULT_CASHE_TEST
-----------------
                0
Elapsed: 00:00:00.00
SQL>
SQL> CREATE TABLE RESULT_CACHE( ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);
Table created.
Elapsed: 00:00:00.03
SQL> INSERT INTO RESULT_CACHE VALUES(10,'RAJIB.PRADHAN',5000);
1 row created.
Elapsed: 00:00:00.00
SQL> SELECT /*+ result_cache */  SALARY
  2  FROM RESULT_CACHE
  3  WHERE ID=10;
    SALARY
----------
      5000
Elapsed: 00:00:00.00
SQL>