I had a flawed understanding of the result cache in that I felt it was used as a method to bypass an expensive access of the data blocks. In other words, Oracle expends resources to fetch a result set and then caches it for future users obviating the need to repeat the expensive data retrieval. A colleague questioned this and demonstrated that the actual methodology seems to be different. All of this was done on an 11GR2 instance.
What is the results cache?
It is an area in the shared pool that Oracle writes the results of the query. The goal is to avoid reading the data again and thus returning it faster to the user who runs the same SQL.
How do I control what goes into the results cache?
This is controlled via the RESULT_CACHE_MODE parameter. This parameter has two possible values
MANUAL – This is the default mode. The users are responsible for deciding what when to use the cache. This is controlled via the use of the /*+ result_cache */ hint.
FORCE – Oracle adds it to all possible statements
For the purpose of this write up, the RESULT_CACHE_MODE is set to MANUAL.
Assume that user TOM creates a table, adds data and then runs a select against that table. The first select will access the table and this can be confirmed by checking the execution plan. The second select of the table will also access the table. For the third execution of the select, add in the /*+ result_cache */ hint. Checking the execution plan will now reveal that the data was returned from the results cache.
Now switch users to BOB, set up a synonym pointing to TOM’s table and run the same SQL without a hint. This execution will access the table. Add in the hint and run the SQL and the data will be fetched from the cache.
V$RESULT_CACHE_OBJECTS displays all the objects (both cached results and dependencies) and their attributes. You can confirm that the same SQL used the results cache in response to the hint by checking the column SCAN_COUNT. This column is incremented every time the results cache is used to satisfy the SQL.
Invalidating the cache
If the data in the table is updated, the next execution of the SQL with the hint will not use the results cache. A new row will be added to the V$RESULT_CACHE_OBJECTS table and future executions of the SQL with the hint will cause the SCAN_COUNT to be incremented on this new row.