Friday, March 8, 2013
SQL result_cache in Oracle 11g
For the problem mentioned in my past blog post - http://technofunctionalconsulting.blogspot.in/2013/02/data-agility-in-master-data-management.html there are times where the SQL Queries are expensive and need lot of processing to generate a result set. These queries are executed from multiple sessions and it would be good if we can get the prepared result in the memory.
SQL Result Cache:
This feature is available in Oracle database 11g that can be enabled with the initialization parameter result_cache_mode the possible values for this parameter are FORCE (will cache all results and not recommended) and MANUAL. Setting this value to MANUAL one can selectively cache the results from the SQLs where the hint /*+ RESULT_CACHE */ is added just after the SELECT.
RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MAX_RESULT are the other parameters that impact the way the result cache will function by defining the maximum amount of memory used and the maximum amount of memory a single result set can occupy.
Please use the following links on Oracle.com to get better understanding of this feature.