/*
|| 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