Prior to 10g, every query must be re-executed even if there is no change in the tables. This reexecution
is unnecessary for few static tables and only caching using at client tools resolve this issue.
But, in 11g, query results can be cached in SGA. Just to avoid confusion, this is much different from
buffer cache caching database blocks. Result cache in addition to that.
Results are retrieved immediately from the result cache if there is no change to the underlying base
tables. Frequently executed queries will see performance improvements.
There are two ways to use it in 11g
a) Manual mode setting result_cache_mode to manual. Only queries with result_cache hint
will use result cache.
b) Force mode setting result_cache_mode parameter to force. All queries will use the result
cache.
In test case below, result cache is not in use. Queries were executed one after another. Same amount
of consistent gets used
SQL> set autotrace traceonly stat
SQL> select count(col1) , count(col2) from prod11 where col1=10;
0 db block gets
1011 consistent gets
SQL> select count(col1) , count(col2) from prod11 where col1=10;
0 db block gets
1011 consistent gets
In the test case below result cache is turned on using an hint /*+ result_cache */. Second execution
of same SQL consumed 0 consistent gets, since results were retrieved from result case without
actually performing
SQL> set autotrace traceonly stat
SQL> select /*+ result_cache */ count(col1) , count(col2) from prod11 where col1=10;
0 db block gets
1011 consistent gets
SQL> select /*+ result_cache */ count(col1) , count(col2) from prod11 where col1=10;0 db block gets
0 consistent gets