Database Reference
In-Depth Information
| 8 | TABLE ACCESS FULL | CUSTOMERS |
0
| 0 |
| 9 | PARTITION RANGE ALL| |
0
| 0 |
| 10 | TABLE ACCESS FULL | SALES |
0
| 0 |
----------------------------------------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
7 - access("S"."CUST_ID"="C"."CUST_ID")
This time, the
Starts
column in the execution plan shows that none of the operations were executed, except
for
RESULT CACHE
. Note that this was one of those cases mentioned in Chapter 10 where a row source operation can
completely avoid calling its child operation because it doesn't require it to fulfill its work. In other words, the result set
for the query is served directly from the result cache.
In the execution plan, it's also interesting to note that a name, the
cache ID
, is associated with the
RESULT CACHE
operation. If you know the cache ID, you can query the
v$result_cache_objects
view to display information about
the cached data. The following query shows that the cached result set has been published (in other words, available
for use), when the result cache was created, how much time (in hundredths of seconds) it took to build it, how many
rows are stored in it, and how many times it has been referenced:
SQL> SELECT status, creation_timestamp, build_time, row_count, scan_count
2 FROM v$result_cache_objects
3 WHERE cache_id = '089x05gkvfuxq7wqg06u9z0zkb';
STATUS CREATION_TIMESTAMP BUILD_TIME ROW_COUNT SCAN_COUNT
--------- ------------------ ---------- --------- ----------
Published 2013-12-11 10:27 95 81 2
Other views that provide information about the result cache are
v$result_cache_dependency
,
v$result_cache_
memory
, and
v$result_cache_statistics
.
From version 11.2 onward, specifying the
result_cache
hint isn't the only available way to enable the result
cache. Another technique is to specify at the table level, with the
result_cache
clause set to
force
, that the result set
of all queries referencing the table have to be cached (unless the
no_result_cache
hint is specified). Note that the
default mode of the
result_cache
clause is
default
. This technique is especially useful for tables containing read-
mostly data. In fact, thanks to this technique, it's not necessary to change the application to take advantage of the
result cache. All you need to do is to specify at the table level that the result cache should be used.
Note that when several tables are referenced in a single query, all tables must have the
result_cache
clause set
to
force
to enable the result cache. The following SQL statements, which are an excerpt of the
rc_query_table.sql
script, show how to enable the result cache for the three tables used in the queries used as examples in this section:
SQL> ALTER TABLE sales RESULT_CACHE (MODE FORCE);
SQL> ALTER TABLE customers RESULT_CACHE (MODE FORCE);
SQL> ALTER TABLE products RESULT_CACHE (MODE FORCE);
To guarantee the consistency of the result sets (that is, that the result set is the same whether it's served from the
result cache or calculated from the database content), every time something changes in the tables referenced by a
query, the cache entries dependent on it are invalidated (an exception with remote objects is discussed shortly). This
is the case even if no real changes occur. For example, even a
SELECT FOR UPDATE
, immediately followed by a
COMMIT
,
leads to the invalidation of the cache entries that depend on the selected table. It means that invalidations take place
when a table with dependent cache entries is involved in a transaction, not when the data which the cache entries
