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%';
NAME VALUE ISDEFAULT
---------------------------------- ------------------ ---------
result_cache_mode MANUAL TRUE
result_cache_max_size 1081344 TRUE
result_cache_max_result 5 TRUE
result_cache_remote_expiration 0 TRUE
4 rows selected.
A brief explanation of each of these parameters is as follows.
- 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).
SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;
System altered.
SQL> SELECT name, value
2 FROM v$parameter
3 WHERE name = 'result_cache_max_size';
NAME VALUE
---------------------------------------- -------------------------
result_cache_max_size 2097152
1 row selected.
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.
SQL> SELECT value
2 FROM v$parameter
3 WHERE name = 'result_cache_mode';
VALUE
----------------
MANUAL
1 row selected.
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).
SQL> set autotrace traceonly
SQL> set timing on
SQL> SELECT
2 p.prod_name
3 , SUM(s.amount_sold) AS total_revenue
4 , SUM(s.quantity_sold) AS total_sales
5 FROM sales s
6 , products p
7 WHERE s.prod_id = p.prod_id
8 GROUP BY
9 p.prod_name;
71 rows selected.
Elapsed: 00:00:05.00
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).
Execution Plan
----------------------------------------------------------
Plan hash value: 504757596
----------------------------------------------------------------------- ... -----------------
| Id | Operation | Name | Rows | ... | Pstart| Pstop |
----------------------------------------------------------------------- ... -----------------
| 0 | SELECT STATEMENT | | 71 | ... | | |
| 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | |
| 2 | HASH GROUP BY | | 71 | ... | | |
|* 3 | HASH JOIN | | 72 | ... | | |
| 4 | VIEW | VW_GBC_5 | 72 | ... | | |
| 5 | HASH GROUP BY | | 72 | ... | | |
| 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 |
| 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 |
| 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | |
----------------------------------------------------------------------- ... -----------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
p.prod_name
, SUM(s.amount_sold) AS total_revenue
, SUM(s.quantity_sold) AS total_"
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.
Statistics
----------------------------------------------------------
14871 recursive calls
0 db block gets
4890 consistent gets
1745 physical reads
0 redo size
3526 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
136 sorts (memory)
0 sorts (disk)
71 rows processed
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.
SQL> SELECT
2 p.prod_name
3 , SUM(s.amount_sold) AS total_revenue
4 , SUM(s.quantity_sold) AS total_sales
5 FROM sales s
6 , products p
7 WHERE s.prod_id = p.prod_id
8 GROUP BY
9 p.prod_name;
71 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 504757596
----------------------------------------------------------------------- ... -----------------
| Id | Operation | Name | Rows | ... | Pstart| Pstop |
----------------------------------------------------------------------- ... -----------------
| 0 | SELECT STATEMENT | | 71 | ... | | |
| 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | |
| 2 | HASH GROUP BY | | 71 | ... | | |
|* 3 | HASH JOIN | | 72 | ... | | |
| 4 | VIEW | VW_GBC_5 | 72 | ... | | |
| 5 | HASH GROUP BY | | 72 | ... | | |
| 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 |
| 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 |
| 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | |
----------------------------------------------------------------------- ... -----------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT /*+ RESULT_CACHE */
p.prod_name
, SUM(s.amount_sold) AS total_revenue
, SUM(s.quantity_sold) AS total_"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
3526 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71 rows processed
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.
SQL> set autotrace traceonly
SQL> set timing on
SQL> SELECT p.prod_name
2 , SUM(s.amount_sold) AS total_revenue
3 , SUM(s.quantity_sold) AS total_sales
4 FROM sales s
5 , products p
6 WHERE s.prod_id = p.prod_id
7 GROUP BY
8 p.prod_name;
71 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 504757596
----------------------------------------------------------------------- ... -----------------
| Id | Operation | Name | Rows | ... | Pstart| Pstop |
----------------------------------------------------------------------- ... -----------------
| 0 | SELECT STATEMENT | | 71 | ... | | |
| 1 | RESULT CACHE | 091zc7mvn8ums36mbd2gqac4h0 | | ... | | |
| 2 | HASH GROUP BY | | 71 | ... | | |
|* 3 | HASH JOIN | | 72 | ... | | |
| 4 | VIEW | VW_GBC_5 | 72 | ... | | |
| 5 | HASH GROUP BY | | 72 | ... | | |
| 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 |
| 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 |
| 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | |
----------------------------------------------------------------------- ... -----------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT p.prod_name
, SUM(s.amount_sold) AS total_revenue
, SUM(s.quantity_sold) AS total_sales
FROM sales s
, "
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.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
3526 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71 rows processed
We can see quite clearly that Oracle has returned the results that it
cached from the manual-mode, hinted SQL. It recognises their
equivalence despite the difference in the SQL text.
Continuing in FORCE result_cache_mode, we will now execute a new aggregate statement, as follows.
SQL> SELECT p.prod_name
2 , MAX(s.quantity_sold) AS max_sales
3 FROM sales s
4 , products p
5 WHERE s.prod_id = p.prod_id
6 GROUP BY
7 p.prod_name;
71 rows selected.
Elapsed: 00:00:00.59
Execution Plan
----------------------------------------------------------
Plan hash value: 504757596
----------------------------------------------------------------------- ... -----------------
| Id | Operation | Name | Rows | ... | Pstart| Pstop |
----------------------------------------------------------------------- ... -----------------
| 0 | SELECT STATEMENT | | 71 | ... | | |
| 1 | RESULT CACHE | 12scakxrxks3p73w5nxr69wn3j | | ... | | |
| 2 | HASH GROUP BY | | 71 | ... | | |
|* 3 | HASH JOIN | | 72 | ... | | |
| 4 | VIEW | VW_GBC_5 | 72 | ... | | |
| 5 | HASH GROUP BY | | 72 | ... | | |
| 6 | PARTITION RANGE ALL| | 918K| ... | 1 | 28 |
| 7 | TABLE ACCESS FULL | SALES | 918K| ... | 1 | 28 |
| 8 | TABLE ACCESS FULL | PRODUCTS | 72 | ... | | |
----------------------------------------------------------------------- ... -----------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SH.SALES, SH.PRODUCTS); parameters=(nls); name="SELECT p.prod_name
, MAX(s.quantity_sold) AS max_sales
FROM sales s
, products p
WHERE s.prod_id = p.prod_id
GROUP "
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1731 consistent gets
0 physical reads
0 redo size
2687 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71 rows processed
In FORCE mode, Oracle has cached the results of our query without us
requesting it. We can see this in the execution plan. We will run the
query a second time, as follows.
SQL> SELECT p.prod_name
2 , MAX(s.quantity_sold) AS max_sales
3 FROM sales s
4 , products p
5 WHERE s.prod_id = p.prod_id
6 GROUP BY
7 p.prod_name;
71 rows selected.
Elapsed: 00:00:00.00
<< ...plan removed... >>
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2687 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71 rows processed
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.
SQL> SELECT view_name
2 FROM dba_views
3 WHERE view_name LIKE 'V_$RESULT_CACHE%';
VIEW_NAME
------------------------------
V_$RESULT_CACHE_DEPENDENCY
V_$RESULT_CACHE_MEMORY
V_$RESULT_CACHE_OBJECTS
V_$RESULT_CACHE_STATISTICS
4 rows selected.
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.
SQL> DESC v$result_cache_objects;
Name Null? Type
------------------------ ------ ------------------
1 ID NUMBER
2 TYPE VARCHAR2(10)
3 STATUS VARCHAR2(9)
4 BUCKET_NO NUMBER
5 HASH NUMBER
6 NAME VARCHAR2(128)
7 NAMESPACE VARCHAR2(5)
8 CREATION_TIMESTAMP DATE
9 CREATOR_UID NUMBER
10 DEPEND_COUNT NUMBER
11 BLOCK_COUNT NUMBER
12 SCN NUMBER
13 COLUMN_COUNT NUMBER
14 PIN_COUNT NUMBER
15 SCAN_COUNT NUMBER
16 ROW_COUNT NUMBER
17 ROW_SIZE_MAX NUMBER
18 ROW_SIZE_MIN NUMBER
19 ROW_SIZE_AVG NUMBER
20 BUILD_TIME NUMBER
21 LRU_NUMBER NUMBER
22 OBJECT_NO NUMBER
23 INVALIDATIONS NUMBER
24 SPACE_OVERHEAD NUMBER
25 SPACE_UNUSED NUMBER
26 CACHE_ID VARCHAR2(93)
27 CACHE_KEY VARCHAR2(93)
As we can see, there is a great deal of information potentially available. We will query a few important columns below.
SQL> SELECT name
2 , type
3 , cache_id
4 , row_count
5 FROM v$result_cache_objects
6 ORDER BY
7 creation_timestamp;
NAME TYPE CACHE_ID ROW_COUNT
------------------------------ ---------- -------------------------- ----------
SH.PRODUCTS Dependency SH.PRODUCTS 0
SH.SALES Dependency SH.SALES 0
SELECT /*+ RESULT_CACHE */ Result 091zc7mvn8ums36mbd2gqac4h0 71
SELECT p.prod_name Result 12scakxrxks3p73w5nxr69wn3j 71
SELECT DECODE('A','A','1','2' Result 0y8dgk314f9f8bz05qsrrny8u8 1
5 rows selected.
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.
SQL> SELECT *
2 FROM v$result_cache_statistics;
ID NAME VALUE
---------- --------------------------------- ---------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2048
3 Block Count Current 32
4 Result Size Maximum (Blocks) 102
5 Create Count Success 3
6 Create Count Failure 0
7 Find Count 4
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
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.
SQL> SELECT ro.id
2 , ro.name
3 , wm_concat(do.object_name) AS object_names
4 FROM v$result_cache_objects ro
5 LEFT OUTER JOIN
6 v$result_cache_dependency rd
7 ON (ro.id = rd.result_id)
8 LEFT OUTER JOIN
9 dba_objects do
10 ON (rd.object_no = do.object_id)
11 WHERE ro.type = 'Result'
12 GROUP BY
13 ro.id
14 , ro.name;
ID NAME OBJECT_NAMES
---------- -------------------------------------------------- ----------------
2 SELECT /*+ RESULT_CACHE */ SALES,PRODUCTS
p.prod_name
, SUM(s.amount_sold) AS total_revenue
, SUM(s.quantity_sold) AS total_
6 SELECT DECODE('A','A','1','2') FROM DUAL
7 SELECT p.prod_name SALES,PRODUCTS
, MAX(s.quantity_sold) AS max_sales
FROM sales s
, products p
WHERE s.prod_id = p.prod_id
GROUP
3 rows selected.
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.
SQL> set autotrace traceonly statistics
SQL> SELECT p.prod_name
2 , MAX(s.quantity_sold) AS max_sales
3 FROM sales s
4 , products p
5 WHERE s.prod_id = p.prod_id
6 GROUP BY
7 p.prod_name;
71 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1731 consistent gets
0 physical reads
0 redo size
2687 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71 rows processed
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.
SQL> SELECT id
2 , name
3 , type
4 , invalidations
5 , status
6 FROM v$result_cache_objects
7 ORDER BY
8 id;
ID NAME TYPE INVALIDATIONS STATUS
---- ------------------------------- ---------- ------------- ---------
1 SH.PRODUCTS Dependency 1 Published
0 SH.SALES Dependency 0 Published
6 SELECT DECODE('A','A','1','2') Result 0 Published
2 SELECT /*+ RESULT_CACHE */ Result 0 Invalid
7 SELECT p.prod_name Result 0 Invalid
10 SELECT p.prod_name Result 0 Published
6 rows selected.
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.
SQL> SELECT value
2 FROM v$result_cache_statistics
3 WHERE name = 'Find Count';
VALUE
---------------
6
1 row selected.
SQL> DECLARE
2 n PLS_INTEGER;
3 BEGIN
4 FOR i IN 1 .. 100 LOOP
5 SELECT COUNT(*) INTO n FROM channels;
6 END LOOP;
7 END;
8 /
PL/SQL procedure successfully completed.
We will now measure the Find Count again, as follows.
SQL> SELECT value
2 FROM v$result_cache_statistics
3 WHERE name = 'Find Count';
VALUE
---------------
105
1 row selected.
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.
SQL> SELECT name
2 , type
3 , row_count
4 FROM v$result_cache_objects
5 ORDER BY
6 creation_timestamp;
NAME TYPE ROW_COUNT
-------------------------------------------------- ---------- ----------
SELECT /*+ RESULT_CACHE */ Result 71
SH.PRODUCTS Dependency 0
SH.SALES Dependency 0
SELECT p.prod_name Result 71
SELECT DECODE('A','A','1','2') FROM DUAL Result 1
SELECT p.prod_name Result 71
SH.CHANNELS Dependency 0
SELECT /*+ RESULT_CACHE */ COUNT(*) FROM CHANNELS Result 1
8 rows selected.
Hence there is no difference for SQL embedded in PL/SQL programs.
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.
SQL> ALTER SESSION SET result_cache_mode = FORCE;
Session altered.
SQL> DECLARE
2
3 TYPE id_ntt IS TABLE OF products.prod_id%TYPE;
4 nt_ids id_ntt := id_ntt(40,41,42,43);
5
6 v_name products.prod_name%TYPE;
7
8 BEGIN
9 FOR i IN 1 .. 10 LOOP
10 FOR ii IN 1 .. nt_ids.COUNT LOOP
11
12 SELECT prod_name INTO v_name
13 FROM products
14 WHERE prod_id = nt_ids(ii);
15
16 END LOOP;
17 END LOOP;
18 END;
19 /
PL/SQL procedure successfully completed.
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.
SQL> SELECT name
2 , type
3 , row_count
4 FROM v$result_cache_objects
5 ORDER BY
6 creation_timestamp;
NAME TYPE ROW_COUNT
------------------------------------------------------- ---------- ----------
SELECT /*+ RESULT_CACHE */ Result 71
<< ...snip... >>
SH.CHANNELS Dependency 0
SELECT /*+ RESULT_CACHE */ COUNT(*) FROM CHANNELS Result 1
SELECT PROD_NAME FROM PRODUCTS WHERE PROD_ID = :B1 Result 1
SELECT PROD_NAME FROM PRODUCTS WHERE PROD_ID = :B1 Result 1
SELECT PROD_NAME FROM PRODUCTS WHERE PROD_ID = :B1 Result 1
SELECT PROD_NAME FROM PRODUCTS WHERE PROD_ID = :B1 Result 1
12 rows selected.
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.
SQL> ALTER SESSION SET result_cache_mode = FORCE;
Session altered.
SQL> DECLARE
2 v_first_name customers.cust_first_name%TYPE;
3 BEGIN
4 FOR i IN 1 .. 2 LOOP
5 FOR r IN (SELECT cust_id FROM customers) LOOP
6 SELECT cust_first_name INTO v_first_name
7 FROM customers
8 WHERE cust_id = r.cust_id;
9 END LOOP;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
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.
SQL> SELECT MIN(name) AS min_name
2 , MAX(name) AS max_name
3 , COUNT(*) AS cache_entries
4 FROM v$result_cache_objects
5 WHERE type = 'Result';
MIN_NAME MAX_NAME CACHE_ENTRIES
------------------------------ ------------------------------ -------------
SELECT CUST_FIRST_NAME FROM CU SELECT CUST_FIRST_NAME FROM CU 2035
STOMERS WHERE CUST_ID = :B1 STOMERS WHERE CUST_ID = :B1
1 row selected.
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.
SQL> SELECT *
2 FROM v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------- ---------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2048
3 Block Count Current 2048
4 Result Size Maximum (Blocks) 102
5 Create Count Success 111011
6 Create Count Failure 0
7 Find Count 141
8 Invalidation Count 2
9 Delete Count Invalid 4
10 Delete Count Valid 108972
10 rows selected.
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 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.
SQL> exec :ts := TO_CHAR(TRUNC(SYSDATE,'HH'),'YYYYMMDDHH24MISS');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> SELECT MIN(prod_id)
2 FROM products AS OF TIMESTAMP TO_TIMESTAMP(:ts,'YYYYMMDDHH24MISS');
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1489483397
--------------------------------------------------------------------------- ... --
| Id | Operation | Name | Rows | ... |
--------------------------------------------------------------------------- ... --
| 0 | SELECT STATEMENT | | 1 | ... |
| 1 | RESULT CACHE | 4vff36vw5vmn32gftq4a5qfpxh | | ... |
| 2 | SORT AGGREGATE | | 1 | ... |
| 3 | INDEX FULL SCAN (MIN/MAX)| PRODUCTS_PK | 72 | ... |
--------------------------------------------------------------------------- ... --
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; attributes=(single-row); parameters=(:TS); name="SELECT MIN(prod_id)
FROM products AS OF TIMESTAMP TO_TIMESTAMP(:ts,'YYYYMMDDHH24MISS')"
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
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');
1 row selected.
<< ...plan removed... >>
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
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.
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> DECLARE
2
3 TYPE id_ntt IS TABLE OF products.prod_id%TYPE;
4 nt_ids id_ntt := id_ntt(40,41,42,43,44,45,46,47);
5
6 v_name products.prod_name%TYPE;
7
8 BEGIN
9 FOR i IN 1 .. 125000 LOOP
10 FOR ii IN 1 .. nt_ids.COUNT LOOP
11
12 SELECT prod_name INTO v_name
13 FROM products
14 WHERE prod_id = nt_ids(ii);
15
16 END LOOP;
17 END LOOP;
18 END;
19 /
PL/SQL procedure successfully completed.
We will now repeat the example but cache the eight different cursors, as follows.
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> DECLARE
2
3 TYPE id_ntt IS TABLE OF products.prod_id%TYPE;
4 nt_ids id_ntt := id_ntt(40,41,42,43,44,45,46,47);
5
6 v_name products.prod_name%TYPE;
7
8 BEGIN
9 FOR i IN 1 .. 125000 LOOP
10 FOR ii IN 1 .. nt_ids.COUNT LOOP
11
12 SELECT prod_name INTO v_name
13 FROM products
14 WHERE prod_id = nt_ids(ii);
15
16 END LOOP;
17 END LOOP;
18 END;
19 /
PL/SQL procedure successfully completed.
We will now report the major differences between the two approaches, as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 4184 hsecs
Run2 ran in 4059 hsecs
Run1 ran in 103.08% of the time
Name Run1 Run2 Diff
STAT..session pga memory -327,680 65,536 393,216
STAT..calls to kcmgrs 1,000,000 8 -999,992
STAT..index fetch by key 1,000,002 10 -999,992
STAT..rows fetched via callbac 1,000,002 10 -999,992
STAT..table fetch by rowid 1,000,002 10 -999,992
LATCH.shared pool simulator 97 1,000,090 999,993
LATCH.cache buffers chains 2,000,007 23 -1,999,984
STAT..consistent gets 2,000,006 22 -1,999,984
STAT..consistent gets - examin 2,000,006 22 -1,999,984
STAT..consistent gets from cac 2,000,006 22 -1,999,984
STAT..session logical reads 2,000,006 22 -1,999,984
LATCH.Result Cache: Latch 0 2,000,017 2,000,017
STAT..buffer is not pinned cou 3,000,004 28 -2,999,976
Run1 latches total versus run2 -- difference and pct
Run1 Run2 Diff Pct
3,003,103 4,003,090 999,987 75.02%
PL/SQL procedure successfully completed.
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.
SQL> CREATE VIEW v_products
2 AS
3 SELECT *
4 FROM products;
View created.
SQL> SELECT * FROM v_products;
<< ...snip... >>
72 rows selected.
We can use the cached results of PRODUCTS as follows. Note that we
are gathering plan statistics to investigate whether this technique
leads to an I/O-free join to PRODUCTS.
SQL> SELECT
2 p.prod_name
3 , AVG(s.quantity_sold) AS avg_sales
4 FROM sales s
5 , (SELECT * FROM v_products) p
6 WHERE s.prod_id = p.prod_id
7 GROUP BY
8 p.prod_name;
<< ...snip... >>
71 rows selected.
We will query the DBMS_XPLAN.DISPLAY_CURSOR function to view the rowsource plan for our example query, as follows.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'RUNSTATS_LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID 33wh37bxzhf26, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ p.prod_name ,
AVG(s.quantity_sold) AS avg_sales FROM sales s , (SELECT * FROM
v_products) p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name
Plan hash value: 1128805752
----------------------------------------------------------------------------------------- ... -----------
| Id | Operation | Name | Starts | E-Rows | A-Rows | ... | Buffers |
----------------------------------------------------------------------------------------- ... -----------
| 1 | HASH GROUP BY | | 1 | 71 | 71 | ... | 1727 |
|* 2 | HASH JOIN | | 1 | 72 | 72 | ... | 1727 |
| 3 | VIEW | VW_GBC_5 | 1 | 72 | 72 | ... | 1727 |
| 4 | HASH GROUP BY | | 1 | 72 | 72 | ... | 1727 |
| 5 | PARTITION RANGE ALL| | 1 | 918K| 918K| ... | 1727 |
| 6 | TABLE ACCESS FULL | SALES | 28 | 918K| 918K| ... | 1727 |
| 7 | VIEW | V_PRODUCTS | 1 | 72 | 72 | ... | 0 |
| 8 | RESULT CACHE | dm772rv5jrfhpf7019qp8srvzu | 1 | | 72 | ... | 0 |
| 9 | TABLE ACCESS FULL | PRODUCTS | 0 | 72 | 0 | ... | 0 |
----------------------------------------------------------------------------------------- ... -----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="V_PRODUCTS"."PROD_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
8 -
32 rows selected.
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.
SQL> SELECT
2 p.prod_name
3 , AVG(s.quantity_sold) AS avg_sales
4 FROM sales s
5 , v_products p
6 WHERE s.prod_id = p.prod_id
7 GROUP BY
8 p.prod_name;
<< ...snip... >>
71 rows selected.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'RUNSTATS_LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID 78nw9vr6vwg7n, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ p.prod_name ,
AVG(s.quantity_sold) AS avg_sales FROM sales s , v_products p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name
Plan hash value: 1128805752
----------------------------------------------------------------------------------------- ... -----------
| Id | Operation | Name | Starts | E-Rows | A-Rows | ... | Buffers |
----------------------------------------------------------------------------------------- ... -----------
| 1 | HASH GROUP BY | | 1 | 71 | 71 | ... | 1731 |
|* 2 | HASH JOIN | | 1 | 72 | 72 | ... | 1731 |
| 3 | VIEW | VW_GBC_5 | 1 | 72 | 72 | ... | 1727 |
| 4 | HASH GROUP BY | | 1 | 72 | 72 | ... | 1727 |
| 5 | PARTITION RANGE ALL| | 1 | 918K| 918K| ... | 1727 |
| 6 | TABLE ACCESS FULL | SALES | 28 | 918K| 918K| ... | 1727 |
| 7 | VIEW | V_PRODUCTS | 1 | 72 | 72 | ... | 4 |
| 8 | RESULT CACHE | 1b3305bb7rs5sdmdydr6u8sb49 | 1 | | 72 | ... | 4 |
| 9 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 | 72 | ... | 4 |
----------------------------------------------------------------------------------------- ... -----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="P"."PROD_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
8 -
32 rows selected.
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).
SQL> BEGIN
2 DBMS_RESULT_CACHE.FLUSH;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v$result_cache_objects;
no rows selected
SQL> SELECT * FROM v$result_cache_statistics;
ID NAME VALUE
---------- -------------------------------------------------- ---------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2048
3 Block Count Current 0
4 Result Size Maximum (Blocks) 102
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
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.
