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

One way to reduce the hard parses that happen due to frequent loads is to increase the size of the shared pool
using the parameter SHARED_POOL_SIZE . The ideal configuration of the shared pool can be determined by querying
the V$SHARED_POOL_ADVICE view.
Like the shared pool algorithm, Oracle uses the LRU algorithm to remove entries in the session cursor cache.
As part of the features for optimizing the database, a feature that was introduced in Oracle 11g is not used for the
right reasons. Let's discuss this feature, the result cache, in the next section.
Result Cache
In Oracle Database 11g Release 1, Oracle introduced the result cache feature. The name of the feature is
self-explanatory. Cache the result (result cache), either on the client side using OCI calls or on the database
server/instance level using an instance level cache area called result cache, part of the shared pool. The database
result cache can be used by either a SQL statement/operation or using a database function. In this section, the two
types of result cache operations at the database level are discussed.
The following parameters are used to manage the behavior of this feature:
NAME TYPE VALUE
------------------------------ ----------- ----------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 251680K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
The parameters client_result_cache_lag and client_result_cache_size are used to configure the result
cache at the client side. The other parameters are used for configuring the result cache at the server side.
The size of the result cache on the server is determined by two parameters: result_cache_max_result and
result_cache_max_size . By default, the result_cache_max_size parameter is about 0.25% of the memory_target
parameter or 1% of the shared_pool parameter. This parameter can be modified to control how much is stored in the
result cache. The result_cache_max_result parameter specifies what percentage of result_cache_max_size a single
result cache can use. The default value is 5%.
Each result set is identified in the cache using a CACHE_ID , which is a 90-character-long string. The CACHE_ID for
a query does not match the SQL_ID used to identify the query in the library cache and contained in V$SQL . Unlike the
SQL_ID , which is generated for every SQL query executed against an Oracle database, the CACHE_ID is for an area or
bucket in the result cache section of the shared pool that stores the end result of the query.
Query Result Cache
When a query is executed for the very first time, the user's process searches for the data in the database buffer cache.
If data is there (because someone else retrieved this data before), it uses it; otherwise, it performs an I/O operation to
retrieve data from the data file on disk into the buffer cache, and from this data, the final result set is built.
Subsequently, if another query requires the same data set, the process uses the data from the buffer cache to
build the result set required by the user. Well, if the buffer cache contains data for reuse, then what's this new result
cache? In simple terms, the result cache can be called a cache area within a cache, in this case, the shared pool. So, the
result cache is an area in the shared pool and contains the end results of a query execution.