Oracle DBMS_RESULT_CACHE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Result Cache administration
Note Client Side SQLNET.ORA client cache parameters are optional and they over-ride server settings. The OCI_RESULT_CACHE_MAX_SIZE must be set in bytes and must be at least 32768. For 2M set it to 2048000.

OCI_RESULT_CACHE_MAX_SIZE = 2048000
OCI_RESULT_CACHE_MAX_RSET_SIZE = 1024000
OCI_RESULT_CACHE_MAX_RSET_ROWS = 100000
AUTHID DEFINER
Constants
Name Data Type Value
STATUS_BYPS VARCHAR2(10) 'BYPASS'
STATUS_CORR VARCHAR2(10) 'CORRUPT'
STATUS_DISA VARCHAR2(10) 'DISABLED'
STATUS_ENAB VARCHAR2(10) 'ENABLED'
STATUS_SYNC VARCHAR2(10) 'SYNC'
Dependencies
ALL_OBJECTS DBMS_RESULT_CACHE_SUM V$RESULT_CACHE_STATISTICS
DBMS_OUTPUT PLITBLM V$SGASTAT
DBMS_RC_LIB V$RESULT_CACHE_MEMORY V$SGA_DYNAMIC_COMPONENTS
DBMS_RESULT_CACHE_NSP V$RESULT_CACHE_OBJECTS  
Documented Yes
First Available 11.1.0.6
Initialization Parameters set linesize 121
col name format a30
col value format a30

SELECT name, value, issys_modifiable, isses_modifiable
FROM gv$parameter
WHERE name LIKE '%result%';

-- modifiable
-- ALTER SYSTEM SET result_cache_max_result=7 SCOPE=BOTH;
-- options: AUTO, MANUAL, and FORCE: MANUAL is the default
-- ALTER SYSTEM SET result_cache_mode='AUTO' SCOPE=BOTH;
-- ALTER SYSTEM SET result_cache_remote_expiration=0 SCOPE=BOTH;

-- not modifiable
-- client_result_cache_lag = 5000
-- result_cache_size = 'MANUAL'
-- client_result_cache_size = 0
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmsrcad.sql
Subprograms
 
BYPASS
Set the bypass mode for the Result Cache. When bypass mode is "on", cached results are no longer used and new results are not saved. When turned off, the cache resumes normal operation. dbms_result_cache.bypass(bypass_mode IN BOOLEAN, session IN BOOLEAN DEFAULT FALSE);
BEGIN
  dbms_result_cache.bypass(FALSE);
  dbms_result_cache.flush;
END;
/

Note: With RAC this must be done on each node
 
DELETE_DEPENDENCY
Deletes the specified dependency object from the Result Cache and invalidates results that used the dependency

Overoad 1
dbms_result_cache.delete_dependency(owner IN VARCHAR2, name IN VARCHAR2) RETURN NUMBER;
SELECT o.object_id, o.object_name, v.object_no
FROM all_objects o, gv$result_cache_dependency v
WHERE o.object_id = v.object_no;

SELECT COUNT(*)
FROM gv$result_cache_objects;

SELECT /*+ RESULT_CACHE */ srvr_id
FROM (
  SELECT srvr_id, SUM(cnt) SUMCNT
  FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
  GROUP BY srvr_id)
WHERE sumcnt = 2;

SELECT o.object_id, o.object_name, v.object_no
FROM all_objects o, gv$result_cache_dependency v
WHERE o.object_id = v.object_no;

SELECT COUNT(*)
FROM gv$result_cache_objects;

set serveroutput on

DECLARE
 i NUMBER;
BEGIN
  i := dbms_result_cache.delete_dependency(USER, 'SERVERS');
  dbms_output.put_line(i);
END;
/

SELECT o.object_id, o.object_name, v.object_no
FROM all_objects o, gv$result_cache_dependency v
WHERE o.object_id = v.object_no;

SELECT COUNT(*)
FROM gv$result_cache_objects;
Overload 2 dbms_result_cache.delete_dependency(IN VARCHAR2, name IN VARCHAR2);
TBD
Overload 3 dbms_result_cache.delete_dependency(object_id IN NATURALN) RETURN NUMBER;
TBD
Overload 4 dbms_result_cache.delete_dependency(object_id IN NATURALN);
TBD
 
FLUSH
Attempts to remove all the objects from the Result Cache, and depending on the arguments retains/releases the memory and retains/clears the statistics.

Overload 1
dbms_result_cache.flush(
retainMem IN BOOLEAN DEFAULT FALSE,
retainSta IN BOOLEAN DEFAULT FALSE,
global    IN BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_result_cache.flush THEN
    dbms_output.put_line('Flush Successful');
  ELSE
    dbms_output.put_line('Flush Failure');
  END IF;
END;
/
Overload 2 dbms_result_cache.flush(
retainMem IN BOOLEAN DEFAULT FALSE,
retainSta IN BOOLEAN DEFAULT FALSE,
global    IN BOOLEAN DEFAULT FALSE);
exec dbms_result_cache.flush(FALSE, TRUE);
 
INVALIDATE
Invalidates all the result-set objects that dependent upon the specified dependency object

Overload 1
dbms_result_cache.invalidate(owner IN VARCHAR2, name IN VARCHAR2)
RETURN NUMBER;
See DEMOs below
Overload 2 dbms_result_cache.invalidate(owner IN VARCHAR2, name IN VARCHAR2);
See DEMOs below
Overload 3 dbms_result_cache.invalidate(object_id IN NATURALN) RETURN NUMBER;
See DEMOs below
Overload 4 dbms_result_cache.invalidate(object_id IN NATURALN);
See DEMOs below
 
INVALIDATE_OBJECT
Invalidates the specified result-set object(s)
Overload 1
dbms_result_cache.invalidate_object(id IN NATURALN) RETURN NUMBER;
See DEMOs below
Overload 2 dbms_result_cache.invalidate_object(id IN NATURALN);
See DEMOs below
Overload 3 dbms_result_cache.invalidate_object(cache_id IN VARCHAR) RETURN NUMBER;
See DEMOs below
Overload 4 dbms_result_cache.invalidate_object(cache_id IN VARCHAR);
See DEMOs below
 
MEMORY_REPORT
Produces the memory usage report for the Result Cache dbms_result_cache.memory_report(detailed IN BOOLEAN DEFAULT FALSE);
set serveroutput on

exec dbms_result_cache.memory_report;

exec dbms_result_cache.memory_report(TRUE);
Result Cache Memory Report Sample 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  = 209728K bytes (209728 blocks) Maximum Result Size = 10486K bytes (10486 blocks) [Memory] Total Memory = 12706360 bytes [0.039% of the Shared Pool] ... Fixed Memory = 79272 bytes [0.000% of the Shared Pool] ....... Cache Mgr  = 152 bytes ....... Memory Mgr = 200 bytes ....... Bloom Fltr = 2K bytes ....... State Objs = 76872 bytes ... Dynamic Memory = 12627088 bytes [0.039% of the Shared Pool] ....... Overhead = 175248 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 52432 bytes (6554 slots)
........... Chunk Maps    = 26216 bytes
........... Miscellaneous = 31064 bytes
....... Cache Memory = 12160K bytes (12160 blocks) ........... Unused Memory = 5 blocks ........... Used Memory = 12155 blocks ............... Dependencies = 149 blocks (4 count) ............... Results = 12006 blocks
................... SQL     = 3089 blocks (465 count)
................... PLSQL   = 8917 blocks (8917 count)
 
STATUS
Returns Result Cache status dbms_result_cache.status RETURN VARCHAR2;
col status format a40

SELECT dbms_result_cache.status
FROM dual;
 
SQL Demos
Manual Result Cache Demo SELECT dbms_result_cache.status
FROM dual;

col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%result%';

SELECT COUNT(*)
FROM gv$result_cache_objects;

/* if cached objects
set serveroutput on

BEGIN
  IF dbms_result_cache.flush THEN
    dbms_output.put_line('Flush Successful');
  ELSE
    dbms_output.put_line('Flush Failure');
  END IF;
END;
/

SELECT COUNT(*)
FROM gv$result_cache_objects;
*/


SELECT srvr_id
FROM (
  SELECT srvr_id, SUM(cnt) SUMCNT
  FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
  GROUP BY srvr_id)
WHERE sumcnt = 2;

conn sys@pdbdev as sysdba

SELECT COUNT(*)
FROM gv$result_cache_objects;

conn uwclass/uwclass@pdbdev

SELECT /*+ RESULT_CACHE */ srvr_id
FROM (
  SELECT srvr_id, SUM(cnt) SUMCNT
  FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
  GROUP BY srvr_id)
WHERE sumcnt = 2;

-- cache object identification
conn sys@pdbdev as sysdba

SELECT o.object_id, o.object_name, v.object_no
FROM all_objects o, gv$result_cache_dependency v
WHERE o.object_id = v.object_no;

SELECT COUNT(*)
FROM gv$result_cache_objects;

set linesize 121
set pagesize 25
col name format a45
col row_size_min format 99999
col cache_id format a30
col cache_key format a30

-- namespace can be SQL or PL/SQL
SELECT id, type, status, name, namespace
depend_count
FROM gv$result_cache_objects
ORDER BY 1;

SELECT id, creation_timestamp, depend_count, scan_count
FROM gv$result_cache_objects
ORDER BY 1;

SELECT id, block_count, scn, column_count, row_count, row_size_max,
row_size_min, row_size_avg
FROM gv$result_cache_objects
ORDER BY 1;

SELECT id, build_time, lru_number, object_no, invalidations, cache_id, cache_key
FROM gv$result_cache_objects
ORDER BY 1;

SELECT id, offset, free, object_id
FROM gv$result_cache_memory
ORDER BY id;

exec dbms_result_cache.flush(FALSE, TRUE);

SELECT COUNT(*)
depend_count
FROM gv$result_cache_objects;
Automatic Result Cache Demo conn sys@pdbdev as sysdba

GRANT SELECT on gv_$result_cache_objects TO uwclass;

SELECT COUNT(*)
depend_count
FROM gv$result_cache_objects;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%result%';

ALTER SYSTEM SET result_cache_mode = 'AUTO' SCOPE=BOTH;

conn uwclass/uwclass@pdbdev

SELECT COUNT(*)
depend_count
FROM gv$result_cache_objects;

set linesize 121
col name format a25
col cache_id format a30
col name format a20
col status format a15

SELECT id, offset, free, object_id
FROM gv$result_cache_memory
ORDER BY id;

SELECT id, type, status, cache_id, name, scn
FROM gv$result_cache_objects
ORDER BY 1;

SELECT MAX(SUBSTR(netaddress,1,3))
FROM servers;

SELECT id, type, status, cache_id, name, scn
FROM gv$result_cache_objects
ORDER BY 1;

SELECT MAX(line_number)
FROM airplanes
WHERE customer_id = 'DAL';

-- sequentially invalidate objects using multiple overloads
 
PL/SQL Demos
Manual PL/SQL Invocation SELECT dbms_result_cache.status
FROM dual;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%result%';

SELECT COUNT(*)
FROM gv$result_cache_objects;

CREATE OR REPLACE FUNCTION nocache(p_srvr_id IN servers.srvr_id%TYPE) RETURN BOOLEAN IS
 srvrow servers%ROWTYPE;
BEGIN
  SELECT *
  INTO srvrow
  FROM servers
  WHERE srvr_id = p_srvr_id;

  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END nocache;
/

set timing on

DECLARE
 b BOOLEAN;
BEGIN
  FOR i IN 1 .. 10 LOOP
    FOR i IN 1 .. 618 LOOP
      IF nocache(i) THEN
        NULL;
      END IF;
    END LOOP;
  END LOOP;
END;
/

CREATE OR REPLACE FUNCTION rcache(p_srvr_id IN servers.srvr_id%TYPE) RETURN BOOLEAN RESULT_CACHE RELIES_ON(servers)
AUTHID DEFINER IS
 srvrow servers%ROWTYPE;
BEGIN
  SELECT *
  INTO srvrow
  FROM servers
  WHERE srvr_id = p_srvr_id;

  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END rcache;
/

set timing on

DECLARE
 b BOOLEAN;
BEGIN
  FOR i IN 1 .. 10 LOOP
    FOR i IN 1 .. 618 LOOP
      IF rcache(i) THEN
        NULL;
      END IF;
    END LOOP;
  END LOOP;
END;
/
 
Related Queries
Result Cache Statistics desc gv$result_cache_statistics

set linesize 121
col name format a30
col value format 999999

SELECT name, value
FROM gv$result_cache_statistics;
Result Cache Performance SELECT inst_id, sid, serial#, username, logon_time,
  ROUND((sysdate-logon_time)*1440) timeonline_min, a.*
FROM gv$session, (
 SELECT inst_id instance_number,sql_id, sql_text,
   ROUND((conc_wait_sec_exec/elap_sec_exec)*100) con_perc,
   ROUND((clu_wait_sec_exec/elap_sec_exec)*100) clust_perc,
   ROUND((user_io_wait_sec_exec/elap_sec_exec)*100) io_perc,
   conc_wait_sec_exec, clu_wait_sec_exec, user_io_wait_sec_exec,
    cpu_time_sec_exec , elap_sec_exec, buffer_gets,
   ROUND((buffer_gets*32678)/1024/1024/1024) buffer_gb,
   disk_reads, rows_processed, module, service, action
 FROM (
   SELECT inst_id, sql_id, sql_text,
     ROUND((concurrency_wait_time/1000000)/DECODE(executions,NULL,1,0,1,executions),2) conc_wait_sec_exec,
     ROUND((cluster_wait_time/1000000)/DECODE(executions,NULL,1,0,1,executions),2) clu_wait_sec_exec ,
     ROUND((user_io_wait_time/1000000)/DECODE(executions,NULL,1,0,1,executions),2) user_io_wait_sec_exec,
     ROUND((direct_writes/DECODE(executions,NULL,1,0,1,executions)),2) direct_writes_exec,
     ROUND((cpu_time/1000000)/DECODE(executions,NULL,1,0,1,executions),2) cpu_time_sec_exec,
     ROUND(( elapsed_time/1000000)/DECODE(executions,NULL,1,0,1,executions),2) elap_sec_exec,
     ROUND((io_interconnect_bytes/DECODE(executions,NULL,1,0,1,executions)),2) io_inter_by_exec,
     concurrency_wait_time, cluster_wait_time, user_io_wait_time, direct_writes,
     cpu_time, elapsed_time, io_interconnect_bytes,
     ROUND(sorts/DECODE(executions,NULL,1,0,1,executions),2) sort_exec,
     fetches, rows_processed, executions, parse_calls,
     ROUND(disk_reads/DECODE(executions,NULL,1,0,1,executions),2) disk_exec,
     ROUND(buffer_gets/DECODE(executions, NULL, 1, 0, 1, executions),2) buff_exec,
     service, module, action, buffer_gets, disk_reads
   FROM gv$sql
   WHERE users_opening > 0
   AND elapsed_time/DECODE(executions,NULL,1,0,1,executions) >= 18000)) a
WHERE a.sql_id=gv$session.sql_id
AND a.instance_number=gv$session.inst_id;

Related Topics
DBMS_CLIENT_RESULT_CACHE
DBMS_RESULT_CACHE_API
DBMS_RESULT_CACHE_INTERNAL
Hints
Packages
Result Cache (SQL)
Result Cache (PL/SQL)
Startup Parameters
Table

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved