oracle-developer.net

query result cache in oracle 11g

Caching has been a feature of Oracle for as long as most of us can remember. Over the many years and versions, Oracle has continually expanded its caching mechanisms. We are all familiar with the buffer cache, keep cache, library cache, shared pool, PGA/UGA and so on. In addition to the many data and cursor caches included in its architecture, Oracle has also enhanced caching support for common programming techniques; for example, scalar subquery caching, global temporary tables and associative arrays. In 11g, Oracle has extended this further by adding result caching to both its server and client architecture.

There are three new result caching features in 11g:

query result cache;

PL/SQL function result cache; and

client OCI result cache.

This article will describe and demonstrate the query result cache only. The PL/SQL function result cache feature shares much of the same architecture, but will be the subject of a future article.

an overview

As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.

We will examine the features of the query result cache in more detail throughout this article.

database configuration

We will begin by looking at some of the database configuration required to use the query result cache. The initialisation parameters are as follows.

SQL> SELECT name, value, isdefault
2 FROM v$parameter
3 WHERE name LIKE 'result_cache%';

result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;

result_cache_max_size: this is the size of the result cache in bytes. The cache is allocated directly from the shared pool but is maintained separately (for example, flushing the shared pool will not flush the result cache);

result_cache_max_result: this specifies the highest percentage of the cache that is able to be used by a single resultset (default 5%); and

result_cache_remote_expiration: this specifies the number of minutes for which a resultset based on a remote object can remain valid. The default is 0 which means that resultsets dependant on remote objects will not be cached.

The cache size is dynamic and can be changed either permanently or until the instance is restarted. We will roughly double the size of the cache for this article and verify that we have a larger result cache as follows (note this was run as SYSDBA).

The setup for the result cache is simple and should be a one-time DBA operation. We will now see some examples of caching and using results below.

caching results manually

As we saw earlier, the default caching mode for this instance is MANUAL. This means that query resultsets will not be cached unless we instruct Oracle to do so by using the RESULT_CACHE hint. In our first example below, we will manually cache the results of a simple aggregate query. Note that the examples in this article are all based on the SH sample schema. First, we verify our cache mode as follows.

We will now run a query and cache its results. We will run this through Autotrace because we are interested in both the workload statistics and the execution plan (Autotrace will also conveniently suppress the query output).

Using the RESULT_CACHE hint, we have instructed Oracle to cache the results of this aggregate query. We can see that it returned 71 rows and took 5 seconds to execute. We will see the amount of work that Oracle did to generate these results further below, but first we will see the execution plan (note that this is a theoretical explain plan and not the real execution plan, but is a good approximation in this system).

Note the highlighted sections of the execution plan. It contains some new information, which we can summarise as follows:

first, we can see a new operation, "RESULT CACHE" at operation ID=1. This is the last step in this particular example and it is telling us that Oracle will cache the results of the preceding operations;

second, we see a system-generated name beside the RESULT CACHE operation. This is used internally as a key for looking up and matching SQL statements to their cached results;

third, we see a new section in the plan report on the result cache metadata for this query. This section includes information such as the objects that the results are dependant on (i.e. to maintain cache coherency) and the leading part of the SQL text that generated the results.

Finally, the Autotrace report displays the work that Oracle performed to generate these results.

We can see a range of I/O and CPU activity in these figures, as expected. We will now test the new query result cache by running the same query a second time and comparing the Autotrace report, as follows.

Starting with the statistics report, we can see that this time Oracle has done very little work. In fact it has performed none of the I/O, sorting or recursive SQL that was required to answer our query the first time. Oracle has recognised that the query can be satisfied from the result cache and simply returned the pre-computed answer to us instead, in approximately 0.1 seconds.

Interestingly, the execution plan remains the same (this is to be expected because the SQL is not optimised a second time) but is now slightly misleading. None of the plan operations actually take place once we have a reusable resultset, but the presence of the RESULT CACHE operation should alert us to the fact that we might already have a cached set of results. In fact, we can use the information supplied in this plan to verify the existence of a cached resultset for ourselves, which we will examine later in this article.

We have now seen a simple example of query result caching. Minimising the amount of work that Oracle has to do to answer our query will reduce the time it takes. It also follows that the more work Oracle can avoid, the better the gains from caching.

automatic result caching

The alternative result_cache_mode to MANUAL is FORCE. This can be session or system specific and in this mode Oracle will attempt to set or use cached query results when it can, unless we use the NO_RESULT_CACHE hint. We will see an example of this mode below. We will set the mode to FORCE at a session level, then repeat our previous SQL example minus the RESULT_CACHE hint. First we set the result_cache_mode as follows.

SQL> ALTER SESSION SET result_cache_mode = FORCE;

Session altered.

With Autotrace set, we will now run the same aggregate statement from our manual example above. Note that the only modification to this SQL statement is to remove the RESULT_CACHE hint.

This is really interesting! We can see that in FORCE mode without a RESULT_CACHE hint, Oracle has used the same plan as the "equivalent" statement in MANUAL mode with the RESULT_CACHE hint (though in reality they are different cursors). Note that the result cache hashes are the same, although the name parameter is different. Given this, we would expect Oracle to use our previously cached results, which we can verify from the Statistics report, as follows.

The wall-clock timing and Statistics report shows clearly that Oracle has retrieved the cached results.

dynamic result cache views

So far we have seen the effects of caching with the two modes of the query result cache. We will now look a little deeper into what happens with the query cache and what information Oracle exposes about it. We can search the data dictionary for the result cache dynamic views, as follows.

Oracle provides four dynamic views. We will have a brief look at these below (refer to the online documentation for more details: a link is provided at the end of this article). We will start with V$RESULT_CACHE_OBJECTS, which exposes the most information about our cached query results.

We can see two types of information in this view: dependencies and results. We will discuss dependencies later, but the results' names clearly align with the queries we have run so far (the SUM and MAX aggregate sales queries). The last query in the output is executed by SQL*Plus. Remember from earlier that we executed two SQL statements (equivalent except for the RESULT_CACHE hint) and note the CACHE_ID values. There is only one entry for the two statements due to the fact that they shared a result set and hashed to the same CACHE_ID.

We can also look at the result cache statistics for a high-level overview of how it is being used, as follows.

We can see some general information on cache entries and hits, which are minimal so far as we have used a clean cache to run our examples. We can also see some statistics about how our cache is organised (i.e. up to 2,048 blocks of 1,024 bytes each which is equivalent to the 2M result_cache_max_size value that we set earlier). Currently, we are using 32K of the cache (32 blocks) and no single result set can be more than 102 blocks (or consume 102K of memory).

result cache dependencies

Each query result is dependant on one or more tables (i.e. the source tables for the query). We can get information on which objects a query is dependant on in a number of places. The V$RESULT_CACHE_DEPENDENCY view summarises the dependencies for each entry in the result cache. We saw the dependencies parameter in the Result Cache report from DBMS_XPLAN.DISPLAY which listed the tables involved in our sample aggregate queries. We also saw entries in the V$RESULT_CACHE_OBJECTS view data with a type of "Dependency". We can put these together to summarise the dependencies as follows.

Dependencies are necessary to protect the integrity of the query results in the cache. If the data in any of the dependant tables is modified, Oracle will invalidate the result cache entry and will not use it until it is refreshed by a repeat of the original SQL. This behaviour cannot be circumvented, even if we are prepared to tolerate inconsistent results.

We can demonstrate result cache invalidation very easily. We will perform a "no-change" update to a single row of PRODUCTS and commit the transaction, as follows.

SQL> UPDATE products
2 SET prod_name = prod_name
3 WHERE ROWNUM = 1;

1 row updated.

SQL> COMMIT;

Commit complete.

We will now repeat one of our cached aggregation queries and measure the workload using Autotrace.

Oracle will not attempt to understand the nature of the modification to the dependant objects. Even with a no-change update, the cached result entry was invalidated and the subsequent repeat of the source SQL caused the data to be generated again. The V$RESULT_CACHE_OBJECTS view provides some statistics on this, as follows.

We can see that the invalidation occurred at two levels. First, the INVALIDATIONS column details the number of times that modifications to an underlying table have caused an invalidation. Second, the STATUS column shows us which results have been invalidated by the same action. When we updated the PRODUCTS table, we invalidated the results from our previous queries (IDs 2 and 7). We then repeated one of the original queries, for which Oracle created a new set of results in the cache (ID 10).

cache find count

If we are caching query results, we might be interested to know how often they are used. The V$RESULT_CACHE_STATISTICS view provides a "Find Count" statistic, but this is cache-wide so we can't limit it to a particular query. In the following example, we will capture the current Find Count and then run a SQL statement in a PL/SQL loop 100 times.

This increased by 99, which is to be expected. We executed our SQL statement 100 times. The first execution cached the results and the 99 remaining executions used them. Needless to say, this was a single-user test system. We can confirm that we added the SQL results to the cache as follows.

parameterised caching

In our previous example, we executed a single SQL statement 100 times and saw 1 cache entry. A far more common scenario is to have single-row lookups based on a primary key derived from another cursor (this is not particularly efficient, but is still extremely common). The query result cache handles this scenario by recognising the different bind variables and caching each resultset independently. The bind variables act as parameters to the result cache lookup and are listed in the Result Cache report from DBMS_XPLAN. If a bind variable is repeated, the cached results will be used.

We will demonstrate this behaviour below. We will set the result_cache_mode to FORCE for convenience. We will choose 4 products and lookup each one 10 times. The lookup will use bind variables.

According to what we now know about the result cache mechanism, we ran 4 different SQL statements above (the same SQL statement with 4 different inputs). We will query V$RESULT_CACHE_OBJECTS to verify this, as follows.

We can see that the results for the same SQL text was added to the cache 4 times, as expected. The bind variable inputs are additional parameters to the cache lookup. Each resultset was added on the first execution of each cursor and the cache was "hit" 9 times for each cursor. Similar logic is commonly used by developers in associative array caching; a colleague of mine calls this "on-demand caching" (i.e. rather than cache entire lookup tables, only cache a lookup record when it is actually requested).

Remember that the result_cache_max_result parameter specifies that the largest cached resultset possible is n% of the total cache memory. While this protects us from filling the cache with the results of a single SQL statement, it doesn't stop us from filling the cache with parameterised cursors like those we saw above. In the following example, we will lookup every customer in the CUSTOMERS table twice. Again, we will be in FORCE result_cache_mode for convenience. Note that there are 55,500 records in the SH.CUSTOMERS demo table.

We will examine the cache entries below. Based on what we know about result cache behaviour, we can expect a large number of single-row resultsets, so we will try to aggregate these. We will query the minimum and maximum names in V$RESULT_CACHE_OBJECTS, together with a count of the entries, as follows.

We added 2,035 customer lookups (out of a possible 55,500) to the cache. In fact, we completely flushed our previous results from the cache. We should therefore be aware of the potential for single lookups, particularly in PL/SQL programs, to "hog" the cache. If we query V$RESULT_CACHE_STATISTICS, we will see that the "Create Count Success" statistic should be quite high.

We have added over 111,000 resultsets to the cache, mostly as a result of the previous example. The loop through 55,500 customers would have continually replaced the existing cache entries (we only had room in the cache for approximately 4% of the total resultsets being processed in the PL/SQL).

flashback query results

The query result cache supports flashback queries. Most readers will be aware of flashback queries (an overview is available here). An SCN or timestamp is supplied to a flashback query using the AS OF extension to the table(s) in the FROM clause. This supplied point-in-time is treated by Oracle as a parameter to the query result cache.

To demonstrate this, we will run a simple flashback query twice. We will use Autotrace to demonstrate the result cache behaviour. We will begin by setting the result_cache_mode to FORCE for convenience.

SQL> ALTER SESSION SET result_cache_mode = FORCE;

Session altered.

We will setup a bind variable for our timestamp and execute a simple flashback query, as follows.

The Result Cache Information report provided by DBMS_XPLAN includes the parameters that Oracle used for this query; in this case the TS bind variable. The execution plan confirms that Oracle will cache the results of this flashback query. We will run the same query a second time to see if the results are re-used, as follows.

SQL> SELECT MIN(prod_id)
2 FROM products AS OF TIMESTAMP TO_TIMESTAMP(:ts,'YYYYMMDDHH24MISS');

Given the same inputs (i.e. the same bind variable), Oracle was able to retrieve the flashback query results from the result cache, as confirmed by the Autotrace Statistics report.

performance considerations

Recall our examples so far. First, we ran some aggregate "report-style" queries to demonstrate the caching mechanism. These are probably not the types of queries we would expect to cache and re-use (they are primarily in the style of run-once reports). Second, we ran some lookup examples from PL/SQL, both with and without primary key predicates. These are far more likely to be candidates for caching in our applications: single-row lookups are common in PL/SQL programs. For this reason, we will concentrate on lookups to test the performance of the query result cache.

In the following example, we will use a version of Tom Kyte's Runstats program to compare the performance of 1 million cached and uncached single-row lookups. First, we will switch to MANUAL result_cache_mode, as follows.

SQL> ALTER SESSION SET result_cache_mode = MANUAL;

Session altered.

We will select 8 primary key values from the SH.PRODUCTS table and perform 125,000 lookups for each (totalling 1 million executions). We will begin with the uncached version, as follows.

We can see that there is little performance difference between the cached and uncached lookups. In fact, the result cache uses more latches (note the two million hits on the new Result Cache latch), although it is marginally quicker overall. These results are probably to be expected when we consider the work to be done in a single primary key lookup. First, the index and table data will be in the buffer cache and second, the amount of I/O saved by a lookup in the query result cache is minimal.

The best performance gains will be achieved when the result cache is preventing a large amount of database work from being repeated (such as in our aggregate queries earlier). Unfortunately, these types of queries are going to be rare in the result cache as the base tables will probably be frequently updated.

encapsulating cached results

Caching lookup tables (in KEEP caches, user-defined arrays etc) is nothing new in Oracle. The query result cache provides another mechanism for doing this. In the following example, we will encapsulate the PRODUCTS table in a view. We will include the RESULT_CACHE hint and then load the contents of PRODUCTS into the query result cache, as follows.

For some reason, we are missing the information from the Result Cache section, but we can see clearly on operation 9 that we incurred no I/O from the PRODUCTS table. We will see if the same is true of a join to the V_PRODUCTS view itself below.

This time, we have incurred some I/O against the PRODUCTS table. Therefore, if we wish to cache a frequently-used table, we will need to repeat whatever query cached the data in the first place.

dbms_result_cache

Finally, we will look at a new built-in package, DBMS_RESULT_CACHE. This package is primarily for DBAs and has a number of APIs to manage and report on the result cache. We will look at one API in particular; the FLUSH function and procedure. Oracle supplies this API in both formats and as its name suggests, it enables us to clear the result cache and its statistics. We can optionally retain either the free memory in the cache or the cache statistics, but the default is to flush everything, as we will see below (note this is executed as SYSDBA).

All of our query results have been removed and the statistics have been reset. Needless to say, flushing the cache is not something that we would expect to do very often, if at all.

As stated, there are several other APIs for result cache management. The online documentation has full details and a link is provided at the end of this article.

summary

In this article, we have taken a brief look at the query result cache and how it can be used to optimise commonly-used queries. We have seen that the cache can provide good performance gains when the amount of I/O and CPU work required to repeat the results is significant. Conversely, we have seen that the gains from low-intensity queries (such as primary key lookups) are marginal. Unfortunately, it is probably the case that lookup and reference queries are the ones that our applications will repeat frequently; in which case the query result cache will not necessarily provide the gains that we might imagine from such a feature.