RESULT_CACHE Parameters

Lets look into 4 result cache parameters:

select * from v$parameter where name like 'result_cache%';

NAME VALUE
result_cache_mode MANUAL
result_cache_max_size 21495808
result_cache_max_result 5
result_cache_remote_expiration 0

Lets examine each value.
1. result_cache_mode : If this is MANUAL then we need to explicitly user "result_cache" key word to cache the results in the cache. If it is set to FORCE all the queries are cached if it valid and fits into the cache.
2. result_cache_max_size : This is maximum size of result_cache memory can hold, This is part of SGA and cannot hold more than 75% size of SGA
3. result_cache_max_result : This tells us what percentage of result cache any single query set can occupy. In this case only 5% of result_cache memory can be occupied by a single SQL query result set.
4. result_cache_remote_expiration : specifies the number of minutes the cached result set that access a remote object will remain valid. In this case the remote objects result set are not cached at all.

So thats about the 4 parameters of result_cache of 11G.

Result_Cache Statistics

After enabling the result cache you would want to know whether it is really helping improve performance. This can be done with help of result cache views.
One such view is v$result_cache_statistics;
select name, value from v$result_cache_statistics;

NAME VALUE
Block Size(Bytes) 1024
Block Count Maximum 3036
Block Count Current 80
Result Size Maximum blocks 240
Create Count Success 10
Create Count Failure 80
Find Count 4
Invalidation Count 3
Delete Count Invalid 0
Delete Count Valid 80

If we examine the above output Create count failure is too high, which might not help in optimized performance.
Create count value tells us how many cache results that were failed to create.

Find Count value must be as high as possible for best performance. In this case its low. Find count depicts the number of cache results that were successfully found.

Delete count valid depicts the number of valid cache results deleted. This value should be relatively low to make system full use of server result caching.
block count current gives us the value of how much memory is used to store cached data.
This is some information about v$result_cache_statistics.