SQL result cache

 

Preamble

Finally they made it ! Available in MySQL since release 4.0.1 it is now possible in Oracle 11g (available since 11gR1).

The concept is really simple, you can find it in Oracle official documentation: server result cache is a memory pool within the shared pool. This pool contains a SQL query result cache, which stores results of SQL queries, and a PL/SQL function result cache, which stores values returned by PL/SQL functions.

So it’s all about caching result of SQL queries and PL/SQL programs to avoid re-doing them and just throw the result from memory to client…

Result cache is activated by default (have you noticed it ? Surely not and we will see why…) but in manual mode, the following parameters let you control result cache:

SQL> show parameter result
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big INTEGER 3000
client_result_cache_size             big INTEGER 0
result_cache_max_result              INTEGER     5
result_cache_max_size                big INTEGER 10M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       INTEGER     0
  • client_result_cache_size: size of the result cache for OCI like applications.
  • client_result_cache_lag: ping-pong delay time (in milliseconds) that the OCI application will wait to check for any database changes.
  • result_cache_max_size: size of the result cache.
  • result_cache_max_result: percentage for the maximum size of a cached result.
  • result_cache_mode: result cache mode. In MANUAL you have to use the result_cache hint to cache the result. In FORCE method everything is cached unless you use no_result_cache hint.
  • result_cache_remote_expiration: maximum number of minutes a result cache using a remote objects can be valid.

Oracle made available the following package:

DBMS_RESULT_CACHE
BYPASS Sets the bypass mode for the Result Cache
FLUSH Attempts to remove all the objects from the Result Cache, and depending on the arguments retains or releases the memory and retains or clears the statistics
INVALIDATE Invalidates all the result-set objects that dependent upon the specified dependency object
INVALIDATE_OBJECT Invalidates the specified result-set object(s)
MEMORY_REPORT Produces the memory usage report for the Result Cache
STATUS Checks the status of the Result Cache

You have the following V$ views:

  • V$RESULT_CACHE_DEPENDENCY
  • V$RESULT_CACHE_MEMORY
  • V$RESULT_CACHE_OBJECTS
  • V$RESULT_CACHE_STATISTICS

All test below will be done on 11gR2 (11.2.0.1.0) running on a RedHat 5.2 server with the following test table:

DROP TABLE test1;
 
CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users;
 
DECLARE
i NUMBER;
nbrows NUMBER;
BEGIN
  i:=1;
  nbrows:=50000;
  LOOP
    EXIT WHEN i>nbrows;
    IF (i=1) THEN
      INSERT INTO test1 VALUES(1,RPAD('A',49,'A'));
    ELSE
      INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A'));
    END IF;
    i:=i+1;
  END LOOP;
  COMMIT;
END;
/
 
CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users;
 
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);

SQL query result cache

Manual mode

Let’s configure first the database:

SQL> ALTER SYSTEM SET result_cache_max_size=50m;
 
SYSTEM altered.
 
SQL> EXEC DBMS_RESULT_CACHE.flush();
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 130
SQL> SET autotrace traceonly EXPLAIN STATISTICS;

Execute your first query:

SQL> SELECT /* Yannick */ /*+ result_cache */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1495873664
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   1 |  RESULT CACHE       | g8127tpqarpzn701dhcjh56m71 |       |       |            |          |
|   2 |   SORT GROUP BY     |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| TEST1                      | 50000 |   146K|   136   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------
 
Result Cache Information (IDENTIFIED BY operation id):
------------------------------------------------------
 
   1 - column-COUNT=2; dependencies=(YJAQUIER.TEST1); name="SELECT /* Yannick */ /*+ result_cache */ id,count(*) from test1 group
by id order by id"
 
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        442  consistent gets
        439  physical reads
          0  redo SIZE
        524  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          1  sorts (memory)
          0  sorts (disk)
          2  ROWS processed

Let’s see what happened at Oracle level:

SQL> SELECT * FROM V$RESULT_CACHE_DEPENDENCY;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         1          0     105444
 
SQL> col object_name FOR a30
SQL> SELECT owner,object_name FROM dba_objects WHERE object_id='105444';
 
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
YJAQUIER                       TEST1
 
SQL> col SQL_TEXT FOR a90
SQL> col name FOR a90
SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------------------------------------
db6ftfwqcxj9r            0 SELECT /* Yannick */ /*+ result_cache */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id
 
SQL> SELECT id,TYPE,name,row_count,block_count,namespace FROM V$RESULT_CACHE_OBJECTS WHERE cache_id='g8127tpqarpzn701dhcjh56m71';
 
        ID TYPE       NAME                                                                                        ROW_COUNT
---------- ---------- ------------------------------------------------------------------------------------------ ----------
BLOCK_COUNT NAMES
----------- -----
         1 Result     SELECT /* Yannick */ /*+ result_cache */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id             2
          1 SQL
 
SQL> SELECT TYPE,status,name,block_count FROM V$RESULT_CACHE_OBJECTS WHERE object_no='105444';
 
TYPE       STATUS    NAME                                                                                       BLOCK_COUNT
---------- --------- ------------------------------------------------------------------------------------------ -----------
Dependency Published YJAQUIER.TEST1                                                                                       1
 
SQL> SET pages 50
SQL> col STATISTICS FOR a80
SQL> SELECT RPAD(id,4) || RPAD(name,30) || ' ' || VALUE AS STATISTICS FROM V$RESULT_CACHE_STATISTICS ORDER BY id;
 
STATISTICS
--------------------------------------------------------------------------------
1   Block SIZE (Bytes)             1024
2   Block COUNT Maximum            51200
3   Block COUNT CURRENT            32
4   Result SIZE Maximum (Blocks)   2560
5   CREATE COUNT Success           1
6   CREATE COUNT Failure           0
7   Find COUNT                     1
8   Invalidation COUNT             0
9   DELETE COUNT Invalid           0
10  DELETE COUNT Valid             0
11  Hash Chain LENGTH              1
12  Global Hit COUNT               0
13  Global Miss COUNT              0
 
13 ROWS selected.

Let’s execute the same query a second time to see what happens:

SQL> SELECT /* Yannick */ /*+ result_cache */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1495873664
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   1 |  RESULT CACHE       | g8127tpqarpzn701dhcjh56m71 |       |       |            |          |
|   2 |   SORT GROUP BY     |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| TEST1                      | 50000 |   146K|   136   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------
 
Result Cache Information (IDENTIFIED BY operation id):
------------------------------------------------------
 
   1 - column-COUNT=2; dependencies=(YJAQUIER.TEST1); name="SELECT /* Yannick */ /*+ result_cache */ id,count(*) from test1 group
by id order by id"
 
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo SIZE
        524  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          2  ROWS processed

As we can see, no more I/O (even logical) and no more sort (even in memory):


          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)

Force mode

Now let’s try with the force mode:

SQL> ALTER SYSTEM SET result_cache_mode=force;
 
SYSTEM altered.
 
SQL> SELECT /* Yannick */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1495873664
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   1 |  RESULT CACHE       | g8127tpqarpzn701dhcjh56m71 |       |       |            |          |
|   2 |   SORT GROUP BY     |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| TEST1                      | 50000 |   146K|   136   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------
 
Result Cache Information (IDENTIFIED BY operation id):
------------------------------------------------------
 
   1 - column-COUNT=2; dependencies=(YJAQUIER.TEST1); name="SELECT /* Yannick */ id,count(*) from test1 group by id order by id"
 
 
STATISTICS
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo SIZE
        524  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          2  ROWS processed

So same result, same plan (!!) but a different parent cursor. So Oracle is able to use the cached result, to use the same plan but do creates a new parent cursor:

SQL> col sql_text FOR a80 tru
SQL> SELECT sql_id,child_number,plan_hash_value,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ------------ --------------- --------------------------------------------------------------------------------
9r2jpnvh7j2ny            0      1495873664 SELECT /* Yannick */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id
db6ftfwqcxj9r            0      1495873664 SELECT /* Yannick */ /*+ result_cache */ id,COUNT(*) FROM test1 GROUP BY id orde

Remark:
If you know fetch the V$RESULT_CACHE_OBJECTS table you will see lots of queries/objects as in FORCE mode even the dictionary queries are cached !!

Status can also be seen with (65 results cached, 25 invalid anyway):

SQL> EXEC DBMS_RESULT_CACHE.MEMORY_REPORT(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  = 50M bytes (50K blocks)
Maximum Result SIZE = 2560K bytes (2560 blocks)
[Memory]
Total Memory = 170256 bytes [0.070% OF the Shared Pool]
... Fixed Memory = 13548 bytes [0.006% OF the Shared Pool]
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
.......  = 4108 bytes
....... Cache Mgr  = 4416 bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 156708 bytes [0.064% OF the Shared Pool]
....... Overhead = 58404 bytes
........... Hash TABLE    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 1060 bytes
....... Cache Memory = 96K bytes (96 blocks)
........... Unused Memory = 5 blocks
........... Used Memory = 91 blocks
............... Dependencies = 26 blocks (26 COUNT)
............... Results = 65 blocks
................... SQL     = 45 blocks (45 COUNT)
................... Invalid = 20 blocks (20 COUNT)
 
PL/SQL PROCEDURE successfully completed.

So why not activating FORCE mode by default you may think ? Well, it strongly depends of your application code… Let’s see it with a stupid example…

SQL> DECLARE
  i NUMBER;
  j NUMBER;
BEGIN
  FOR i IN 1..50000 LOOP
    SELECT COUNT(*) INTO j
    FROM yjaquier.test1
    WHERE id = i;
  END LOOP;
END;
/
 
PL/SQL PROCEDURE successfully completed.

Then if you check or display result cache statistics you will see that result cache has been more or less flushed out by a single query i.e.:

SELECT COUNT(*) FROM YJAQUIER.TEST1 WHERE ID = :B1
SQL> SELECT COUNT(*) FROM V$RESULT_CACHE_OBJECTS WHERE TYPE='Result';
 
  COUNT(*)
----------
     50132
 
SQL> SELECT RPAD(id,4) || RPAD(name,30) || ' ' || VALUE AS STATISTICS FROM V$RESULT_CACHE_STATISTICS ORDER BY id;
 
 
STATISTICS
--------------------------------------------------------------------------------
1   Block SIZE (Bytes)             1024
2   Block COUNT Maximum            51200
3   Block COUNT CURRENT            50400
4   Result SIZE Maximum (Blocks)   2560
5   CREATE COUNT Success           50310
6   CREATE COUNT Failure           0
7   Find COUNT                     421
8   Invalidation COUNT             148
9   DELETE COUNT Invalid           184
10  DELETE COUNT Valid             0
11  Hash Chain LENGTH              8-15
12  Global Hit COUNT               83
13  Global Miss COUNT              25
 
13 ROWS selected.

That’s why it’s not default value and not Oracle recommended setting. In this particular example it would create unnecessary overhead and so performance issue…Oracle Database recommends that applications cache results for queries of read-only or read-mostly database objects.

Table annotations

You can also flag tables as candidate for result caching and if you issue a query where all tables are candidate for caching then the query will be cached:

SQL> ALTER SYSTEM SET result_cache_mode=manual;
 
SYSTEM altered.
 
SQL> EXEC DBMS_RESULT_CACHE.flush();
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1495873664
 
----------------------------------------------------------------------------
| Id  | Operation          | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     2 |     6 |   138   (2)| 00:00:02 |
|   1 |  SORT GROUP BY     |       |     2 |     6 |   138   (2)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| TEST1 | 50000 |   146K|   136   (0)| 00:00:02 |
----------------------------------------------------------------------------
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        442  consistent gets
          0  physical reads
          0  redo SIZE
        524  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          1  sorts (memory)
          0  sorts (disk)
          2  ROWS processed

Now if you alter the table:

SQL> ALTER TABLE test1 result_cache(MODE force);
 
TABLE altered.
 
SQL> SELECT /* Yannick */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1495873664
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   1 |  RESULT CACHE       | g8127tpqarpzn701dhcjh56m71 |       |       |            |          |
|   2 |   SORT GROUP BY     |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| TEST1                      | 50000 |   146K|   136   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------
 
Result Cache Information (IDENTIFIED BY operation id):
------------------------------------------------------
 
   1 - column-COUNT=2; dependencies=(YJAQUIER.TEST1); name="SELECT /* Yannick */ id,count(*) from test1 group by id order by id"
 
 
STATISTICS
----------------------------------------------------------
        183  recursive calls
          0  db block gets
        466  consistent gets
          0  physical reads
          0  redo SIZE
        524  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          7  sorts (memory)
          0  sorts (disk)
          2  ROWS processed
 
SQL> SELECT /* Yannick */ id,COUNT(*) FROM test1 GROUP BY id ORDER BY id;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1495873664
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   1 |  RESULT CACHE       | g8127tpqarpzn701dhcjh56m71 |       |       |            |          |
|   2 |   SORT GROUP BY     |                            |     2 |     6 |   138   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| TEST1                      | 50000 |   146K|   136   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------
 
Result Cache Information (IDENTIFIED BY operation id):
------------------------------------------------------
 
   1 - column-COUNT=2; dependencies=(YJAQUIER.TEST1); name="SELECT /* Yannick */ id,count(*) from test1 group by id order by id"
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo SIZE
        524  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          2  ROWS processed

PL/SQL function result cache

Preparation:

SQL> ALTER SYSTEM SET result_cache_mode=manual;
 
SYSTEM altered.
 
SQL> EXEC DBMS_RESULT_CACHE.flush();
 
PL/SQL PROCEDURE successfully completed.
 
SQL> CREATE OR REPLACE FUNCTION count_test1(n NUMBER)
RETURN NUMBER
RESULT_CACHE
IS
  i NUMBER;
BEGIN
  SELECT COUNT(*) INTO i
  FROM yjaquier.test1
  WHERE id = n;
  RETURN i;
END;
/
 
FUNCTION created.

Testing to see if function is cached:

SQL> SET autotrace ON EXPLAIN STATISTICS;
SQL> SELECT count_test1(50000) FROM dual;
 
COUNT_TEST1(50000)
------------------
             49999
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | ROWS  | COST (%CPU)| TIME     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
 
STATISTICS
----------------------------------------------------------
         20  recursive calls
          0  db block gets
        124  consistent gets
          0  physical reads
          0  redo SIZE
        434  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> SELECT count_test1(50000) FROM dual;
 
COUNT_TEST1(50000)
------------------
             49999
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | ROWS  | COST (%CPU)| TIME     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo SIZE
        434  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

If you check in result cache tables:

 
SQL> SELECT id,TYPE,name,row_count,block_count,namespace FROM V$RESULT_CACHE_OBJECTS WHERE TYPE='Result';
 
        ID TYPE       NAME                                                                                        ROW_COUNT
---------- ---------- ------------------------------------------------------------------------------------------ ----------
BLOCK_COUNT NAMES
----------- -----
         1 Result     "YJAQUIER"."COUNT_TEST1"::8."COUNT_TEST1"#fac892c7867b54c6 #1                                       1
          1 PLSQL

Now let’s update the table and see how it behaves:

SQL> UPDATE yjaquier.test1 SET id=25000 WHERE id=50000;
 
49999 ROWS updated.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT count_test1(50000) FROM dual;
 
COUNT_TEST1(50000)
------------------
                 0
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | ROWS  | COST (%CPU)| TIME     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
 
STATISTICS
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        100  consistent gets
          0  physical reads
          0  redo SIZE
        431  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> SELECT count_test1(50000) FROM dual;
 
COUNT_TEST1(50000)
------------------
                 0
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | ROWS  | COST (%CPU)| TIME     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo SIZE
        431  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

Fortunately no surprise… Well, in 11gR2 only, 11gR1 has a “bug” and you must have specified in source code of the function:

RESULT_CACHE RELIES_ON (test1)

To have a good result. RELIES_ON has been deprecated in 11gR2…

Client result cache

The Oracle Call Interface (OCI) client result cache is same concepts than SQL result cache so with same pros/cons. The cache is now at OCI client level and is controlled by client_result_cache_lag and client_result_cache_size Oracle initialization parameters.

OCI drivers such as OCCI, the JDBC OCI driver, and ODP.NET support client result caching.

A bit more complex to test but what is strange at first feeling is that you define Oracle initialization parameters that will instruct OCI client to set up a cache…

References

  • 11g New Feature : SQL Query Result Cache [ID 453567.1]
  • 11g New Feature PL/SQL Function Result Cache [ID 430887.1]
  • Processes Terminate With ORA-7445 [__intel_new_memcpy()] Errors After Upgrade to 11.2 [ID 1142314.1]
  • 11gR2 NEW FEATURE OCI Client Result Cache – Table annotations support [ID 864736.1]
  • query result cache in oracle 11g
  • pl/sql function result cache in 11g

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>