Database Reference
In-Depth Information
Result Caching
Caching is one of the most common techniques used in computer systems to improve performance. Both hardware
and software make extensive use of it. Oracle Database is no exception. For example, it caches data file blocks in the
buffer cache, data dictionary information in the dictionary cache, and cursors in the library cache. As of version 11.1,
result caches
are also available.
■
Note
result caches are available in enterprise edition only.
How It Works
Oracle Database provides three result caches:
•
The
server result cache
(also known as
query result cache
) is a server-side cache that stores
query result sets.
•
The
PL/SQL function result cache
is a server-side cache that stores the return value of PL/SQL
functions.
•
The
client result cache
is a client-side cache that stores query result sets.
The next sections describe how these caches work and what you have to do to take advantage of them. Note that,
by default, result caches aren't used.
Server Result Cache
The server result cache can be used to avoid the reexecution of queries and some subqueries (those defined in the
WITH
clause and inline view defined in the
FROM
clause). Simply put, the first time a query is executed, its result set is
stored in the shared pool. Then, for subsequent executions of the same query, the result set is served directly from
the result cache instead of being recalculated. Note that two queries are considered equal and, therefore, can use
the same cached result, only if they have the same text (differences in blank spaces and capitalization are allowed,
though). In addition, if bind variables are present, their values must all be the same. This is necessary because,
quite obviously, bind variables are input parameters that are passed to the query, and hence, the result set is usually
different for different bind variable values. Also note that the result cache is stored in the shared pool, and all sessions
connected to a given database instance share the same cache entries.
To provide you with an example (based on the
rc_query_hint.sql
script), let's execute the query already used
in the section on materialized views twice (notice that the
result_cache
hint is specified in the query to enable the
result cache):
SQL> SELECT /*+
result_cache
*/
2 p.prod_category, c.country_id,
3 sum(s.quantity_sold) AS quantity_sold,
4 sum(s.amount_sold) AS amount_sold
5 FROM sales s, customers c, products p
6 WHERE s.cust_id = c.cust_id
7 AND s.prod_id = p.prod_id
8 GROUP BY p.prod_category, c.country_id
9 ORDER BY p.prod_category, c.country_id;
