Friday, December 10, 2010

Basics of PL/SQL Result Cache feature in 11g

In which case it can be useful?

Imagine you are selecting from a list of 200,000 orders related to 100 products references and you want to use a standard product reference description from a function get_product_desc(p_prod_code) with :
SELECT ord_ref,get_product_desc(prod_code) as prod_std_ref,ord_qty,ord_price FROM ORDERS_LINES.

Without RESULT CACHE feature, you will have 200,000 executions of the get_product_desc function.
With RESULT_CACHE enabled, the function will be executed only 100 times, results being kept in the result cache memory.


Related system PARAMETERS:

RESULT_CACHE_MAX_SIZE initialization parameter, by default set to a small size of the shared pool.
You can change the size with the alter system command:
Ex: ALTER SYSTEM SET result_cache_max_size = 4M SCOPE = MEMORY;

RESULT_CACHE_MODE initialization parameter, by default MANUAL, can be set to FORCE.


Related Package: DBMS_RESULT_CACHE

Ex: DBMS_RESULT_CACHE.FLUSH to clear cache. Useful when doing stats etc...
       DBMS_RESULT_CACHE.MEMORY_REPORT to dispay cache memory info.


QUERY PLAN

When Result cache is used we have new information in the query execution plan.


EXAMPLE

CREATE FUNCTION get_product_desc (
                  p_prod_code IN VARCHAR2 )
                  RETURN VARCHAR2 RESULT_CACHE IS

        prodname VARCHAR2(200);

BEGIN

         SELECT substr(prod_ref||’- ‘||prod_desc,1,200)
         INTO prodname
         FROM Products WHERE prod_code=p_prod_code;

         RETURN prodname;

END get_product_desc;


V$ VIEWS

V$RESULT_CACHE_OBJECTS.TYPE 
V$RESULT_CACHE_DEPENDENCY 
V$RESULT_CACHE_STATISTICS

Ex:
SELECT name, value FROM   v$result_cache_statistics
WHERE  name IN ('Create Count Success','Find Count');


CACHE data dependencies

To avoid cache inconsistency, in 11gR1 the "RELIES_ON" clause can be used, in 11gR2 no need for it, all done automagically. If clause is found it will be just ignored.
Good to note that in 11gR2, even dynamic SQL in the function (execute immediate) is managed as a dependency.