New 11g Result Cache

select /*+ result_cache */ SUM(sal)
from   scott.emp
where  deptno=20;


When a query with RESULT_CACHE hint is run, Oracle will see if the results of the query have already been executed, computed, and cached, and, if so, retrieve the data from the cache instead of querying the data blocks and computing the results again. Take the following important points into consideration before using this feature:

The Result Cache feature is useful only for SQL queries that are executed over and over again frequently.

The underlying data doesn’t change very often. When the data changes, the result set is removed from the cache.

If you are executing the same queries over and over, using the RESULT_CACHE hint often makes subsequent queries run faster


Note that the Result Cache does not work when you are logged in as sysdba.