Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 291

With the result cache feature, the final results of the query are stored in the result cache section of the shared pool
and, subsequently, when a user executes the same query, instead of the process having to traverse through all the
million rows in the buffer cache, the process bypasses this step and retrieves data from the result cache section of the
shared pool.
In an Oracle RAC environment (Figure 9-4 ), this process is no different when the query is executed multiple times
from one instance; results are retrieved from the result cache. So is there a difference? Actually, there is and there is
not. The difference is when the second instance in the cluster executes the same query with the /*+ RESULT CACHE */
hint. Instead of getting all the rows from the I/O subsystem, only the results from the result cache are transferred.

Figure 9-4. Result cache behavior in an Oracle RAC environment

This is a great benefit in a data warehouse that uses RAC. In a data warehouse, large volumes of data are
retrieved, filtered, sorted, and displayed to the user. When users from multiple instances execute the same query,
there is the possibility that all of the rows will be transferred over the interconnect to the requesting instance. Using
result cache helps reduce interconnect traffic or calls to the I/O subsystem. Then why does the documentation say
the result cache is local to the instance? Because it is! In an Oracle RAC environment, the result cache is maintained
locally within the shared pool of the instance. Figure 9-4 illustrates this through a four-step process.

1. Data is read from disk and populated into the buffer cache of the instance where the query

was executed first.

Based on the WHERE condition of the query, the data is filtered in the buffer cache. 2.

3. The final result set is populated into the result cache section of the shared pool.

Subsequently when another user executes the same query with the RESULT CACHE hint
on another instance, the result set is moved from the result cache buffer of the holding
instance to the requesting instance.

4.