/*
|| Oracle 11g Results Caches, Part 1: SQL Query Result Caches
||
|| Demonstrates Oracle 11g SQL Query Results Cache features,
|| including:
|| - Creating SQL Query Result Caches in MANUAL mode
|| - Creating SQL Query Result Caches in FORCE mode
|| - Managing SQL Query Result Caches
|| - Querying Result Cache memory usage
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Results Caching, and they should be carefully proofread before being executed
|| executed against any existing Oracle database to avoid potential damage!
*/

/* 
|| Listing 1.1:
|| Demonstrates how Results Cache operates in MANUAL mode (the default)
*/

-----
-- Preparation:
-- 1. Flush the entire current contents of the Results Cache
-- 2.) Enable manual Result Cache mode
-- 3.) Set the maximum size of the Result Cache to just 1MB
-- 4.) Set the maximum single result cache usage to just 1% of the total
--     Result Cache (i.e. 10KB).
-----
BEGIN
    DBMS_RESULT_CACHE.FLUSH;
END;
/

ALTER SYSTEM SET result_cache_mode = MANUAL;
ALTER SYSTEM SET result_cache_max_size = 1M;
ALTER SYSTEM SET result_cache_max_result = 1;

-----
-- Execute a query and place its results into the SQL Results Cache
-----
SELECT /*+RESULT_CACHE SQRC_1.1*/ 
     promo_category_id
    ,promo_category
    ,SUM(promo_cost) total_cost
    ,ROUND(AVG(promo_cost),2) avg_cost
  FROM sh.promotions
 GROUP BY promo_category_id, promo_category
 ORDER BY promo_category_id, promo_category
;
-----
-- Show a detailed report of Result Cache memory usage
-----
SET SERVEROUTPUT ON
BEGIN
    DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => TRUE);
END;
/

-----
-- Show the results from EXPLAIN PLAN for the next execution of this query
-----
EXPLAIN PLAN FOR
SELECT /*+ RESULT_CACHE */ 
     promo_category_id
    ,promo_category
    ,SUM(promo_cost) total_cost
    ,ROUND(AVG(promo_cost),2) avg_cost
  FROM sh.promotions
 GROUP BY promo_category_id, promo_category
 ORDER BY promo_category_id, promo_category
;
SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));

/* 
|| Listing 1.2:
|| Illustrates how SQL Results Cache operates in FORCE mode
*/

-----
-- Preparation:
-- 1.) Enable manual SQL Result Cache mode
-- 2.) Set the maximum size of the SQL Result Cache to just 1MB
-- 3.) Set the maximum single result cache usage to just 1% of the total
--     SQL Result Cache (i.e. 10KB).
-----

ALTER SYSTEM SET result_cache_mode = FORCE;
ALTER SYSTEM SET result_cache_max_size = 20M;
ALTER SYSTEM SET result_cache_max_result = 50;

-----
-- Generate a query that will be forced into the SQL Query Results Cache
-----
SELECT /*SQRC_1.2*/ 
     vendor_id
    ,vendor_name
  FROM ap.vendors
;

EXPLAIN PLAN FOR
SELECT /*SQRC_1.2*/ 
     vendor_id
    ,name
  FROM ap.vendors
;
SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));


-----
-- Execute a query, but insure its results are +NOT+ placed into 
-- the SQL Results Cache (by using the NO_RESULT_CACHE hint)
-----


-----
-- Show a detailed report of Result Cache memory usage and results
-- of +not+ caching the query
-----
SET SERVEROUTPUT ON
BEGIN
    DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => TRUE);
END;
/

EXPLAIN PLAN FOR
SELECT /*+ NO_RESULT_CACHE */ 
     promo_category_id
	,promo_category
    ,SUM(promo_cost) total_cost
    ,ROUND(AVG(promo_cost),2) avg_cost
  FROM sh.promotions
 GROUP BY promo_category_id, promo_category
 ORDER BY promo_category_id, promo_category
;
SELECT * 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));

/* 
|| Listing 1.3:
|| "Flushing" the SQL Results Cache
*/

-----
-- Flush the entire contents of the Result Cache and all 
-- statistics (the default behavior)
-----
BEGIN
    DBMS_RESULT_CACHE.FLUSH;
END;
/

-----
-- Flush the Results Cache, but retain any current memory allocations
-- as well as statistics
-----
BEGIN
    DBMS_RESULT_CACHE.FLUSH(
	  retainMem => TRUE
        ,retainSta => TRUE
	);
END;
/

-----
-- Flush the Results Cache, but retain any current memory allocations
-----
BEGIN
    DBMS_RESULT_CACHE.FLUSH(
	  retainMem => TRUE
        ,retainSta => FALSE
	);
END;
/

-----
-- Flush the Results Cache, but retain any statistics
-----
BEGIN
    DBMS_RESULT_CACHE.FLUSH(
	  retainMem => FALSE
        ,retainSta => TRUE
	);
END;
/

/*
|| Invalidating Results Cache results returned
*/

-- Invalidate any results cached for a specific table
BEGIN
    DBMS_RESULT_CACHE.INVALIDATE(
         owner => 'SH'
        ,name => 'PROMOTIONS'
    );
END;
/

-- Invalidate any results cached for a specific Result Cache
BEGIN
    DBMS_RESULT_CACHE.INVALIDATE(
         object_id => 70943
    );
END;
/

-- Invalidate any results cached for a specific Result Cache
BEGIN
    DBMS_RESULT_CACHE.INVALIDATE_OBJECT(
         object_id => '89gqh0j9248q8d0w79w0fcwhw2'
    );
END;
/

SET PAGESIZE 1000
SET LINESIZE 150

/* 
|| Listing 1.4:
|| Querying SQL Result Cache metadata
*/

-----
-- View:    V$RESULT_CACHE_STATISTICS
-- Purpose: Displays statistics about current usage of SQL Results Cache
-----
TTITLE 'Current SQL Result Cache Statistics|(From V$RESULT_CACHE_STATISTICS)'
COL name    FORMAT A30      HEADING	'Name'
COL value	FORMAT 999999   HEADING 'Value'
SELECT 
     name
    ,value
  FROM v$result_cache_statistics
 ORDER BY name
;
TTITLE OFF

-----
-- View:    V$RESULT_CACHE_OBJECTS
-- Purpose: Shows which objects have at least one row cached within 
--          the SQL Results Cache
-----
TTITLE 'Objects Currently Cached in SQL Results Cache|(From V$RESULT_CACHE_OBJECTS)'
COL type            FORMAT A11      HEADING	'Cache|Type' WRAP
COL status          FORMAT A09      HEADING	'Status'
COL create_dtm      FORMAT A11      HEADING	'Created On' WRAP
COL bucket_no       FORMAT 99999    HEADING	'Bckt|#'
COL hashvalue       FORMAT A12      HEADING	'Hash Value'
COL name     	    FORMAT A20		HEADING 'Cache Name'
COL invalidations	FORMAT 9999999	HEADING	'Invali-|dations'
COL space_overhead	FORMAT 9999999  HEADING 'Space|Over|Head'
COL space_unused	FORMAT 9999999  HEADING 'Unused|Space'
COL cacheid         FORMAT A20		HEADING 'Cache ID' WRAP 
COL cachekey    	FORMAT A20      HEADING 'Cache Key' WRAP
SELECT 
     type
    ,status
    ,bucket_no
    ,SUBSTR(hash,1,40) hashvalue
    ,name
    ,TO_CHAR(creation_timestamp, 'yyyy-mm-dd hh24:mi:ss') create_dtm
    ,invalidations
    ,space_overhead
    ,space_unused
    ,SUBSTR(cache_id, 1, 40) cacheid
    ,SUBSTR(cache_key, 1, 40) cachekey
  FROM v$result_cache_objects
;
TTITLE OFF

TTITLE 'Object Statistics for Current SQL Results Cache|(From V$RESULT_CACHE_OBJECTS)'
COL type            FORMAT A11      HEADING	'Cache|Type' WRAP
COL status          FORMAT A09      HEADING	'Status'
COL hashvalue       FORMAT A12      HEADING	'Hash Value'
COL name     	    FORMAT A20		HEADING 'Cache Name'
COL object_no       FORMAT 9999999  HEADING 'OID'
COL depend_count    FORMAT 9999     HEADING 'Dpnd|Cnt'
COL block_count     FORMAT 9999     HEADING 'Blk|Cnt'
COL column_count    FORMAT 9999     HEADING 'Col|Cnt'
COL scn             FORMAT 9999999  HEADING 'Build|Inval|SCN'
COL pin_count       FORMAT 9999     HEADING 'Pin|Cnt'
COL scan_count      FORMAT 9999     HEADING 'Scan|Cnt'
COL row_count       FORMAT 9999     HEADING 'Row|Cnt' 
COL row_size_max    FORMAT 9999     HEADING 'Max|Row|Size'
COL row_size_min    FORMAT 9999     HEADING 'Min|Row|Size'
COL row_size_avg    FORMAT 9999     HEADING 'Avg|Row|Size'
COL build_time      FORMAT 99999    HEADING 'Cache|Build|Time|(cs)'
COL lru_number      FORMAT 99999    HEADING 'Place|In|LRU|List'
COL invalidations	FORMAT 9999  	HEADING	'Invl'
SELECT
     type
    ,status
    ,SUBSTR(hash,1,128) hashvalue
    ,name
    ,object_no
    ,depend_count
    ,block_count
    ,column_count
    ,scn
    ,pin_count
    ,scan_count
    ,row_count
    ,row_size_max
    ,row_size_min
    ,row_size_avg
    ,build_time
    ,lru_number
    ,invalidations
  FROM v$result_cache_objects
;
TTITLE OFF

-----
-- View:    V$RESULT_CACHE_MEMORY
-- Purpose: Monitors the usage of SQL Results Cache Memory
-----
TTITLE 'SQL Results Cache Memory Contents|(From V$RESULT_CACHE_MEMORY)'
COL chunk           FORMAT 9999999  HEADING	'Chunk|#'
COL offset          FORMAT 9999999  HEADING	'Offset|#'
COL free            FORMAT A05      HEADING	'Free?'
COL position        FORMAT 9999999  HEADING	'Position'
SELECT 
     chunk
    ,offset
    ,free
    ,position
  FROM v$result_cache_memory
;
TTITLE OFF

-----
-- View:    V$RESULT_CACHE_DEPENDENCY
-- Purpose: Lists which database objects a SQL Results Cache is dependent
--          upon for returning values
-----
TTITLE 'SQL Results Cached Object Dependencies|(From V$RESULT_CACHE_DEPENDENCY)'
COL result_id       FORMAT 9999999  HEADING	'Result|ID#'
COL depend_id       FORMAT 9999999  HEADING	'Depends|On'
COL owner           FORMAT A08      HEADING	'Schema'
COL object_name     FORMAT A30      HEADING	'Object Name'
SELECT 
     RCD.result_id
    ,RCD.depend_id
	,DBO.owner
    ,DBO.object_name
  FROM 
     v$result_cache_dependency RCD
    ,dba_objects DBO
 WHERE DBO.object_id = RCD.object_no
;
TTITLE OFF

/* 
|| Listing 1.5:
|| Results of querying view V$RESULTS_CACHE_OBJECTS
*/

Mon Jul 21                                                                                                                                   page    1
                                                    Objects Currently Cached in SQL Results Cache
                                                            (From V$RESULT_CACHE_OBJECTS)

                                                                                       Space
Cache                   Bckt                                                Invali-     Over   Unused
Type        Status         # Hash Value   Cache Name           Created On   dations     Head    Space Cache ID             Cache Key
----------- --------- ------ ------------ -------------------- ----------- -------- -------- -------- -------------------- --------------------
Dependency  Published   2776 277818072    AP.VENDORS           2008-07-21         0        0        0 AP.VENDORS           AP.VENDORS
                                                               22:11:45

Dependency  Published   1414 1245660550   SH.PROMOTIONS        2008-07-21         0        0        0 SH.PROMOTIONS        SH.PROMOTIONS
                                                               22:06:57

Result      Published   3815 1990516455   SELECT /*SQRC_1.2*/  2008-07-21         0      291      607 89gqh0j9248q8d0w79w0 836vrmmmkcwd0brg8br1
                                               vendor_id       22:11:45                               fcwhw2               0xhc4p
                                              ,name
                                            FROM ap.vendors

Result      Published   3826 2172407538   SELECT /*+RESULT_CAC 2008-07-21         0      308      493 b89anjnr81cz08a2zguh d5us9nzrmnj166zg0fgh
                                          HE SQRC_1.1*/        22:06:57                               wxq3cu               w1z03d
                                               promo_category_
                                          id
                                              ,promo_category
                                              ,SUM(promo_cost)
                                           total_cost
                                              ,ROUND(AVG(prom


Mon Jul 21                                                                                                                                   page    1
                                                   Object Statistics for Current SQL Results Cache
                                                            (From V$RESULT_CACHE_OBJECTS)

                                                                                                                                 Cache  Place
                                                                                      Build                     Max   Min   Avg  Build     In
Cache                                                             Dpnd   Blk   Col    Inval   Pin  Scan   Row   Row   Row   Row   Time    LRU
Type        Status    Hash Value   Cache Name                OID   Cnt   Cnt   Cnt      SCN   Cnt   Cnt   Cnt  Size  Size  Size   (cs)   List  Invl
----------- --------- ------------ -------------------- -------- ----- ----- ----- -------- ----- ----- ----- ----- ----- ----- ------ ------ -----
Dependency  Published 277818072    AP.VENDORS              70943     1     1     0   599585     0     0     0     0     0     0      0      0     0
Dependency  Published 1245660550   SH.PROMOTIONS           70470     1     1     0   588471     0     0     0     0     0     0      0      0     0
Result      Published 1990516455   SELECT /*SQRC_1.2*/         0     1     5     2   599585     0     0   164    50    11    25      1      1     0
                                        vendor_id
                                       ,name
                                     FROM ap.vendors

Result      Published 2172407538   SELECT /*+RESULT_CAC        0     1     1     4   588471     0     0     9    29    20    24      6      0     0
                                   HE SQRC_1.1*/
                                        promo_category_
                                   id
                                       ,promo_category
                                       ,SUM(promo_cost)
                                    total_cost
                                       ,ROUND(AVG(prom