Background:
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.
More Information:
Please use the following links on Oracle.com to get better understanding of this feature.
http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html
http://www.oracle.com/technetwork/articles/datawarehouse/vallath-resultcache-rac-284280.html
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/rescache/res_cache.htm
2 comments:
A note of caution about the parameter result_cache_mode, make sure that this is set to the same value in all RAC instances or your application could suffer unpredictable results.
I worked with one customer to debug an issue that had been on going for months and it turned out that the value was different across their nodes.
Wonderful Input +Jeff Barber. Thanks for sharing.
Post a Comment