Oracle result_cache
Systems with large amounts of memory can take advantage of the result cache to improve response times of repetitive queries.
The result cache stores the results of SQL queries and PL/SQL functions in an area called Result Cache Memory in the shared pool.
When these queries and functions are executed repeatedly, the results are retrieved directly from the cache memory. This results in a faster response time.
The cached results stored become invalid when data in the dependent database objects is modified.
Setting Result_Cache_Max_Size
Set Result_Cache_Max_Size from the command line or in an initialization file by a DBA.
SQL> show parameter result_cache_max_sizeresult_cache_max_size big integer 2112K or use following statement
SQL> SELECT name, value
FROM v$parameter
WHERE name = 'result_cache_max_size';
Enabling Query Result Cache
Use the RESULT_CACHE_MODE initialization parameter in the database initialization parameter file.
RESULT_CACHE_MODE can be set to
MANUAL (default): You must add the RESULT_CACHE hint to your queries for the results to be cached.
FORCE: Results are always stored in the Result Cache Memory, if possible.
Using the DBMS_RESULT_CACHE Package
You can use the DBMS_RESULT_CACHE package to perform various operations such as bypassing the cache, retrieving statistics on the cache memory usage, and flushing the cache. For example, to view the memory allocation statistics, use dbms_result_cache.memory_report.
The output of this command is similar to the following:
SQL> execute dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2112K bytes (2112 blocks)
Maximum Result Size = 105K bytes (105 blocks)
[Memory]
Total Memory = 5140 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL Query Result Cache
Definition:
Cache the results of the current query in memory, and then use the cached results in future executions of the query.
Cached results reside in the result cache memory portion of the SGA.
Benefits:
Improved performance
Usage
SQL result caching is useful when your queries need to analyze a large number of rows to return a small number of rows or a single row.
In oracle11g two new optimizer hints are available to turn on and turn off SQL result caching:
/*+ result_cache */
/*+ no_result_cache */
These hints enable you to override the settings of the RESULT_CACHE_MODE initialization parameter.
Clearing the Shared Pool and Result Cache
To understand the use of Query Result Cache, ensure that you are using clean, new data. Connect to the database as SYS.
Clear the shared pool and the result cache by executing the code as shown below.
SQL> connect / as sysdba
SQL> SET PAGESIZE 1000
SQL> SET SERVEROUTPUT ON
SQL> execute dbms_result_cache.flush
SQL> alter system flush shared_pool;
SQL> execute dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2112K bytes (2112 blocks)
Maximum Result Size = 105K bytes (105 blocks)
[Memory]
Total Memory = 5140 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
Managing RESULT_CACHE manually
connect sh/sh
SQL> set autotrace traceonly
SQL> set timing on
SQL> SELECT /*+ RESULT_CACHE */ p.prod_name, SUM(s.amount_sold) AS total_revenue, SUM(s.quantity_sold) AS total_sales
FROM sales s,products p
WHERE s.prod_id = p.prod_id
GROUP BY
p.prod_name;
71 rows selected.
Elapsed: 00:00:06.71
We can summarize above output 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 tellings 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 dependent on (i.e. to maintain cache coherency) and the leading part of the SQL text that generated the results.
With respect to statistics we can see a range of I/O and CPU activity as expected. We will now test the new query result cache by running the same query a second time and comparing the Auto trace report, as follows.
Execution plan remains but you could observe changes in statistics.
Example – 2
Example
CREATE TABLE qrc_tab (id NUMBER);
INSERT INTO qrc_tab VALUES (1);
INSERT INTO qrc_tab VALUES (2);
INSERT INTO qrc_tab VALUES (3);
INSERT INTO qrc_tab VALUES (4);
INSERT INTO qrc_tab VALUES (5);
CREATE OR REPLACE FUNCTION slow_function(p_id IN rc_tab.id%TYPE)
RETURN qrc_tab.id%TYPE
AS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
/
SET TIMING ON
Next, we query the test table using the slow function and check out the elapsed time. Each run takes approximately five seconds, one second sleep for each row queried.
SELECT slow_function(id) FROM qrc_tab;
. . . . .
5 rows selected.
Elapsed: 00:00:05.15
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 five seconds, but subsequent runs to be much quicker.
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
. . . .
Elapsed: 00:00:05.20
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
. . . . .
Elapsed: 00:00:00.15
If we 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.
ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
SELECT slow_function(id) FROM qrc_tab;
. . . . .
Elapsed: 00:00:00.14
SELECT /*+ no_result_cache */ slow_function(id) FROM qrc_tab;
. . . . .
Elapsed: 00:00:05.14
PL/SQL Function Result Cache
Function Result Cache
PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA.
These results can be reused by any session calling the same function with the same parameters.
This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL.
Enabling a function to use the function result cache is as simple as adding the RESULT_CACHE clause, and optionally the RELIES_ON clause.
CREATE TABLE res_cache_test_tab (
id NUMBER,
value NUMBER
);
Insert data
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO res_cache_test_tab VALUES (i, i*10);
END LOOP;
COMMIT;
END;
/
The following function returns the VALUE from the test table for the specified ID.It also includes a call to the DBMS_LOCK.SLEEP procedure to slow down the function.
CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
RETURN NUMBER
RESULT_CACHE
AS
l_value res_cache_test_tab.value%TYPE;
BEGIN
SELECT value INTO l_value
FROM res_cache_test_tab WHERE id = p_in;
DBMS_LOCK.sleep(1);
RETURN l_value;
END get_value;
/
The following procedure tests the performance of the get_value function by making calls to it in two loops and measuring the elapsed time for each run.
CREATE OR REPLACE PROCEDURE run_test (p_value in integer)
AS
l_start NUMBER;
l_loops NUMBER := 10;
l_value res_cache_test_tab.value%TYPE;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
l_value := get_value(i);
END LOOP;
DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time -
l_start) || ' hsecs');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
l_value := get_value(i);
END LOOP;
DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time -
l_start) || ' hsecs');
END run_test;
/
exec run_test
set serveroutput on
exec run_test(10)
First Loop: 1012 hsecs
Second Loop: 0 hsecs
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.20
exec run_test(10)
First Loop: 0 hsecs
Second Loop: 0 hsecs
If we alter the contents of the table, we still get the fast elapsed time, indicating the existing values are still being used.
BEGIN
FOR i IN 11 .. 20 LOOP
INSERT INTO res_cache_test_tab VALUES (i, i*10);
END LOOP;
COMMIT;
END;
/
This represents a potential data integrity problem.
In oracle11g release 1 RELIES_ON clause is used to specify dependent
tables and views so the result cache can be invalidated if the dependent
objects are modified.
CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (res_cache_test_tab)
AS
l_value res_cache_test_tab.value%TYPE;
BEGIN
SELECT value INTO l_value
FROM res_cache_test_tab WHERE id = p_in;
DBMS_LOCK.sleep(1);
RETURN l_value;
END get_value;
/
After recreating the function, we see a normal first-time run, followed by a run using the cached results.
No comments:
Post a Comment