The Result Cache is a feature in Oracle 11g and later versions that allow the database to store the results of frequently executed queries in memory, in order to improve performance by avoiding the need to re-execute the same query multiple times.
When a query is executed, the database first checks the Result Cache to see if the results of the query have already been stored there. If they have, the database can retrieve the results from the Result Cache rather than re-executing the query, which can save a significant amount of time.
“enq: RC – Result Cache: Contention” is an Oracle database message that indicates a contention or conflict for the Result Cache.
When multiple sessions try to access the same Result Cache data at the same time, a contention can occur, and the message “enq: RC – Result Cache: Contention” is generated to indicate this. To avoid this issue, You can try to re-write the query or increase the size of the Result Cache.
Some useful commands related to the Result Cache in the Oracle database are as below:
To set the Result Cache mode to either MANUAL or FORCE. The MANUAL mode allows the developer to decide when to use the Result Cache, while the FORCE mode automatically uses the Result Cache for all queries.
ALTER SYSTEM SET RESULT_CACHE_MODE = {MANUAL | FORCE}
To set the Result Cache mode for the current session only.
ALTER SESSION SET RESULT_CACHE_MODE = {MANUAL | FORCE}
To view the statistics for the Result Cache, such as the number of queries executed, the number of queries that used the Result Cache, and the number of times the Result Cache was used.
SELECT * FROM V$RESULT_CACHE_STATISTICS
To view the memory usage of the Result Cache.
SELECT * FROM V$RESULT_CACHE_MEMORY
To view all the queries that are stored in the Result Cache with the VALID status.
SELECT * FROM V$SQL WHERE RESULT_CACHE_STATUS = 'VALID'
To enable or disable the Result Cache for the current session only.
ALTER SESSION SET RESULT_CACHE = {OFF | ON}
To set the maximum size of the Result Cache, after which it will start evicting older entries.
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = {size in bytes}
- Oracle Database Service for Azure (ODSA) 3 – Database Services - May 1, 2023
- Oracle Database Service for Azure (ODSA) 2 – Interconnect Details - April 30, 2023
- Oracle Database Service for Azure (ODSA) 1 – Fundamentals - April 29, 2023