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

• The cluster has four instances; however, the view contains data only when the result cache

for the instance has been utilized, meaning there are no entries in the result cache section for
instance 2 and instance 4, respectively. However, when a user executes the identical query
on either of these instances, the behavior observed on instance 3 will be duplicated on these
instances.

Will the query keep returning the same result set all the time? What happens when data changes in the
underlying objects? What happens to the result cache section that contains the results?
Good questions. Let's continue with the workshop and try to answer some of these questions.
When data changes in any of the underlying objects, Oracle Database invalidates the result set on all the
instances in the cluster, indicating that subsequent execution of the same query will require fresh processing of data to
rebuild the result set and store the result in the result cache section of the shared pool.

Script: MVRACPDnTap_rcobjects.sql

If the query is executed again to retrieve the new result set from the database, a new row ( ID=9 ) is added to the
result cache section. Oracle Database retains the invalid result set until the instance is bounced, the result cache is
flushed, or the data in the result cache ages out of the buffer.

Script: MVRACPDnTap_rcobjects.sql

In the output above, when a few rows are deleted from the ORDER_LINE table, the result row is marked as Invalid
and the INVALIDATIONS (INVALS) counter of the GV$RESULT_CACHE_OBJECTS view is incremented. If, subsequently,
there is another operation on the ORDER_LINE table, either by adding new rows or by deleting a few more rows, the
counter will be incremented again. This indicates how many times the object was invalidated in the result cache.