|
11g QRC (Query Result Cache)
A separate shared memory pool is now used for storing and retrieving cached
results. Query retrieval from the query result cache is faster than rerunning
the query. Frequently executed queries will see performance improvements when
using the query result cache.
The new query result cache enables explicit caching of results in database
memory. Subsequent queries using the cached results will experience significant
performance improvements.
The SQL query result cache is an area of memory in the Shared Global Area (SGA)
that can retain the result sets that a query generates.
Result of queries and query fragments can be cached in the SQL query result
cache (within the shared pool). The database can then use cached results to
answer future executions of these queries and query fragments. Because
retrieving results from the SQL query result cache is faster than rerunning a
query, frequently run queries experience a significant performance improvement
when their results are cached. Users can annotate a query or query fragment with
a result cache hint to indicate that results are to be stored in the SQL query
result cache. You can set the RESULT_CACHE_MODE initialization parameter to
control whether the SQL query result cache is used for all queries (when
possible), or only for queries that are annotated. The database automatically
invalidates a cached result whenever a transaction modifies the data or metadata
of any of the database objects used to construct that cached result.
Function Result Cache on PL/SQL
PL/SQL function is sometimes used to return the result of a computation whose
inputs are one or several parameterized queries issued by the function. In some
cases, these queries access data (for example, the catalog of wares in a
shopping application) that changes very infrequently compared to the frequency
of calling the function. You can include syntax in the source text of a PL/SQL
function to request that its results be cached and, to ensure correctness, that
the cache be purged when any of a list of tables experiences DML. The cache,
like the SQL query result cache, is in the shared pool. The DBA manages and
monitors both result caches using common APIs. The look-up key for the cache is
the combination of actual arguments with which the function is invoked. When a
particular invocation of the result-cached function is a cache hit, then the
function body is not executed; instead, the cached value is returned
immediately.
Oracle 11g allows the results of SQL queries to be cached in the SGA and reused
to improve performance. Set up the following schema objects to see how the SQL
query cache works.
CREATE TABLE QueryResultCacheTab (
id NUMBER
);
INSERT INTO QueryResultCacheTab VALUES (1);
INSERT INTO QueryResultCacheTab VALUES (2);
INSERT INTO QueryResultCacheTab VALUES (3);
CREATE OR REPLACE FUNCTION QRC_function(p_id IN QueryResultCacheTab.id%TYPE)
RETURN QueryResultCacheTab.id%TYPE DETERMINISTIC AS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
/
SET TIMING ON
The function contains a one second sleep so we can easily detect if it has been
executed by checking the elapsed time of the query.
SELECT qrc_function(id) FROM QueryResultCacheTab;
QRC_FUNCTION(ID)
-----------------
1
2
3
QRC - Adding the RESULT_CACHE hint to the query tells the server to attempt to
retrieve the information from the result cache. If the information is not
present, it will cache the results of the query provided there is enough room in
the result cache. Since we have no cached results, we would expect the first run
to take approximately two seconds, but subsequent runs to be much quicker.
SELECT /*+ result_cache */ qrc_function(id) FROM QueryResultCacheTab;
QRC_FUNCTION(ID)
-----------------
1
2
3
3 rows selected
Elapsed: 00:00:02.20
SELECT /*+ result_cache */ qrc_function(id) FROM QueryResultCacheTab;
QRC_FUNCTION(ID)
-----------------
1
2
3
3 rows selected.
Elapsed: 00:00:00.05
For QRC the default action of the result cache is controlled by the
RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint
must be used for a query to access the result cache.
SHOW PARAMETER RESULT_CACHE_MODE
NAME
TYPE
VALUE
------------------------------------ -----------
------------
result_cache_mode
string
MANUAL
SQL>
Adding or set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used
by default, but we can bypass it using the NO_RESULT_CACHE hint.
|
