RESULT CACHE: RC LATCH

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:

The results of a query is held in result cache for short time and only queries where have 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 round(avg(SCAN_COUNT)) for a20
col round(max(SCAN_COUNT)) for a20
select ID,
       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,NAMESPACE 
	   order by round(sum(BLOCK_COUNT)) desc;

To remove the cached results from result cache, invalidate the result cache.

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_remove option in 21c

dbms_result_cache.black_list_remove(
cache_id IN VARCHAR2,
global   IN BOOLEAN DEFAULT FALSE);

exec dbms_result_cache.black_list_remove(220, TRUE);

If high latch free waits are observed for the result cache RC latch, then set the below workaround to disable the ADS with result cache mode as MANUAL

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

Leave a Reply