Result cache is a pool to cache the frequently accessed non changing results of query in shared pool. It’s main benefit is to increase the performance of the cached query by fetching the result sets directly from in memory avoiding physical IO’s.
Uses:
OLAP systems
Why:
- Scanning large result sets and retrieving less rows
- Buffer cache scan will be skipped for the queries in result cache
- Less CPU consumption due to lack of need for consistent and current reads
Not suitable for:
OLTP systems where there are lot of DML commit transactions. The moment the data is modified, result cache invalidates the result set stored out of candidature.
Solution:
This wait occurs mainly due to space shortage of result cache memory being full.
The results of a query is held in result cache for short time and only queries where having high number of selective filters and return only few rows are the candidates. Hence queries which are not eligible to cache should be discarded if not appropriate.
First course of action should be to check RC memory report for space crunch
kish@x3z6zx9<^>set serveroutput on
kish@x3z6zx9<^>EXEC DBMS_RESULT_CACHE.MEMORY_REPORT;
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 = 11552K bytes (11552 blocks) <<== Available cache size(11 MB)
Maximum Result Size = 577K bytes (577 blocks)
[Memory]
Total Memory = 758336 bytes [0.161% of the Shared Pool] <<== Used space (7 KB)
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 752984 bytes [0.160% of the Shared Pool]
....... Overhead = 130392 bytes
....... Cache Memory = 608K bytes (608 blocks)
........... Unused Memory = 606 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Check in dynamic view
kish@x3z6zx9<^>col ID for 999999
kish@x3z6zx9<^>col NAME for a20
kish@x3z6zx9<^>col VALUE for 999999999
kish@x3z6zx9<^>select * from v$result_cache_statistics where ID in (2,3);
ID NAME VALUE
------- -------------------- ---------------------------------------------------------------------------------
2 Block Count Maximum 11552 <<== 11 MB
3 Block Count Current 608 <<== 6 KB
In below example, full rows(5000000) are retrieved from a large table without any filter
total rows in table = 5000000 ; row processed = 5000000
kIsH@Xhydra<>set timing on
kIsH@Xhydra<>set autot traceonly
kIsH@Xhydra<>select * from kish.xtbl;
5000000 rows selected.
Elapsed: 00:00:16.07 <<<<<================
Execution Plan
----------------------------------------------------------
Plan hash value: 1420452506
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000K| 252M| 11230 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| XTBL | 5000K| 252M| 11230 (1)| 00:00:01 |
--------------------------------------------------------------------------
Using result cache in this situation takes longer than without cache and is absurd because this query is not the right candidate for result caching. Hence this query and queries similar to this should be removed from the list to reduce result cache contention
kIsH@Xhydra<>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.03
kIsH@Xhydra<>alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.08
kIsH@Xhydra<>select /*+ result_cache */ * from kish.xtbl;
5000000 rows selected.
Elapsed: 00:00:16.37 <<<<<================
Execution Plan
----------------------------------------------------------
Plan hash value: 1420452506
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 5000K| 252M| 11230
(1)| 00:00:01 |
| 1 | RESULT CACHE | grv29sc2j3kuc53cxvzdpknbqd | 5000K| 252M| 11230
(1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| XTBL | 5000K| 252M| 11230
(1)| 00:00:01 |
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=8; dependencies=(KISH.XTBL); name="select /*+ result_cache *
/ * from kish.xtbl"
By default, result cache will be in manual mode
kIsH@Xhydra<>show parameter result_cache_mo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
The settings can be configured to force explicitly which forces the query to use result cache irrespective of their performance or auto which uses result cache for queries automatically when needed
kIsH@Xhydra<>alter system set result_cache_mode=force;
System altered.
kIsH@Xhydra<>set timing on
kIsH@Xhydra<>set autot traceonly
kIsH@Xhydra<>select * from kish.xtbl;
5000000 rows selected.
Elapsed: 00:00:16.14
Execution Plan
----------------------------------------------------------
Plan hash value: 1420452506
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 5000K| 252M| 11230
(1)| 00:00:01 |
| 1 | RESULT CACHE | grv29sc2j3kuc53cxvzdpknbqd | 5000K| 252M| 11230
(1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| XTBL | 5000K| 252M| 11230
(1)| 00:00:01 |
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=8; dependencies=(KISH.XTBL); name="select * from kish.xtbl"
kIsH@Xhydra<>alter system set result_cache_mode=auto;
System altered.
Elapsed: 00:00:00.02
kIsH@Xhydra<>select * from kish.xtbl;
5000000 rows selected.
Elapsed: 00:00:15.67
Execution Plan
----------------------------------------------------------
Plan hash value: 1420452506
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000K| 252M| 11230 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| XTBL | 5000K| 252M| 11230 (1)| 00:00:01 |
--------------------------------------------------------------------------
The status of the result cache queries can be seen in the below query. The result cache can also be bypassed if there are contention due to particular query
kIsH@Xhydra<>col name for a20
col namespace for a20
col status for a20
col rcount for 999999
select NAME,
NAMESPACE,
STATUS,
count(*) rcount
from v$result_cache_objects
group by NAME,NAMESPACE,STATUS
order by rcount desc;
kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<> 2 3 4 5 6 7
NAME NAMESPACE STATUS RCOUNT
-------------------- -------------------- -------------------- -------
select /*+ result_ca SQL Bypass 1
che */ * from kish.x
tbl
How to remove a candidate which is not efficient for a result cache ?
set lines 200 pages 1000
col NAMESPACE for a20
col NAME for a20
col round(avg(SCAN_COUNT)) for a20
col round(max(SCAN_COUNT)) for a20
select ID,
NAME,
NAMESPACE,
count(*),
round(avg(SCAN_COUNT)),
round(max(SCAN_COUNT)),
round(sum(BLOCK_COUNT)) from v$result_cache_objects where type='Result'
group by ID,NAME,NAMESPACE
order by round(sum(BLOCK_COUNT)) desc;
To remove the specific object and its dependent queries from result cache, use invalidate option
DBMS_RESULT_CACHE.INVALIDATE (
owner IN VARCHAR2,
name IN VARCHAR2)
RETURN NUMBER;
DBMS_RESULT_CACHE.INVALIDATE (
object_id IN BINARY_INTEGER);
Results which are stored in cache can also be blacklisted individually using black_list_add to prevent queries from using the object from result cache
dbms_result_cache.black_list_remove(
cache_id IN VARCHAR2,
global IN BOOLEAN DEFAULT FALSE);
exec dbms_result_cache.black_list_add('5tkmw2hy1n5z48rfgn162kqjrv', TRUE);
If high latch free waits are observed for the result cache RC latch and high number of invalidations happen due to adaptive dynamic sampling queries, the use the below workaround
alter system set "_optimizer_ads_use_result_cache" = FALSE;
Also below query is observed with high CPU usage
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel
To flush all the objects from result cache, use the below procedure
kIsH@zxd00<^>EXEC DBMS_RESULT_CACHE.FLUSH();
PL/SQL procedure successfully completed.
To permanently blacklist a set of cache id’s from result cache, use the following parameter.
alter system set "_result_cache_black_list"='5tkmw2hy1n5z48rfgn162kqjrv','gymbn0n3vka176untmq57gwfzs';
Result cache can also be bypassed by the queries from using it or can be permanently disabled
--Remove the cache result sets and further prevent queries from using it
EXEC DBMS_RESULT_CACHE.BYPASS(TRUE);
or
--disable result cache
alter system set "result_cache_max_size"=0
Below are the type of Result cache latches in 21c
kIsH@zxd00<^>col NAME for a20
kIsH@zxd00<^>col DISPLAY_NAME for a20
kIsH@zxd00<^>select * from v$latchname where name like 'Result Cache%';
LATCH# NAME DISPLAY_NAME HASH TYPE CON_ID
---------- -------------------- -------------------- ---------- ---- ----------
846 Result Cache: RC Lat Result Cache: RC Lat 1054203712 SGA 0
ch ch
847 Result Cache: SO Lat Result Cache: SO Lat 986859868 SGA 0
ch ch
848 Result Cache: MB Lat Result Cache: MB Lat 995186388 SGA 0
ch ch
849 Result Cache: Heap Q Result Cache: Heap Q 2355791010 SGA 0
ueue Latch ueue Latch
850 Result Cache; Flush Result Cache; Flush 230283579 PDB 0
View Latch View Latch
851 Result Cache: Set Result Cache: Set 218554630 PDB 0
6 rows selected.
