<\/p>\n
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.<\/p>\n
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.<\/p>\n
The Result Cache came with the Oracle Database 11g Release 1 version and is enabled by default.<\/p>\n
We can learn the Result Cache status with the following query.<\/p>\n
SQL> SELECT dbms_result_cache.status() FROM dual;\r\n\r\nDBMS_RESULT_CACHE.STATUS()\r\n--------------------------------------------------------------------------------\r\nENABLED<\/pre>\nIt 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.<\/p>\n
SQL> ALTER SYSTEM SET result_cache_max_size = 2G SCOPE = SPFILE;\r\nSQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE = SPFILE;<\/pre>\nYou will then need to restart the database.<\/p>\n
To use the Result Cache, you need to add \u00a0\/*+ RESULT_CACHE *\/ hint to the query. The sample query is as follows.<\/p>\n
SQL> SELECT \/*+ RESULT_CACHE *\/\r\np.prod_name,\r\nSUM (s.amount_sold) AS total_revenue,\r\nSUM (s.quantity_sold) AS total_sales\r\nFROM sales s, products p\r\nWHERE s.prod_id = p.prod_id\r\nGROUP BY p.prod_name;<\/pre>\nThe 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.<\/p>\n
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.<\/p>\n
We can change the result_cache_mode parameter as follows:<\/strong><\/p>\n
SQL> alter system set result_cache_mode=FORCE scope=both sid='*';\r\n\r\nSystem altered.<\/pre>\nEven 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.<\/p>\n
Querying cached objects :<\/strong><\/p>\n
With the following query, you can find the objects used result cache.<\/p>\n
SQL> SELECT name,\r\nTYPE,\r\ncache_id,\r\nrow_count\r\nFROM v$result_cache_objects\r\nORDER BY creation_timestamp;<\/pre>\nThe result cache may need to be flushed in some cases. You can clear existing cache as follows.<\/p>\n
SQL> BEGIN\r\nDBMS_RESULT_CACHE.FLUSH;\r\nEND;\r\n\/\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\nMemory 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.<\/p>\n
SQL> set serveroutput on\r\nSQL> exec dbms_result_cache.memory_report(TRUE);\r\nR e s u l t C a c h e M e m o r y R e p o r t\r\n[Parameters]\r\nBlock Size = 1K bytes\r\nMaximum Cache Size = 52448K bytes (52448 blocks)\r\nMaximum Result Size = 2622K bytes (2622 blocks)\r\n[Memory]\r\nTotal Memory = 174336 bytes [0.004% of the Shared Pool]\r\n... Fixed Memory = 14880 bytes [0.000% of the Shared Pool]\r\n....... Memory Mgr = 200 bytes\r\n....... Cache Mgr = 208 bytes\r\n....... Bloom Fltr = 2K bytes\r\n....... = 4120 bytes\r\n....... RAC Cbk = 5408 bytes\r\n....... State Objs = 2896 bytes\r\n... Dynamic Memory = 159456 bytes [0.004% of the Shared Pool]\r\n....... Overhead = 126688 bytes\r\n........... Hash Table = 64K bytes (4K buckets)\r\n........... Chunk Ptrs = 24K bytes (3K slots)\r\n........... Chunk Maps = 12K bytes\r\n........... Miscellaneous = 126688 bytes\r\n....... Cache Memory = 32K bytes (32 blocks)\r\n........... Unused Memory = 31 blocks\r\n........... Used Memory = 1 blocks\r\n............... Dependencies = 0 blocks (0 count)\r\n............... Results = 1 blocks\r\n................... SQL = 1 blocks (1 count)\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\n“v$result_cache_statistics” is another view where you can get information about the Result Cache.<\/p>\n
SQL> select name, value from v$result_cache_statistics;\r\n\r\nNAME VALUE\r\n------------------------------ --------------------\r\nBlock Size (Bytes) 1024\r\nBlock Count Maximum 3072000 <<<<<<<<<<< 3 GB Result Cache maximum size\r\nBlock Count Current 1038720 <<<<<<<<<<< 1 GB is currently used\r\nResult Size Maximum (Blocks) 153600\r\nCreate Count Success 1047967\r\nCreate Count Failure 80\r\nFind Count 1312482070\r\nInvalidation Count 9056\r\nDelete Count Invalid 9402\r\nDelete Count Valid 0\r\nHash Chain Length 237-269\r\nFind Copy Count 1312537867\r\nLatch (Share) 0<\/pre>\n\n<\/div>\n