-- Function results are cached when same input parameters are used 
-- using the Oracle 11g Result_Cache feature.  Use of this feature 
-- results in saving of significant space and time when the cache is
-- system-wide.  In multi-user applications better response times
-- are achieved and less memory usage. Applications that implement 
-- session-private scheme results in improved scalability.
-- Recalculation occurs on i/o changes can be defined to be 
-- dependent on table/object by the RELIES_ON(object) option.

CREATE OR REPLACE PACKAGE sales_rpt_pkg IS

  TYPE rec_sales_sum  IS RECORD
    (division_id          NUMBER,
     total_quantity       NUMBER,
     sales_sum            NUMBER,
     invoice_year         NUMBER,
     trans_count          NUMBER);

  FUNCTION get_sales (div_id IN edw_sales_fact.division_id%TYPE)
   RETURN rec_sales_sum
  RESULT_CACHE;

END sales_rpt_pkg;
/

CREATE OR REPLACE PACKAGE BODY sales_rpt_pkg AS

  FUNCTION get_sales (div_id IN edw_sales_fact.division_id%TYPE)
  RETURN rec_sales_sum
  RESULT_CACHE RELIES_ON (edw_sales_fact)
  AS
    v_rec  rec_sales_sum;
  BEGIN
    SELECT
      division_id,
      SUM(quantity),
      SUM(sales_amt),
      TO_CHAR(invoice_date,'yyyy'),
      COUNT(DISTINCT invoice_id)
    INTO v_rec
    FROM edw_sales_fact
    WHERE division_id = div_id
    GROUP BY
     division_id, TO_CHAR(invoice_date,'yyyy');
  RETURN v_rec;
  END get_sales;

END sales_rpt_pkg;
/

-- Testing the code with RESULT_CACHE RELIES_ON (edw_sales_fact)
-- feature.  The performance analysis can be performed better
-- when data volumes are large.

DECLARE
  div_id     SIMPLE_INTEGER := 100;
  sum_qty    NUMBER;
  sum_sales  NUMBER;
  trans_cnt  NUMBER;
BEGIN
  sum_qty := sales_rpt_pkg.get_sales(div_id).total_quantity;
  sum_sales := sales_rpt_pkg.get_sales(div_id).sales_sum;
  trans_cnt := sales_rpt_pkg.get_sales(div_id).trans_count;
  DBMS_OUTPUT.PUT_LINE('Total Quantity    = ' ||sum_qty);
  DBMS_OUTPUT.PUT_LINE('Total Sales       = ' ||sum_sales);
  DBMS_OUTPUT.PUT_LINE('Transaction Count = ' ||trans_cnt);
END;
/



-- DBMS_RESULT_CACHE package can be used by the DBA to manage the 
-- shared pool that is used by the SQL result cache and the PL/SQL 
-- function cache.  When hot patching PL/SQL code that uses 
-- result_cache feature,  cached results flusing may not occur 
-- automatically. The flusing can be done by code shown below.

BEGIN
  DBMS_RESULT_CACHE.Bypass(TRUE);
  DBMS_RESULT_CACHE.Flush;
END;
/

-- Caching can be enabled by executing the code below.  In RAC 
-- implementation, flusing, patching and enabling have to done on all 
-- database instances.

BEGIN
  DBMS_RESULT_CACHE.Bypass(FALSE);
END;
/

-- The following views are useful to analyze the result_cache 
-- performance:
-- [G]V$RESULT_CACHE_STATISTICS
-- [G]V$RESULT_CACHE_MEMORY
-- [G]V$RESULT_CACHE_OBJECTS
-- [G]V$RESULT_CACHE_DEPENDENCY