The Oracle 11g Result Set Cache stores entire result sets in shared memory.
If a SQL query is executed and its result set is in the cache then almost the entire overhead of the
SQL execution is avoided: this includes parse time, logical reads, physical reads and any cache contention
overhead (latches for instance) that might normally be incurred.
1) Multiple SQLs that have overlapping data will store that data redundantly in the cache. So the result set that contains
all customers from ‘X’ will duplicate some of the data in the cached result set for all customers from ‘Y’.
Therefore, the result set cache is not always as memory efficient as the buffer cache.
2) Any change to a dependent object – to any table referenced in the query – will invalidate the entire cached result set.
So the result set cache is most suitable for tables that are read only or nearly read only.
3) Really big result sets will either be too big to fit in the result set cache, or will
force most of the existing entries in the cache out.
4) Rapid concurrent creation of result sets in the cache will result in latch contention.
Note: The result set cache is part of the shared pool. By default it is sized at only 1% of the shared pool,
so is usually pretty small. I increased the size of my result set cche to 10MB using the RESULT_CACHE_MAX_SIZE parameter.
************** Not suitable for all SQLs ************************
Result set caching makes sense only if the following are true:
1) The result set is small
2) The SQL statement is expensive
3) The SQL statement does not experience high rates of concurrent execution
4) The SQL does not generate a large number of result sets
5) The SQL is against relatively static tables
The result set cache best suits small result sets from expensive queries on tables that are infrequently updated.
So before applying the result set cache to all SQLs is to be ineffective and can lead to significant latch contention.
Application team has to drill down the SQLs and provide more information before going for some specific implemenation.