SQL result_cache in Oracle 11g

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

Advertisements

2 Responses to “SQL result_cache in Oracle 11g”

  1. Jeff Barber Says:

    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.

  2. Prasad Chitta Says:

    Wonderful Input +Jeff Barber. Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: