Result Cache, can be used to cache query and function results in memory. The cached information is stored in a dedicated area inside the shared pool
where it can be shared by other PL/SQL programs that are performing similar calculations.
The result cache takes its memory from the shared pool. Therefore, if you expect to increase the maximum size of the result cache, take this into consideration when sizing the shared pool.
-> If you are managing the size of the shared pool using the SGA_TARGET initialization parameter, Oracle Database allocates 0.50% of the value of the SGA_TARGET parameter to the result cache.
-> If you are managing the size of the shared pool using the SHARED_POOL_SIZE initialization parameter, then Oracle Database allocates 1% of the shared pool size to the result cache.
############## Result Cache Parameters ##############
1) Result_cache_mode
The Result Cache is set up using the result_cache_mode initialization parameter with one of these three values:
-> MANUAL is the default and recommended value.
-> Query results can only be stored in the result cache by using a query hint or table annotation.
-> FORCE : All results are stored in the result cache.
2) RESULT_CACHE_MAX_SIZE
-> Specifies the memory allocated to the server result cache. To disable the server result cache, set this parameter to 0.
-> the result cache is specific to each database instance and
can be sized differently on each instance.
3) RESULT_CACHE_MAX_RESULT
-> Specifies the maximum amount of server result cache memory (in percent) that can be used for a single result.
Valid values are between 1 and 100. The default value is 5%.
You can set this parameter at the system or session level.
4) RESULT_CACHE_REMOTE_EXPIRATION
-> The default value is 0, which specifies that results using remote objects will not be cached
If a non-zero value is set for this parameter, DML on the remote database does not invalidate the server result cache.
Specifies the expiration time (in minutes) for a result in the server result cache that depends on remote database objects.
############## how to check result cache corruption ##############
At times we come across result cache Contention or Corruption issues which can be easily fixed by disabling and re-enabling result cache . "ORA-600 [qesrcro_dol2ro] / result cache corruption" can be seen in alert log in case of corruption issues .
SQL> SELECT dbms_result_cache.status() FROM dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
CORRUPT
--> Generate Result Cache Report
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
-> It will flush the server result cache.
EXEC DBMS_RESULT_CACHE.FLUSH;
############## Flushing Result Cache ##############
--Flush retaining statistics (default for both are FALSE)
begin
dbms_result_cache.flush (
retainmem => FALSE,
retainsta => TRUE);
end;
/
--Flush Retaining memory (default for both are FALSE)
begin
dbms_result_cache.flush (
retainmem => TRUE,
retainsta => FALSE);
end;
/
--Flush memory and statistics globally
begin
dbms_result_cache.flush(
retainmem => TRUE,
retainsta => FALSE,
global => TRUE);
end;
/
############## Manually Use Result Cache Using Hint ##############
--Use the result_cache hint because manual mode is being used for this instance
select /*+ result_cache */
------------------------------------------
| Id | Operation |
------------------------------------------
| 11 | COUNT STOPKEY |
| 12 | VIEW |
| 13 | RESULT CACHE |
------------------------------------------
The /*+ NO_RESULT_CACHE */ hint instructs the database not to cache the results in either the server or client result caches.
Enable result cache of Table
ALTER TABLE sales RESULT_CACHE (MODE FORCE);
############## Disable and re-enable the result cache ##############
alter system set RESULT_CACHE_MAX_SIZE=0;
alter system set RESULT_CACHE_MAX_SIZE=0;
alter system set RESULT_CACHE_MAX_SIZE=125856K
############## Temporarily ByPass the result cache ##############
If we dont want to disable Result cache , temporarily we can Bypass Result cache using below
--> To Bypass
begin
dbms_result_cache.bypass(true) ;
dbms_result_cache.flush ;
end ;
/
select dbms_result_cache.status () from dual ;
--> To Normalize
begin
dbms_result_cache.bypass(flase) ;
dbms_result_cache.flush ;
end ;
/
############## other views ##############
# to check which sql is using result cache
select sid , result_cache from v$sql ;
select * from GV$RESULT_CACHE_DEPENDENCY;
select * from GV$RESULT_CACHE_MEMORY;
select * from GV$RESULT_CACHE_OBJECTS;
select * from GV$RESULT_CACHE_STATISTICS;
show parameters result
Set line 400
--Check result cache parameters
col name for a30
col value for a30
select
name,
value
from
v$parameter
where
name like '%result%';
--Query the v$result_cache_objects to check if there is any cached object
select
count(*)
from
v$result_cache_objects;
col "Space Over" for a30
col "Space Unused" for a30
col "Obj_Name_Dep" for a30
select
type "Type",
name "Name",
namespace "SQL|PL/SQL",
creation_timestamp "Creation",
space_overhead "Space Over",
space_unused "Space Unused",
cache_id "Obj_Name_Dep",
invalidations "Invds"
from
gv$result_cache_objects;
--Check the Result Cache Setting and Statistics
select
name "Name",
value "Value"
from
v$result_cache_statistics;
--Check objects cached
select
o.owner "Owner",
o.object_id "ID",
o.object_name "Name",
r.object_no "Obj Number"
from
dba_objects o,
gv$result_cache_dependency r
where
o.object_id = r.object_no;
--Checking memory blocks and their status in Result Cache Memory
select * from
gv$result_cache_memory;
SQL> set linesize 150
SQL> select id,name,row_count,type,invalidations from v$result_cache_objects;
############## Requirements for the Result Cache ##############
Enabling the result cache does not guarantee that a specific result set will be included in the server or client result cache.
In order for results to be cached, the following requirements must be met:
1. Read Consistency Requirements
-> If the current session has an active transaction referencing objects in a query, then the results from this query are not eligible for caching.
2. Query Parameter Requirements
Cache results can be reused if they are parameterized with variable values when queries are equivalent and the parameter values are the same. Different values or bind variable names may cause cache misses. Results are parameterized if any of the following constructs are used in the query:
-> Bind variables
-> The SQL functions DBTIMEZONE, SESSIONTIMEZONE, USERENV/SYS_CONTEXT (with constant variables), UID, and USER NLS parameters
