Since Oracle Database 11g, there has been a new cache area in the shared pool that is reserved specifically for result sets: the so-called result cache. Results of statements that use the result cache are stored in the result cache when they are executed and reused in subsequent executions. This drastically reduces execution time. To guarantee consistent queries, the cache is automatically invalidated when changes are made to the table values. The result cache can be used on the client or server side. Client-side use requires the use of OCI calls.
The result cache is not a bolivia telegram screening panacea for all possible slow-running queries. As with all technologies, a specific application profile should be met. For example, long-running, computationally intensive SQL queries and PL/SQL constructs that are predictable and repeat frequently can benefit from the result caches. The result set should be consistent and deterministic, i.e. have little DML activity on the underlying tables.
The following parameters must be set for the initial configuration: The RESULT_CACHE_MAX_SIZE parameter defines the total size of the reserved area for the result cache in the shared pool. The memory is reserved for results of SQL queries AND also for results of PL/SQL functions. If this parameter is set to 0, the result cache is switched off.