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

IN-MeMOrY aND reSULt CaChe

the introduction of the in-memory option in Oracle Database version 12.1.0.2 does have a corresponding effect in
the behavior of the resuLt CaChe feature discussed in the previous section of this chapter. When the in-memory
cache option is invoked and the in-memory area is populated, an analytical operation that uses the RESULT CACHE
hint will populate the result cache buffer from the in-memory area instead of the database buffer cache.
Figure 9-8 illustrates this through a four-step process.

1. Data is read from disk and populated into the in-memory area of the instance where the

query was executed first.

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

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

4. 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.

Figure 9-8. Result cache from in-memory area

the explain plan from executing the query discussed in the workshop earlier with the RESULT CACHE hint
illustrates the usage of the in-memory area to filter the rows and to populate the result cache buffer.

SELECT /*+ RESULT_CACHE */ d_name,
SUM(s_quantity)
FROM stock S,
district D
WHERE S.s_w_id = D.d_w_id
GROUP BY d_name