| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsrcad.sql |
| First Available |
11.1 |
| Constants |
|
Name |
Data Type |
Value |
| STATUS_CLSD |
VARCHAR(10) |
'CLOSED' |
| STATUS_OPEN |
VARCHAR(10) |
'OPEN' |
| STATUS_SYNC |
VARCHAR(10) |
'SYNC' |
|
| Dependent Objects |
| ALL_OBJECTS |
GV_$RESULT_CACHE_MEMORY |
| DBMS_RC_LIB |
GV_$RESULT_CACHE_OBJECTS |
| GV_$CLIENT_RESULT_CACHE_STATS |
GV_$RESULT_CACHE_STATISTICS |
| GV_$RESULT_CACHE_DEPENDENCY |
|
|
Result Cache Parameters |
set linesize 121
col name format a30
col value format a30
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%result%';
-- modifiable
-- ALTER SYSTEM SET result_cache_max_result = 5 SCOPE=BOTH;
-- option 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 = 3000
-- result_cache_size = 0
-- client_result_cache_size = 1179648 |
| 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; |
| Security Model |
Execute is granted to the DBA role |
| |
| 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); |
BEGIN
dbms_result_cache.bypass(FALSE);
dbms_result_cache.flush;
END;
/
Note: With RAC this must be done on each node |
| |
| 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)
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); |
| 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 DEMO below |
| Overload 2 |
dbms_result_cache.invalidate(owner IN VARCHAR2, name IN VARCHAR2); |
| See DEMO below |
| Overload 3 |
dbms_result_cache.invalidate(object_id IN POSITIVEN) RETURN NUMBER; |
| See DEMO below |
| Overload 4 |
dbms_result_cache.invalidate(object_id IN POSITIVEN); |
| See DEMO below |
| |
| INVALIDATE_OBJECT |
Invalidates the specified result-set object(s)
Overload 1 |
dbms_result_cache.invalidate_object(id IN POSITIVEN) RETURN NUMBER; |
| See DEMO below |
| Overload 2 |
dbms_result_cache.invalidate_object(id IN POSITIVEN); |
| See DEMO below |
| Overload 3 |
dbms_result_cache.invalidate_object(cache_id IN VARCHAR) RETURN NUMBER; |
| See DEMO below |
| Overload 4 |
dbms_result_cache.invalidate_object(cache_id IN VARCHAR); |
| See DEMO below |
| |
| STATUS |
| Returns Result Cache
status |
dbms_result_cache.status RETURN
VARCHAR2 |
SELECT dbms_result_cache.status
FROM dual; |
| |
| Demo |
Manual
Result Cache Demo |
SELECT dbms_result_cache.status
FROM dual;
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%result%';
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;
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;
-- cache object identification
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
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 |
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;
SELECT COUNT(*)
depend_count
FROM gv$result_cache_objects;
col name format a25
col cache_id format a30
SELECT id, cache_id, hash, type, status, name
FROM gv$result_cache_objects
ORDER BY 1;
-- 5 demos
-- sequentially invalidate objects using multiple overloads
FROM gv$result_cache_objects get cache_id, object_no and name |