Configure and manage result cache

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] About the Result Cache
[2] Configuring the Result Cache
[3] Specifying Queries for Result Caching
[4] Monitoring the Result Cache
====
HOL
====
[1] Using Result Cache feature with SQL
[2] Using Result Cache feature with PL/SQL
[3] Testing with Multitenant

The "Result Cache" is a new feature of 11g, so we need to use the COMPATIBLE parameter is at least 11.0.0.0. To use the client cache views, this parameter must be at least 11.2.0.0.0.


Database Administrator’s Guide
6 Managing Memory
6.4.3.4.1 The Result Cache and Shared Pool Size
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.

6.4.3.8 Specifying the Result Cache Maximum Size

The RESULT_CACHE_MAX_SIZE initialization parameter is a dynamic parameter that enables you to specify the maximum size of the 
result cache component of the SGA

SQL> alter system set RESULT_CACHE_MAX_SIZE=3M;

System altered.


Typically, there is no need to specify this parameter, because the default maximum size is chosen by the database based 
on total memory available to the SGA and on the memory management method currently in use.

You can view the current default maximum size by displaying the value of the RESULT_CACHE_MAX_SIZE parameter. 
To change this maximum size, you can set RESULT_CACHE_MAX_SIZE with an ALTER SYSTEM statement, or you can 
specify this parameter in the text initialization parameter file. In each case, the value is rounded up to the 
nearest multiple of 32K.

If RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is disabled. 
To reenable it you must set RESULT_CACHE_MAX_SIZE to a nonzero value (or remove this parameter from the text 
initialization parameter file to get the default maximum size) and then restart the database.

Most reliable way to determine if the result cache is enabled. You can use the following query instead:

SELECT dbms_result_cache.status() FROM dual;

The view V$RESULT_CACHE_STATISTICS and the PL/SQL package procedure DBMS_RESULT_CACHE.MEMORY_REPORT 
display information to help you determine the amount of memory currently allocated to the result cache.

The PL/SQL package function DBMS_RESULT_CACHE.FLUSH clears the result cache and releases all the 
memory back to the shared pool.


Database Performance Tuning Guide
15 Tuning the Result Cache

[1] About the Result Cache

A result cache is an area of memory, either in the Shared Global Area (SGA) or 
client application memory, that stores the results of a database query or 
query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale.

This section describes the two types of result cache and contains the following topics:

1. Server Result Cache Concepts
2. Client Result Cache Concepts

1. Server Result Cache Concepts
-> The server result cache is a memory pool within the shared pool. 
-> This memory pool consists of the SQL query result cache—which stores results of SQL queries—and the PL/SQL function result cache, which stores values returned by PL/SQL functions.

Benefits of Using the Server Result Cache
-> The benefits of using the server result cache depend on the application.
-> OLAP applications can benefit significantly from its use. 
-> Good candidates for caching are queries that access a high number of rows but return a small number, such as those in a data warehouse.
-> For example, you can use advanced query rewrite with equivalences to create materialized views that materialize queries in the result cache instead of using tables.

Database Data Warehousing Guide
11 Advanced Query Rewrite for Materialized Views
--> Creating Result Cache Materialized Views with Equivalences

Understanding How the Server Result Cache Works
-> When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.

-> When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects 
is modified.

The following sections contains examples of how to retrieve results from the server result cache:
How Results are Retrieved in a Query
-> The following example shows a query of hr.employees that uses the RESULT_CACHE hint to retrieve rows from the server result cache.

connect HR/HR@pdb2
SET AUTOTRACE ON EXP
SET LINESIZE 100
SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
FROM employees
GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 6s9pvgnscuxfbck5ggg4f5wg3v | | | | |
| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT /*+ RESULT_CACHE */ department_id,
AVG(salary)
FROM employees
GROUP BY department_id"

connect sys/oracle_4U@pdb2 as sysdba

SELECT id, type, creation_timestamp, block_count,
column_count, pin_count, row_count
FROM V$RESULT_CACHE_OBJECTS
WHERE cache_id = '&cache_id';


ID TYPE CREATION_TIMESTAMP BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT
---------- ---------- ----------------------------- ----------- ------------ ---------- ----------
91 Result Sep 24 2019 10:55:31 1 2 0 12


How Results are Retrieved in a View

connect HR/HR@pdb2
SET AUTOTRACE ON EXP
SET LINESIZE 1000

WITH summary AS
( SELECT /*+ RESULT_CACHE */ department_id, avg(salary) avg_sal
FROM hr.employees
GROUP BY department_id )
SELECT d.*, avg_sal
FROM hr.departments d, summary s
WHERE d.department_id = s.department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 523547400

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 517 | 7 (29)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 517 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 567 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 286 | 5 (40)| 00:00:01 |
| 5 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 6 | RESULT CACHE | fkf7hjnq1wmjz2wb3r49mg3v22 | | | | |
| 7 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("D"."DEPARTMENT_ID"="S"."DEPARTMENT_ID")
filter("D"."DEPARTMENT_ID"="S"."DEPARTMENT_ID")

Result Cache Information (identified by operation id):
------------------------------------------------------

6 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT /*+ RESULT_CACHE */ department_id, avg(salary) avg_sal
FROM hr.employees
GROUP BY department_id "


In this example, the summary view results are retrieved directly from the cache, as indicated in step 3 of the execution plan. The value in the Name column is the cache ID of the result.

connect sys/oracle_4U@pdb2 as sysdba

SELECT id, type, creation_timestamp, block_count,
column_count, pin_count, row_count
FROM V$RESULT_CACHE_OBJECTS
WHERE cache_id = '&cache_id'; 
Enter value for cache_id: fkf7hjnq1wmjz2wb3r49mg3v22
old 4: WHERE cache_id = '&cache_id'
new 4: WHERE cache_id = 'fkf7hjnq1wmjz2wb3r49mg3v22'

ID TYPE CREATION_TIMESTAMP BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT
---------- ---------- ----------------------------- ----------- ------------ ---------- ----------
92 Result Sep 24 2019 10:58:53 1 2 0 12


[2] Configuring the Result Cache

-> 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.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- --------------------
sga_max_size big integer 792M
sga_target big integer 0
unified_audit_sga_queue_size integer 1048576
SQL>
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- --------------------
memory_max_target big integer 792M
memory_target big integer 792M

SQL>
SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- --------------------
shared_pool_reserved_size big integer 6M
shared_pool_size big integer 0

SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- --------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0


Note:
Oracle Database will not allocate more than 75% of the shared pool to the server result cache.

The size of the server result cache grows until it reaches the maximum size. Query results larger than the available space in the cache are not cached. The database employs a Least Recently Used (LRU) algorithm to age out cached results, but does not otherwise automatically release memory from the server result cache.

Table 15-1 Server Result Cache Initialization Parameters

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.

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.

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. 

RESULT_CACHE_MODE 
-> 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.


>CDB
SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

SQL> alter session set container=pdb2;

Session altered.

SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

=================
DBMS_RESULT_CACHE 
=================
#Functions
SELECT DBMS_RESULT_CACHE.STATUS() FROM DUAL;
-> For reliable way to determine if the result cache is enabled

#Procedures
EXEC DBMS_RESULT_CACHE.MEMORY_REPORT;
-> It will display memory usage statistics for the result cache:

EXEC DBMS_RESULT_CACHE.FLUSH;
-> It will flush the server result cache.


--HOL
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL>
SQL> SELECT DBMS_RESULT_CACHE.STATUS() FROM DUAL;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE 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 = 0 bytes
Maximum Cache Size = 0 bytes (0 blocks)
Maximum Result Size = 0 bytes (0 blocks)
[Memory]
Total Memory = 12264 bytes [0.006% of the Shared Pool]
... Fixed Memory = 12264 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> alter session set container=pdb2;

Session altered.

SQL>
SQL> SELECT DBMS_RESULT_CACHE.STATUS() FROM DUAL;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE 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 = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
... Fixed Memory = bytes [% of the Shared Pool]
BEGIN DBMS_RESULT_CACHE.MEMORY_REPORT; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SYS.DBMS_RESULT_CACHE", line 379
ORA-06512: at "SYS.DBMS_RESULT_CACHE", line 722
ORA-06512: at line 1


SQL> !oerr ora 01476
01476, 00000, "divisor is equal to zero"
// *Cause:
// *Action:

SQL> desc DBMS_RESULT_CACHE
PROCEDURE BYPASS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BYPASS_MODE BOOLEAN IN
SESSION BOOLEAN IN DEFAULT
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE DELETE_DEPENDENCY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
PROCEDURE DELETE_DEPENDENCY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
FUNCTION FLUSH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
GLOBAL BOOLEAN IN DEFAULT
PROCEDURE FLUSH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
GLOBAL BOOLEAN IN DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID VARCHAR2 IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID VARCHAR2 IN
PROCEDURE MEMORY_REPORT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DETAILED BOOLEAN IN DEFAULT
FUNCTION STATUS RETURNS VARCHAR2

SQL>

----------------------------------
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


3. Restrictions for the Result Cache

Results cannot be cached when the following objects or functions are in a query:
-> Temporary tables and tables in the SYS or SYSTEM schemas
-> Sequence CURRVAL and NEXTVAL pseudo columns
-> SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), 
SYS_GUID, SYSDATE, and SYS_TIMESTAMP


[3] Specifying Queries for Result Caching

This section describes how to specify queries for result caching and contains the following topics:

1. Using SQL Result Cache Hints

1.1 Using the RESULT_CACHE Hint

-> When the result cache mode is MANUAL, the /*+ RESULT_CACHE */ hint instructs the database to cache 
the results of a query block and to use the cached results in future executions.

Example 15-3 Using the RESULT_CACHE Hint

SELECT /*+ RESULT_CACHE */ prod_id, SUM(amount_sold)
FROM sales 
GROUP BY prod_id
ORDER BY prod_id;

In this example, the query instructs the database to cache rows for a query of the sales table.


1.2 Using the NO_RESULT_CACHE Hint

The /*+ NO_RESULT_CACHE */ hint instructs the database not to cache the results in either the server or client result caches.

Example 15-4 Using the NO_RESULT_CACHE Hint

SELECT /*+ NO_RESULT_CACHE */ prod_id, SUM(amount_sold) 
FROM sales 
GROUP BY prod_id
ORDER BY prod_id;

In this example, the query instructs the database not to cache rows for a query of the sales table.


1.3 Using the RESULT_CACHE Hint in Views

The RESULT_CACHE hint applies only to the query block in which the hint is specified. 
If the hint is specified only in a view, then only these results are cached.

View caching has the following characteristics:

1. The view must be one of the following types:

-> A standard view (a view created with the CREATE ... VIEW statement)
-> An inline view specified in the FROM clause of a SELECT statement
-> An inline view created with the WITH clause

2. The result of a view query with a correlated column (a reference to an outer query block) cannot be cached.
3. Query results are stored in the server result cache, not the client result cache.
4. A caching view is not merged into its outer (or referring) query block.

The following example shows a query of the inline view view1.

SELECT *
FROM ( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count
FROM hr.employees 
GROUP BY department_id, manager_id ) view1
WHERE department_id = 30;

-> Because the RESULT_CACHE hint is specified only in the inner block, 
the results of the inner query are stored in the server result cache, 
but the results of the outer query are not cached.

WITH view2 AS
( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count
FROM hr.employees 
GROUP BY department_id, manager_id ) 
SELECT *
FROM view2 
WHERE count BETWEEN 1 and 5;

In this example,because the RESULT_CACHE hint is specified only in the query block in the WITH clause, 
the results of the employees query are eligible to be cached. Because these results are cached from 
the query in the first example, the SELECT statement in the WITH clause in the second example can 
retrieve the cached rows.

2. Using Result Cache Table Annotations

You can also use table annotations to control result caching. Table annotations affect the entire query, 
not query segments. The primary benefit of using table annotations is avoiding the necessity of adding 
result cache hints to queries at the application level. Because a table annotation has a lower precedence 
than a SQL result cache hint, you can override table and session settings by using hints at the query level.

Table 15-4 Values for the RESULT_CACHE Table Annotation

DEFAULT
-> If at least one table in a query is set to DEFAULT, then result caching is not enabled at the table level 
for this query, unless if the RESULT_CACHE_MODE initialization parameter is set to FORCE or the RESULT_CACHE 
hint is specified. This is the default value.

FORCE
-> If all the tables of a query are marked as FORCE, then the query result is considered for caching. 
The table annotation FORCE takes precedence over the RESULT_CACHE_MODE parameter value of MANUAL 
set at the session level.

2.1 Using the DEFAULT Table Annotation

CREATE TABLE sales (...) RESULT_CACHE (MODE DEFAULT);

SELECT prod_id, SUM(amount_sold)
FROM sales 
GROUP BY prod_id 
ORDER BY prod_id;

In this example, the sales table is created with a table annotation that disables result caching. 
The example also shows a query of the sales table, whose results are not considered for caching 
because of the table annotation.



2.2 Using the FORCE Table Annotation

ALTER TABLE sales RESULT_CACHE (MODE FORCE);

SELECT prod_id, SUM(amount_sold)
FROM sales 
GROUP BY prod_id 
HAVING prod_id=136;

SELECT /*+ NO_RESULT_CACHE */ * 
FROM sales
ORDER BY time_id DESC;

This example includes two queries of the sales table. The first query, which is frequently used and 
returns few rows, is eligible for caching because of the table annotation.

The second query, which is a one-time query that returns many rows, uses a hint to prevent result caching.

[4] Monitoring the Result Cache
V$RESULT_CACHE_STATISTICS
-> Lists various server result cache settings and memory usage statistics.

COLUMN name FORMAT a20
SELECT name, value
FROM V$RESULT_CACHE_STATISTICS;

Note: CLIENT_RESULT_CACHE_STATS$ the client table contains information similar.

V$RESULT_CACHE_MEMORY
-> Lists all the memory blocks in the server result cache and their corresponding statistics.

V$RESULT_CACHE_OBJECTS
-> Lists all the objects whose results are in the server result cache along with their attributes.

V$RESULT_CACHE_DEPENDENCY
-> Lists the dependency details between the results in the server result cache and dependencies among these results.


DBA_TABLES, USER_TABLES, ALL_TABLES
-> Contains a RESULT_CACHE column that shows the result cache mode annotation for the table. 
If the table is not annotated, then this column shows DEFAULT. This column applies to 
both server and client result caches.

==== 
HOL
==== 
[1] Using Result Cache feature with SQL
[2] Using Result Cache feature with PL/SQL
[3] Testing with Multitenant


[1] Using Result Cache feature with SQL

SQL> alter session set container=pdb2;

Session altered.

SQL>
SQL>
SQL> SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;

STATUS
--------------------------------------------------------------------------------
ENABLED

SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL>
SQL>
SQL> grant dba to usr_result_cache identified by test;

Grant succeeded.

SQL> alter user usr_result_cache default tablespace users quota unlimited on users;

User altered.

SQL> create table usr_result_cache.tbl_result_cache_test tablespace users as select * from dba_objects;

Table created.

SQL> select count(*) from usr_result_cache.tbl_result_cache_test;

COUNT(*)
----------
92536

SQL> insert into usr_result_cache.tbl_result_cache_test select * from usr_result_cache.tbl_result_cache_test;

92536 rows created.

SQL> /

185072 rows created.

SQL> /

370144 rows created.

SQL> /

740288 rows created.

SQL> commit;

Commit complete.

SQL> connect usr_result_cache/test@pdb2
Connected.
SQL>
SQL> show user
USER is "USR_RESULT_CACHE"
SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> show parameter result

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL>
SQL>
SQL> set autotrace on exp stat
SQL> set linesize 150
SQL> set timing on
SQL> select object_type,count(1) from tbl_result_cache_test
where object_id > 0
group by object_type
/

Elapsed: 00:00:00.61

Execution Plan
----------------------------------------------------------
Plan hash value: 1065627458

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 658 | 436 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 47 | 658 | 436 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TBL_RESULT_CACHE_TEST | 92536 | 1265K| 433 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID">0)

Statistics
----------------------------------------------------------
42 recursive calls
1 db block gets
43552 consistent gets
25704 physical reads
184 redo size
2033 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
47 rows processed

SQL> alter session set result_cache_mode=force;

Session altered.

SQL> select object_type,count(1) from tbl_result_cache_test
where object_id > 0
group by object_type;

Elapsed: 00:00:00.74

Execution Plan
----------------------------------------------------------
Plan hash value: 1065627458

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 658 | 436 (1)| 00:00:01 |
| 1 | RESULT CACHE | 3awh1nrbhw9r7dpknc173pk3nc | | | | |
| 2 | HASH GROUP BY | | 47 | 658 | 436 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TBL_RESULT_CACHE_TEST | 92536 | 1265K| 433 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("OBJECT_ID">0)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST); parameters=(nls); name="select object_type,count(1) from tbl_result_cach
e_test
where object_id > 0
group by object_type"

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43462 consistent gets
22377 physical reads
0 redo size
2033 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
47 rows processed


SQL> set autotrace traceonly exp stat
SQL> select object_type,count(1) from tbl_result_cache_test
where object_id > 0
group by object_type;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1065627458

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 658 | 436 (1)| 00:00:01 |
| 1 | RESULT CACHE | 3awh1nrbhw9r7dpknc173pk3nc | | | | |
| 2 | HASH GROUP BY | | 47 | 658 | 436 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TBL_RESULT_CACHE_TEST | 92536 | 1265K| 433 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("OBJECT_ID">0)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST); parameters=(nls); name="select object_type,count(1) from tbl_result_cach
e_test
where object_id > 0
group by object_type"

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2033 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
47 rows processed

set linesize 150
col name format a30
col cache_id format a30
col cache_key format a30
select id,name,cache_id,row_count,block_count,type,status from v$result_cache_objects;

ID NAME CACHE_ID ROW_COUNT BLOCK_COUNT TYPE STATUS
---------- ------------------------------ ------------------------------ ---------- ----------- ---------- ---------
73 USR_RESULT_CACHE.TBL_RESULT_CA USR_RESULT_CACHE.TBL_RESULT_CA 0 1 Dependency Published
CHE_TEST CHE_TEST

69 select object_type,count(1) fr 3awh1nrbhw9r7dpknc173pk3nc 47 2 Result Published
om tbl_result_cache_test
where object_id > 0
group by object_type


SELECT id, type, creation_timestamp, block_count,
column_count, pin_count, row_count
FROM V$RESULT_CACHE_OBJECTS
WHERE cache_id = '&cache_id'; 

ID TYPE CREATION_TIMESTAMP BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT
---------- ---------- ----------------------------- ----------- ------------ ---------- ----------
69 Result Sep 24 2019 12:11:37 2 2 0 47


select * from v$result_cache_memory;

ID CHUNK OFFSET FRE OBJECT_ID POSITION CON_ID
---------- ---------- ---------- --- ---------- ---------- ----------
0 0 0 NO 0 0 0
1 0 1 NO 1 0 0
2 0 2 NO 2 0 0
3 0 3 NO 3 0 0
4 0 4 NO 4 0 0
5 0 5 NO 5 0 0
6 0 6 NO 6 0 0
7 0 7 NO 7 0 0
8 0 8 NO 8 0 0
9 0 9 NO 9 0 0
10 0 10 NO 10 0 0
11 0 11 NO 11 0 0
12 0 12 NO 12 0 0
13 0 13 NO 13 0 0
14 0 14 NO 14 0 0
15 0 15 NO 15 0 0
16 0 16 NO 16 0 0
17 0 17 NO 17 0 0
18 0 18 NO 18 0 0
19 0 19 NO 18 1 0
20 0 20 NO 20 0 0
21 0 21 NO 20 1 0
22 0 22 NO 22 0 0
23 0 23 NO 23 0 0
24 0 24 NO 24 0 0
25 0 25 NO 25 0 0
26 0 26 NO 26 0 0
27 0 27 NO 27 0 0
28 0 28 NO 28 0 0
29 0 29 NO 29 0 0
30 0 30 NO 30 0 0
31 0 31 NO 31 0 0
32 1 0 NO 32 0 0
33 1 1 NO 33 0 0
34 1 2 NO 34 0 0
35 1 3 NO 35 0 0
36 1 4 NO 36 0 0
37 1 5 NO 37 0 0
38 1 6 NO 38 0 0
39 1 7 NO 39 0 0
40 1 8 NO 40 0 0
41 1 9 NO 41 0 0
42 1 10 NO 42 0 0
43 1 11 NO 43 0 0
44 1 12 NO 44 0 0
45 1 13 NO 45 0 0
46 1 14 NO 46 0 0
47 1 15 NO 47 0 0
48 1 16 NO 48 0 0
49 1 17 NO 49 0 0
50 1 18 NO 50 0 0
51 1 19 NO 51 0 0
52 1 20 NO 52 0 0
53 1 21 NO 53 0 0
54 1 22 NO 54 0 0
55 1 23 NO 55 0 0
56 1 24 NO 56 0 0
57 1 25 NO 57 0 0
58 1 26 NO 58 0 0
59 1 27 NO 59 0 0
60 1 28 NO 60 0 0
61 1 29 NO 61 0 0
62 1 30 NO 62 0 0
63 1 31 NO 63 0 0
64 2 0 NO 64 0 0
65 2 1 NO 65 0 0
66 2 2 NO 66 0 0
67 2 3 NO 67 0 0
68 2 4 NO 68 0 0
69 2 5 NO 69 0 0
70 2 6 NO 70 0 0
71 2 7 NO 71 0 0
72 2 8 NO 72 0 0
73 2 9 NO 73 0 0
74 2 10 NO 74 0 0
75 2 11 NO 74 1 0
76 2 12 NO 76 0 0
77 2 13 NO 77 0 0
78 2 14 NO 78 0 0
79 2 15 NO 79 0 0
80 2 16 NO 80 0 0
81 2 17 NO 69 1 0
82 2 18 NO 82 0 0
83 2 19 NO 83 0 0
84 2 20 NO 84 0 0
85 2 21 NO 85 0 0
86 2 22 NO 86 0 0
87 2 23 NO 87 0 0
88 2 24 NO 88 0 0
89 2 25 NO 89 0 0
90 2 26 NO 90 0 0
91 2 27 NO 91 0 0
92 2 28 NO 92 0 0
93 2 29 NO 93 0 0
94 2 30 NO 94 0 0
95 2 31 NO 95 0 0

SQL> select * from v$result_cache_statistics;

ID NAME VALUE CON_ID
---------- ------------------------------ ----------- ----------
1 Block Size (Bytes) 1024 0
2 Block Count Maximum 2048 0
3 Block Count Current 96 0
4 Result Size Maximum (Blocks) 102 0
5 Create Count Success 64 0
6 Create Count Failure 0 0
7 Find Count 72 0
8 Invalidation Count 12 0
9 Delete Count Invalid 6 0
10 Delete Count Valid 0 0
11 Hash Chain Length 1 0
12 Find Copy Count 64 0
13 Latch (Share) 0 0


SQL> set autotrace on exp stat
SQL> SELECT /*+ NO_RESULT_CACHE */ object_type,count(1) from tbl_result_cache_test
where object_id > 0
group by object_type; 2 3

Elapsed: 00:00:00.58

Execution Plan
----------------------------------------------------------
Plan hash value: 1065627458

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 658 | 436 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 47 | 658 | 436 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TBL_RESULT_CACHE_TEST | 92536 | 1265K| 433 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> set autotrace off
SQL> select * from v$result_cache_statistics where name like 'Invalidation%';

ID NAME VALUE CON_ID
---------- ------------------------------ ------------ ----------
8 Invalidation Count 12 0

SQL> insert into tbl_result_cache_test
2 select * from tbl_result_cache_test where rownum <= 10;

10 rows created.

Elapsed: 00:00:00.01
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> select * from v$result_cache_statistics where name like 'Invalidation%';

ID NAME VALUE CON_ID
---------- ------------------------------ ---------------------
8 Invalidation Count 13 0

select id,name,row_count,type,invalidations from v$result_cache_objects;

ID NAME ROW_COUNT TYPE INVALIDATIONS
---------- ------------------------------ ---------- ---------- -------------
73 USR_RESULT_CACHE.TBL_RESULT_CA 0 Dependency 1
CHE_TEST
69 select object_type,count(1) fr 47 Result 0
om tbl_result_cache_test
where object_id > 0
group by object_type

SQL> select * from v$result_cache_statistics;

ID NAME VALUE CON_ID
---------- ------------------------------ --------------------
1 Block Size (Bytes) 1024 0
2 Block Count Maximum 2048 0
3 Block Count Current 96 0
4 Result Size Maximum (Blocks) 102 0
5 Create Count Success 64 0
6 Create Count Failure 0 0
7 Find Count 74 0
8 Invalidation Count 13 0
9 Delete Count Invalid 6 0
10 Delete Count Valid 0 0
11 Hash Chain Length 1 0
12 Find Copy Count 66 0
13 Latch (Share) 0 0

SELECT object_type,count(1) from tbl_result_cache_test 
where object_id > 0 
group by object_type;


SQL> SQL> select * from v$result_cache_statistics;

ID NAME VALUE CON_ID
---------- ------------------------------ ------------------
1 Block Size (Bytes) 1024 0
2 Block Count Maximum 2048 0
3 Block Count Current 96 0
4 Result Size Maximum (Blocks) 102 0
5 Create Count Success 65 0 <<<
6 Create Count Failure 0 0
7 Find Count 74 0
8 Invalidation Count 13 0
9 Delete Count Invalid 7 0
10 Delete Count Valid 0 0
11 Hash Chain Length 1 0
12 Find Copy Count 66 0
13 Latch (Share) 0 0

13 rows selected.

SQL> set serveroutput on
SQL> 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 = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
... Fixed Memory = bytes [% of the Shared Pool]
BEGIN DBMS_RESULT_CACHE.MEMORY_REPORT; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SYS.DBMS_RESULT_CACHE", line 379
ORA-06512: at "SYS.DBMS_RESULT_CACHE", line 722
ORA-06512: at line 1

exit

Login to CDB.

sql
SQL> EXEC DBMS_RESULT_CACHE.MEMORY_REPORT(detailed=>true);
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 = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 241880 bytes [0.107% of the Shared Pool]
... Fixed Memory = 12264 bytes [0.005% of the Shared Pool]
....... Memory Mgr = 208 bytes
....... Cache Mgr = 256 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 9752 bytes
... Dynamic Memory = 229616 bytes [0.101% of the Shared Pool]
....... Overhead = 131312 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 131312 bytes
....... Cache Memory = 96K bytes (96 blocks)
........... Unused Memory = 3 blocks
........... Used Memory = 93 blocks
............... Dependencies = 34 blocks (34 count)
............... Results = 59 blocks
................... SQL = 2 blocks (1 count)
................... CDB = 28 blocks (27 count)
................... Invalid = 29 blocks (29 count)

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;

STATUS
-----------
ENABLED

SQL> EXEC DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM V$RESULT_CACHE_OBJECTS;

no rows selected

-bash-4.1$ sqlplus sys/oracle_4U@pdb2 as sysdba

SQL> EXEC DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM V$RESULT_CACHE_OBJECTS;

no rows selected

SQL> select /*+ RESULT_CACHE */ COUNT(*) from usr_result_cache.tbl_result_cache_test;

COUNT(*)
----------
1480586

SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS WHERE NAME ='Invalidation Count';

ID NAME VALUE CON_ID
---------- ------------------------- ---------------------------------------
8 Invalidation Count 0 0

SQL> EXEC DBMS_RESULT_CACHE.INVALIDATE('USR_RESULT_CACHE','TBL_RESULT_CACHE_TEST');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS WHERE NAME ='Invalidation Count';

ID NAME VALUE CON_ID
---------- ------------------------- ---------------------------------------
8 Invalidation Count 1 0

connect usr_result_cache/test@pdb2

CREATE TABLE tbl_result_cache_test2 
result_cache (mode force)
as
select * from tbl_result_cache_test
where rownum < 100;


SQL> select * from tbl_result_cache_test2 where rownum < = 2;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O
------------ ------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- ----------
SYS ICOL$ 20 2 TABLE Jul 07 2014 05:39:02 Jul 07 2014 06:11:35 2014-07-07:05:39:02 VALID N N N 1 METADATA LINK Y
SYS TAB$ 4 2 TABLE Jul 07 2014 05:39:02 Jul 07 2014 06:52:15 2014-07-07:05:39:02 VALID N N N 1 METADATA LINK Y

SQL> set linesize 150
SQL> select id,name,row_count,type,invalidations from v$result_cache_objects;

ID NAME ROW_COUNT TYPE INVALIDATIONS
---------- --------------------------------------------------------------------------------------------------------------------------
8 USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST2 0 Dependency 0
0 USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST 0 Dependency 1
9 select * from tbl_result_cache_test2 where rownum < = 2 2 Result 0
1 select /*+ RESULT_CACHE */ COUNT(*) from usr_result_cache.tbl_result_cache_test 1 Result 0


SQL> select count(*) from tbl_result_cache_test2;

COUNT(*)
----------
99

SQL> select id,name,row_count,type,invalidations from v$result_cache_objects;

ID NAME ROW_COUNT TYPE INVALIDATIONS
---------- --------------------------------------------------------------------------------------------------------------------------
8 USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST2 0 Dependency 0
0 USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST 0 Dependency 1
10 select count(*) from tbl_result_cache_test2 1 Result 0
9 select * from tbl_result_cache_test2 where rownum < = 2 2 Result 0
1 select /*+ RESULT_CACHE */ COUNT(*) from usr_result_cache.tbl_result_cache_test 1 Result 0


[2] Using Result Cache feature with PL/SQL


SQL> connect usr_result_cache/test@pdb2
Connected.
SQL>
SQL>
SQL>
SQL> create table tbl_function_result_cache tablespace users as select * from dba_objects where rownum < 3;

Table created.

SQL> insert into tbl_function_result_cache
2 select * from tbl_function_result_cache;

2 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> create sequence myseq;

Sequence created.

SQL>
SQL> create or replace function fn_result_cache(p_name in varchar2)
return varchar2
RESULT_CACHE
is
id number:=0;
begin
select myseq.nextval into id from dual;
id := id + 1;
return initcap(lower(p_name));
end;
2 3 4 5 6 7 8 9 10 11
12 /

Function created.

SQL>
SQL> define _editor=vi
SQL> ed
Wrote file afiedt.buf

1 create or replace function fn_without_result_cache(p_name in varchar2)
2 return varchar2
3 is
4 id number:=0;
5 begin
6 select myseq.nextval into id from dual;
7 id := id + 1;
8 return initcap(lower(p_name));
9* end;
SQL> r
1 create or replace function fn_without_result_cache(p_name in varchar2)
2 return varchar2
3 is
4 id number:=0;
5 begin
6 select myseq.nextval into id from dual;
7 id := id + 1;
8 return initcap(lower(p_name));
9* end;

Function created.

SQL> select fn_without_result_cache(object_name) from tbl_function_result_cache;

FN_WITHOUT_RESULT_CACHE(OBJECT_NAME)
------------------------------------
Icol$
Tab$
Icol$
Tab$

SQL> select myseq.currval from dual;

CURRVAL
----------
4

SQL> select fn_result_cache(object_name) from tbl_function_result_cache;

FN_RESULT_CACHE(OBJECT_NAME)
------------------------------
Icol$
Tab$
Icol$
Tab$

SQL> select myseq.currval from dual;

CURRVAL
----------
6

SQL> select fn_result_cache(object_name) from tbl_function_result_cache;

FN_RESULT_CACHE(OBJECT_NAME)
-----------------------------
Icol$
Tab$
Icol$
Tab$

SQL> select myseq.currval from dual;

CURRVAL
----------
6

set linesize 150
col name format a30
col cache_id format a30
col cache_key format a30
select id,name,cache_id,row_count,block_count,type,status from v$result_cache_objects;

ID NAME CACHE_ID ROW_COUNT BLOCK_COUNT TYPE STATUS
---------- ------------------------------ ------------------------------ ---------- ----------- ---------- ---------
12 USR_RESULT_CACHE.FN_RESULT_CAC USR_RESULT_CACHE.FN_RESULT_CAC 0 1 Dependency Published
HE HE

16 "USR_RESULT_CACHE"."FN_RESULT_ 3nkrgjm0ggfau9cjm4vdqdppwn 1 1 Result Published
CACHE"::8."FN_RESULT_CACHE"#96
3b7b52b7a7c411 #1

13 "USR_RESULT_CACHE"."FN_RESULT_ 3nkrgjm0ggfau9cjm4vdqdppwn 1 1 Result Published
CACHE"::8."FN_RESULT_CACHE"#96
3b7b52b7a7c411 #1


SQL> select fn_result_cache(object_name) from tbl_function_result_cache;

FN_RESULT_CACHE(OBJECT_NAME)
-----------------------------
Icol$
Tab$
Icol$
Tab$

SQL> select myseq.currval from dual;

CURRVAL
----------
6

SQL> set serveroutput on
SQL> 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 = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 209736 bytes [0.086% of the Shared Pool]
... Fixed Memory = bytes [% of the Shared Pool]
... Dynamic Memory = 209736 bytes [0.086% of the Shared Pool]
....... Overhead = 111432 bytes
....... Cache Memory = 96K bytes (96 blocks)
........... Unused Memory = 24 blocks
........... Used Memory = 72 blocks
............... Dependencies = 38 blocks (38 count)
............... Results = 34 blocks
................... SQL = 2 blocks (2 count)
................... PLSQL = 2 blocks (2 count)
................... CDB = 24 blocks (24 count)
................... Invalid = 6 blocks (6 count)

PL/SQL procedure successfully completed.

SQL> show user
USER is "USR_RESULT_CACHE"

sqlplus sys/oracle_4U@pdb2 as sysdba

SQL> set serveroutput on
SQL> 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 = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 209736 bytes [0.086% of the Shared Pool]
... Fixed Memory = bytes [% of the Shared Pool]
... Dynamic Memory = 209736 bytes [0.086% of the Shared Pool]
....... Overhead = 111432 bytes
....... Cache Memory = 96K bytes (96 blocks)
........... Unused Memory = 24 blocks
........... Used Memory = 72 blocks
............... Dependencies = 38 blocks (38 count)
............... Results = 34 blocks
................... SQL = 2 blocks (2 count)
................... PLSQL = 2 blocks (2 count)
................... CDB = 24 blocks (24 count)
................... Invalid = 6 blocks (6 count)

PL/SQL procedure successfully completed.


[3] Testing with Multitenant

-bash-4.1$ sqlplus usr_result_cache/test@pdb2


SQL> select /*+ RESULT_CACHE */ COUNT(*) from tbl_result_cache_test;

COUNT(*)
----------
1480586

SQL> SELECT count(*) from V$RESULT_CACHE_OBJECTS;

COUNT(*)
----------
2

SQL> set linesize 10000
SQL> set pagesize 1000
SQL> select * from V$RESULT_CACHE_OBJECTS;

ID TYPE STATUS BUCKET_NO HASH NAME NAMESPACE CREATION_TIMESTAMP CREATOR_UID DEPEND_COUNT BLOCK_COUNT SCN COLUMN_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT ROW_SIZE_MAX ROW_SIZE_MIN ROW_SIZE_AVG BUILD_TIME LRU_NUMBER OBJECT_NO INVALIDATIONS SPACE_OVERHEAD SPACE_UNUSED CACHE_ID CACHE_KEY CHECKSUM EDITION_ID DB_ CON_ID
---------- ---------- --------- ---------- ---------- ---------------
0 Dependency Published 1488 3822859728 USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST Sep 24 2019 13:04:35 135 1 1 7803248 0 0 0 0 0 0 0 0 0 96240 0 0 0 USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST USR_RESULT_CACHE.TBL_RESULT_CACHE_TEST 0 0 No 4
1 Result Published 1716 1226200756 select /*+ RESULT_CACHE */ COUNT(*) from tbl_result_cache_test SQL Sep 24 2019 13:04:35 135 1 1 7803248 1 0 0 1 8 8 8 19 461 0 0 342 675 596p8bvt3n4gm55v07caqnbbcb gfz0h2rxqvww80h7kyfgy7k9ph 1839517456 0 No 4

SQL> quit

-bash-4.1$ sql

SQL> SELECT count(*) from V$RESULT_CACHE_OBJECTS;

COUNT(*)
----------
2

SQL> set linesize 10000
SQL> set pagesize 1000
SQL> select * from V$RESULT_CACHE_OBJECTS;

ID TYPE STATUS BUCKET_NO HASH NAME NAMESPACE CREATION_TIMESTAMP CREATOR_UID DEPEND_COUNT BLOCK_COUNT SCN COLUMN_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT ROW_SIZE_MAX ROW_SIZE_MIN ROW_SIZE_AVG BUILD_TIME LRU_NUMBER OBJECT_NO INVALIDATIONS SPACE_OVERHEAD SPACE_UNUSED CACHE_ID CACHE_KEY CHECKSUM EDITION_ID DB_ CON_ID
---------- ---------- --------- ---------- ---------- ---------------
0 Dependency Published 1488 3822859728 object-id(96240) Sep 24 2019 13:04:35 135 1 1 7803248 0 0 0 0 0 0 0 0 0 96240 0 0 0 object-id(96240) object-id(96240) 0 0 No 4
1 Result Published 1716 1226200756 select /*+ RESULT_CACHE */ COUNT(*) from tbl_result_cache_test SQL Sep 24 2019 13:04:35 135 1 1 7803248 1 0 0 1 8 8 8 19 461 0 0 342 675 596p8bvt3n4gm55v07caqnbbcb gfz0h2rxqvww80h7kyfgy7k9ph 1839517456 0 No 4

SQL> show user
USER is "SYS"
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB2 READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

--> cdb

SQL> EXEC DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM V$RESULT_CACHE_OBJECTS;

no rows selected

--> PDB2

-bash-4.1$ sqlplus sys/oracle_4U@pdb2 as sysdba

SQL> SELECT * FROM V$RESULT_CACHE_OBJECTS;

no rows selected

exit;

======================
Patch to Documentation:
======================
Database Administrator’s Guide
6 Managing Memory
6.4.3.4.1 The Result Cache and Shared Pool Size
6.4.3.8 Specifying the Result Cache Maximum Size

Database Performance Tuning Guide
15 Tuning the Result Cache

Thank you for visiting this blog 🙂