Oracle has introduced three new caching features in its Oracle 11g release:

  • Query result cache
  • Client OCI result cache
  • PL/SQL function result cache

In this posting, I will present the query result cache feature.

In addition to existing caches, the query result cache feature keeps query results in the shared pool. By caching a query result, Oracle can directly return the cached result set and avoid not only physical reads if needed but also data computing (grouping or sorting for example). Result cache is also shared between all sessions, so when a query result is in the cache, all users can use the cached result set instead of consuming server resources.

Configuration

Just take a look on how result cache can be configured. There are 6 parameters to configure result caching feature.

SQL> show parameters 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 4M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

The client_result_cache_lag and client_result_cache_size parameters will not be discussed here as they are concerning client result cache feature.

Two parameters are dynamic system parameters:

  • result_cache_max_size: configure the maximum amount of memory the database instance can use for result cache feature. If AMM (Automatic Memory Management) is enabled, the size is derived from the values of SHARED_POOL_SIZE, SGA_TARGET and MEMORY_TARGET. Note that if result_cache_max_size is set to 0, the result cache is disabled.
  • result_cache_max_result: percentage of result_cache_max_size that any single result set can use. For example, on my test environment, if a result set is bigger than 204K, it will not be cached (4096K * 0.05 = 204.8K).

The last two parameters can be set at session or system level:

  • result_cache_mode: set how database keep results, two values are possible.
    MANUAL: only results of queries with the hint RESULT_CACHE are cached if the result set matches the memory condition
    FORCE: all results that could be cached are put in result cache unless NO_RESULT_CACHE hint is present in the query
  • result_cache_remote_expiration: set an expiration time for result set on distant objects. When this parameter is set to 0, result cache for distant objects is disabled.
    Setting this parameter allows to keep in the cache results which can be stale because Oracle cannot invalidate the cache if the remote table is updated in remote database instance.

Cache management

Oracle maintains 4 dynamic views to check result cache usage and statistics:

  • V$RESULT_CACHE_OBJECTS: cached objects and attributes
  • V$RESULT_CACHE_DEPENDENCY: dependency between cached objects
  • V$RESULT_CACHE_MEMORY: result cache memory usage
  • V$RESULT_CACHE_STATISTICS: memory usage and statistics
SQL> select * from V$RESULT_CACHE_STATISTICS;
 ID        NAME                                     VALUE
---------- ---------------------------------------- ----------------------------------------
         1 Block Size (Bytes)                       1024
         2 Block Count Maximum                      4096
         3 Block Count Current                      0
         4 Result Size Maximum (Blocks)             204
         5 Create Count Success                     0
         6 Create Count Failure                     0
         7 Find Count                               0
         8 Invalidation Count                       0
         9 Delete Count Invalid                     0
        10 Delete Count Valid                       0
        11 Hash Chain Length                        0
        12 Find Copy Count                          0
12 rows selected.

Result cache uses its own block size to stock result sets which is different from database block size.
To manage result cache feature through PL/SQL, there is a package called DBMS_RESULT_CACHE. This package contains procedures and functions to check memory usage, flush the cache, etc. For example, the procedure memory_report prints a report on the feature memory usage.

SQL> set serveroutput on
SQL> exec DBMS_RESULT_CACHE.Memory_Report();
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  = 4M bytes (4K blocks)
Maximum Result Size = 204K bytes (204 blocks)
[Memory]
Total Memory = 5180 bytes [0.004% of the Shared Pool]
... Fixed Memory = 5180 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.

Basic result cache tests

All tests will be run with Oracle sample schema SH. First, I try to execute twice the same statement without result cache (which is the default).

 

SQL> set lines 150
SQL> set timing on
SQL> set autotrace traceonly explain
SQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;
620 rows selected.
Elapsed: 00:00:00.31
SQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;
620 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   620 |  6200 |   407   (1)| 00:00:05 |
|   1 |  HASH GROUP BY     |           |   620 |  6200 |   407   (1)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 55500 |   541K|   405   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Here, we see the effect of the existing caches, the second execution taking less time. As the two statements share the same execution plan, I have copied it only once.

Now, result cache is enabled for this session and the same statement is executed two times again.

SQL> ALTER SESSION SET result_cache_mode = FORCE;
Session altered.
SQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;
620 rows selected.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |   620 |  6200 |   407   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | dg3tccfff51dj1n3932f7wjypt |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |   620 |  6200 |   407   (1)| 00:00:05 |
|   3 |    TABLE ACCESS FULL| CUSTOMERS                  | 55500 |   541K|   405   (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SH.CUSTOMERS); parameters=(nls); name="SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY"

SQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;
620 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |   620 |  6200 |   407   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | dg3tccfff51dj1n3932f7wjypt |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |   620 |  6200 |   407   (1)| 00:00:05 |
|   3 |    TABLE ACCESS FULL| CUSTOMERS                  | 55500 |   541K|   405   (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SH.CUSTOMERS); parameters=(nls); name="SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY"

A new operation named “RESULT CACHE” has been added in the execution plan. The two statements share the same name for result cache operation. The first execution is a little more longer than when cache is disabled but the second is immediate. The explanation seems simple, during the first execution Oracle puts the result in cache and for the second execution, the only operation is to read cache.
To check cache allocation and verify the hypothesis, I check into some result cache views under SYS account.

SQL> SELECT id, name, cache_id, cache_key, type, status FROM v$result_cache_objects;
 ID NAME                                     CACHE_ID                       CACHE_KEY                      TYPE       STATUS
--- ---------------------------------------- ------------------------------ ------------------------------ ---------- ---------
  0 SH.CUSTOMERS                             SH.CUSTOMERS                   SH.CUSTOMERS                   Dependency Published
  1 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt     9mxfx9vgpfzacd7c143887rgg1     Result     Published
    S GROUP BY CUST_CITY
2 objects have been created, a dependency object and a result object and both are published. The result object cache id corresponds to the result cache operation name in execution plan.

SQL> select * from V$RESULT_CACHE_STATISTICS;
 ID         NAME                                     VALUE
---------- ---------------------------------------- ----------
         1 Block Size (Bytes)                       1024
         2 Block Count Maximum                      4096
         3 Block Count Current                      32
         4 Result Size Maximum (Blocks)             204
         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 Find Copy Count                          0

“Create Count Success” statistic shows that the first query creates the result object in cache and “Find Count” shows that the second execution has found and used the result object.

To check how cache reacts when data is changed, I have updated the table customers.

-- SH session
SQL> UPDATE CUSTOMERS SET cust_first_name = cust_first_name where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
-- SYS session
SQL> SELECT id, name, cache_id, cache_key, type, status FROM v$result_cache_objects;
 ID NAME                                     CACHE_ID                       CACHE_KEY                      TYPE       STATUS
--- ---------------------------------------- ------------------------------ ------------------------------ ---------- ---------
  0 SH.CUSTOMERS                             SH.CUSTOMERS                   SH.CUSTOMERS                   Dependency Published
  1 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt     9mxfx9vgpfzacd7c143887rgg1     Result     Invalid
    S GROUP BY CUST_CITY

Result is not deleted from cache, result object became invalid and dependency object is still published. But a counter allows checking the number of invalidations that occurred on a dependency object. In fact, the next time the statement will be executed, the cache will be updated.

I suppose the result set is not deleted because of its location in the shared pool. According to Oracle documentation, in general, any item in the shared pool remains there until it is flushed according to a modified LRU algorithm (see Memory Architecture).

-- The statement 'SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY' has been executed several times again in two different SH session with result cache
-- SYS
SQL> SELECT id, name, cache_id, cache_key, type, status, invalidations FROM v$result_cache_objects;
 ID NAME                                     CACHE_ID                       CACHE_KEY                      TYPE       STATUS    INVALIDATIONS
--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------
  0 SH.CUSTOMERS                             SH.CUSTOMERS                   SH.CUSTOMERS                   Dependency Published             1
 10 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt     9mxfx9vgpfzacd7c143887rgg1     Result     Published             0
    S GROUP BY CUST_CITY
  1 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt     9mxfx9vgpfzacd7c143887rgg1     Result     Invalid               0
    S GROUP BY CUST_CITY
SQL> select * from V$RESULT_CACHE_STATISTICS;
 ID NAME                                     VALUE
--- ---------------------------------------- ----------
  1 Block Size (Bytes)                       1024
  2 Block Count Maximum                      4096
  3 Block Count Current                      32
  4 Result Size Maximum (Blocks)             204
  5 Create Count Success                     2
  6 Create Count Failure                     0
  7 Find Count                               14
  8 Invalidation Count                       1
  9 Delete Count Invalid                     0
 10 Delete Count Valid                       0
 11 Hash Chain Length                        1
 12 Find Copy Count                          0

A new updated result object has been created, a new object is present in v$result_cache_objects and “Create Count Success” is increased. Result cache memory can be consumed by invalid results which always need to be refreshed if the table is frequently updated. To check if the cache is efficient, check the ratio between “Create Count Success” and “Find Count” and the value of “Invalidation Count”.

Result cache with views

Now, I would like to see result cache behaviour with views. I have created a view with the same statement whose result is already in the cache.

-- SH
SQL> CREATE VIEW customer_cities AS SELECT cust_city, count(*) as total_customers FROM sh.customers GROUP BY cust_city;
View created.
SQL> select * from customer_cities;
620 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |   620 |  6200 |   407   (1)| 00:00:05 |
|   1 |  RESULT CACHE       | 3ysncryjpt7d11yrf9vs04ctmd |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |   620 |  6200 |   407   (1)| 00:00:05 |
|   3 |    TABLE ACCESS FULL| CUSTOMERS                  | 55500 |   541K|   405   (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------
-- SYS
SQL> SELECT id, name, cache_id, cache_key, type, status, invalidations FROM v$result_cache_objects;
 ID NAME                                     CACHE_ID                       CACHE_KEY                      TYPE       STATUS    INVALIDATIONS
--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------
 28 SH.CUSTOMER_CITIES                       SH.CUSTOMER_CITIES             SH.CUSTOMER_CITIES             Dependency Published             0
 29 select * from customer_cities            3ysncryjpt7d11yrf9vs04ctmd     3yjy9v421rggpfdpw9b6tvwjky     Result     Published             0

Oracle did not use the existing result cache although the query is the same. A new result cache and a new dependency object on the view have been created. The new dependency object is the view itself. Is Oracle capable of invalidating the cache if the table customers change?

SQL> update sh.customers set cust_gender=upper(cust_gender) where rownum <=1;1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT id, name, cache_id, cache_key, type, status, invalidations FROM v$result_cache_objects;
 ID NAME                                     CACHE_ID                       CACHE_KEY                      TYPE       STATUS    INVALIDATIONS
--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------
 28 SH.CUSTOMER_CITIES                       SH.CUSTOMER_CITIES             SH.CUSTOMER_CITIES             Dependency Published             0
 29 select * from customer_cities            3ysncryjpt7d11yrf9vs04ctmd     3yjy9v421rggpfdpw9b6tvwjky     Result     Invalid               0

The result set is now invalid. Oracle associated this result cache with dependency object SH.CUSTOMERS already present in the cache.

PL/SQL and binding variable

To test result cache behaviour within PL/SQL, I have created a function that returns the full name of a customer passing the customer id. Then I have simulated a program which needs to print out the full name of many customers by looping on this function and I have checked result cache usage.

-- SH session
CREATE OR REPLACE FUNCTION getCustomerName(customer_id number)
RETURN varchar2
AS
first_name customers.cust_first_name%TYPE;
last_name customers.cust_last_name%TYPE;
BEGIN
SELECT cust_first_name, cust_last_name into first_name, last_name FROM customers where cust_id = customer_id;
RETURN first_name || ', ' || last_name;
END;
SQL> DECLARE  2  cust_full_name varchar2(80);
  3  BEGIN
  4  FOR x IN 1500 .. 1999 LOOP
  5  cust_full_name:=getCustomerName(x);
  6  END LOOP;
  7  END;
  8  /
-- SYS
ID  NAME                                      CACHE_ID                       CACHE_KEY                      TYPE       STATUS    INVALIDATIONS
--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------
 xx SELECT CUST_FIRST_NAME, CUST_LAST_NAME F xxxxxxxxxxxxxxxxxxxxxxxxxx     xxxxxxxxxxxxxxxxxxxxxxxxxx     Result     Published             0
    ROM CUSTOMERS WHERE CUST_ID = :B1

We notice two things, PL/SQL access to the result cache and bind variable has been recognized. The parameter has been replaced in the SQL text but as each result had a different result, logically result cache created a result set for each value of the bind value.
It seems that in this configuration, result cache can be efficient only if the statement will be re-executed with the same value for the bind variable because the first execution just creates the result object. As the result cache memory is limited, a single execution can replace old result object by new object that may be not used later.

Conclusion

Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter’s table) or on complex statement consumming time in computing the same data.

But if the “Created Count Success” is much larger than “Find count”, result cache could not be very efficient, i.e. created objects are not used before being replaced by new objects.

So result cache can be a useful feature, but we need to pay attention on memory consumption and also on which statement offers the best benefit.


Thumbnail [60x60]
by
Nicolas Jardot