Thursday, July 21, 2016

Oracle result_cache

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.

 Increasing Result Cache Memory Size
You can increase the small, default result cache memory size by using the RESULT_CACHE_MAX_SIZE initialization parameter.

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_size
result_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