Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Result Cache is the memory space in the shared pool area within the Shared Global Area (SGA). In this memory area, the database query result or the query block is stored for reuse. This memory space is shared by cached blocks, sql statements, and non-stale sessions.
The use of the Result Cache is ideal for tables that are small in size and require frequent access. Using for large tables may cause a problem. However, it will provide very serious gains in small tables which are constantly accessed.
The Result Cache came with the Oracle Database 11g Release 1 version and is enabled by default.
We can learn the Result Cache status with the following query.
1 2 3 4 5 | SQL> SELECT dbms_result_cache.status() FROM dual; DBMS_RESULT_CACHE.STATUS() -------------------------------------------------------------------------------- ENABLED |
It is useful to set two important parameters related to the result cache. To avoid a bottleneck, you must assign a value as follows to result_cache_max_size and shared_pool_size. You can set these parameters according to your memory or according to your needs.
1 2 | SQL> ALTER SYSTEM SET result_cache_max_size = 2G SCOPE = SPFILE; SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE = SPFILE; |
You will then need to restart the database.
To use the Result Cache, you need to add /*+ RESULT_CACHE */ hint to the query. The sample query is as follows.
1 2 3 4 5 6 7 | SQL> SELECT /*+ RESULT_CACHE */ p.prod_name, SUM (s.amount_sold) AS total_revenue, SUM (s.quantity_sold) AS total_sales FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name; |
The use of result cahce is manually managed by default. If we wish, we can also force it to be used automatically. This is possible according to the value of the result_cache_mode parameter.
The value of this parameter is MANUAL by default. We can force the system to use result cache by setting this parameter as FORCE. The usage is limited by the result_cache_max_size parameter.
We can change the result_cache_mode parameter as follows:
1 2 3 | SQL> alter system set result_cache_mode=FORCE scope=both sid='*'; System altered. |
Even if the result_cache_mode parameter is set to FORCE, the result cache will not be used if NO_RESULT_CACHE query hint is specified in the query. Query Hint is always superior from the parameter.
Querying cached objects :
With the following query, you can find the objects used result cache.
1 2 3 4 5 6 | SQL> SELECT name, TYPE, cache_id, row_count FROM v$result_cache_objects ORDER BY creation_timestamp; |
The result cache may need to be flushed in some cases. You can clear existing cache as follows.
1 2 3 4 5 6 | SQL> BEGIN DBMS_RESULT_CACHE.FLUSH; END; / PL/SQL procedure successfully completed. |
Memory status must also be monitored when using the Result Cache. You can mointor it with the Result Cache Memory Report. You can create this report as follows. With this report, we can see the memory usage and configurations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SQL> set serveroutput on 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 = 52448K bytes (52448 blocks) Maximum Result Size = 2622K bytes (2622 blocks) [Memory] Total Memory = 174336 bytes [0.004% of the Shared Pool] ... Fixed Memory = 14880 bytes [0.000% of the Shared Pool] ....... Memory Mgr = 200 bytes ....... Cache Mgr = 208 bytes ....... Bloom Fltr = 2K bytes ....... = 4120 bytes ....... RAC Cbk = 5408 bytes ....... State Objs = 2896 bytes ... Dynamic Memory = 159456 bytes [0.004% of the Shared Pool] ....... Overhead = 126688 bytes ........... Hash Table = 64K bytes (4K buckets) ........... Chunk Ptrs = 24K bytes (3K slots) ........... Chunk Maps = 12K bytes ........... Miscellaneous = 126688 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 31 blocks ........... Used Memory = 1 blocks ............... Dependencies = 0 blocks (0 count) ............... Results = 1 blocks ................... SQL = 1 blocks (1 count) PL/SQL procedure successfully completed. |
“v$result_cache_statistics” is another view where you can get information about the Result Cache.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> select name, value from v$result_cache_statistics; NAME VALUE ------------------------------ -------------------- Block Size (Bytes) 1024 Block Count Maximum 3072000 <<<<<<<<<<< 3 GB Result Cache maximum size Block Count Current 1038720 <<<<<<<<<<< 1 GB is currently used Result Size Maximum (Blocks) 153600 Create Count Success 1047967 Create Count Failure 80 Find Count 1312482070 Invalidation Count 9056 Delete Count Invalid 9402 Delete Count Valid 0 Hash Chain Length 237-269 Find Copy Count 1312537867 Latch (Share) 0 |
Very useful, thanks