ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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.
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%';
ALTER SYSTEM SET result_cache_mode='MANUAL' SCOPE=BOTH; -- options: AUTO, MANUAL, and FORCE: MANUAL is the default
ALTER SYSTEM SET result_cache_max_result=7 SCOPE=BOTH;
ALTER SYSTEM SET result_cache_mode='AUTO' SCOPE=BOTH;
ALTER SYSTEM SET result_cache_remote_expiration=0 SCOPE=BOTH;
ALTER SYSTEM SET client_result_cache_size=0 SCOPE=SPFILE;
ALTER SYSTEM SET client_result_cache_lag=5000 SCOPE=SPFILE;
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;
/
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);
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);
Produces the history usage report for the Result Cache
The compact parameter appears to affect the report storage metadata as it has no impact on the report produced.
dbms_result_cache.history_report(compact IN BOOLEAN DEFAULT FALSE);
set serveroutput on
show parameter result_cache_mode
NAME TYPE VALUE
------------------ ----------- ------------------------------
result_cache_mode string MANUAL
exec dbms_result_cache.history_report(TRUE); Result Cache PLSQL Function History Report
DISABLED
Window size = 0 bytes
Extent size = 0 bytes
Hash Count = 0
Max windows = 2
Max records = 0
Total records in partitions = 0
PL/SQL procedure successfully completed.
ALTER SYSTEM SET result_cache_mode = FORCE SID='*' SCOPE=BOTH;
System altered.
-- attempts to get results other than the default shown above all fail
-- as all three of these HISTORY_ procs are not documented it may be that
-- this functionality is not enabled in 21.1.
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
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;
/
