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.