8.1 Server-Side Result Set Cache

The server-side result cache is used to cache the results of the current queries, query fragments, and PL/SQL functions in memory. The cached results are used in future uses of the query, query fragment, or PL/SQL function.

The cached results stay in the result cache memory part of the SGA. A cached result is automatically invalidated whenever a database object used in its creation is successfully changed. See Tuning the Result Cache for more information about configuring the server-side result set cache.

To enable the server-side result cache on True Cache:

1. On the primary database, verify that your query is eligible to take advantage of query result caching.

   For a query to be eligible for result caching, it has to satisfy certain requirements. See Requirements for the Result Cache.

2. On the primary database, allow the table's query results to be stored in the result cache for True Cache.

   ALTER TABLE table_name RESULT_CACHE (STANDBY ENABLE);

3. On True Cache, verify that the result cache is working for queries against the table.

   SELECT /*+ RESULT_CACHE */ DISTINCT(column_name) FROM table_name;

4. For query result caching on True Cache to behave the same as on the primary database, configure the query result cache size to be identical on both.

   In particular, set RESULT_CACHE_MAX_SIZE to the same value (and greater than 0) on both the primary database and True Cache. If the result cache size is smaller than the query result set, the database will use physical reads.

Support for server-side result set caching is available for both DBCA Thin and DBCA Oracle Call Interface (DBCA OCI) drivers.