How to Use the New Oracle 11g Result Cache

In Oracle 11g, a new feature called the Result Cache lets you cache SQL results in an area of the SGA to improve performance. The following RESULT_CACHE hint caches the results on execution:

 

 

 

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.

TIP

If you are executing the same queries over and over (especially grouping or calculation functions), using the RESULT_CACHE hint often makes subsequent queries run faster.