Oracle 11.2 Result_cache Description

1 Related parameters
Result_cache_max_result: Specifies the maximum amount of cache that can be used for any result, the default value is 5%, but you can specify any percentage value between 1 and 100, which can be implemented at the system and session level;
result_cache_max_size: An integer multiple of 32k, if the result cache value is set to 0, the result cache is disabled and cannot exceed 75% of the shared pool;
result_cache_remote_expiration: You can specify the time, in minutes, that the results of dependent remote database objects remain valid, and the default value is 0, which means that the results of remote objects are not used by the cache;
Result_cache_mode: Default is MANUAL, that is, add hint/ + Result_cache/Use result cache only
Enable Result cache:

alter system set result_cache_max_result=5;alter system set result_cache_max_size=20m;

2 Related stored procedures
Query memory allocation:

  Sql>set serveroutput on;          Sql> exec dbms_result_cache.memory_reportr 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 bytesmaximum Cache Size = 1280K bytes (blocks) Maximum Result Size = 64K bytes (+ blocks) [Memory]total Mem Ory = 202160 bytes [0.110% of the Shared Pool] ... Fixed Memory = 5352 bytes [0.003% of the Shared Pool] ... Dynamic Memory = 196808 bytes [0.107% of the Shared Pool] .... Overhead = 131272 bytes .... Cache Memory = 64K bytes (up to blocks) ..... Unused Memory = Blocks ...... Used Memory = Blocks ....... ..... Dependencies = Blocks (ten count) ...... ..... Results = Blocks ........ ....... SQL = Blocks (count) ........ ....... Invalid = 5 Blocks (5 count) sql> select Dbms_result_cache.status from dual; ENABLED  

Delete all existing results and empty the cache:
EXECUTE DBMS_RESULT_CACHE.FLUSH;
Check:
select * from GV$RESULT_CACHE_OBJECTS
To invalidate a particular result:

beginDBMS_RESULT_CACHE.INVALIDATE(‘SH‘,‘SALES‘);end;

select cache_id,lru_number,db_link,status,bucket_no,hash,name,namespace,type,statusfrom GV$RESULT_CACHE_OBJECTS where name like ‘%sales%‘;

CACHE_ID    LRU_NUMBER  DB_LINK STATUS  BUCKET_NO   HASH    NAME    NAMESPACE   TYPE    STATUS3gqafv8xzpk9t535y6dgfmyhjt  0   No  Invalid 2055    571566087   "select /*+ result_cache */ prod_id,sum(amount_sold) from sh.sales group by prod_id order by prod_id"   SQL Result**    Invalid**

3 Execution Plan view
SQL> select /*+ result_cache */ prod_id,sum(amount_sold) from sales group by prod_id order by prod_id;
Execution Plan

Plan hash value: 4109827725--------------------------------------------------------------------------------------------------------------------| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

|   0 | SELECT STATEMENT      |                            |    72 |   648 |   680  (24)| 00:00:09 |       |       ||   1 |  RESULT CACHE         | g23n3fafz6vxs65351bmca3jq8 |       |       |            |          |       |       ||   2 |   SORT GROUP BY       |                            |    72 |   648 |   680  (24)| 00:00:09 |       |       ||   3 |    PARTITION RANGE ALL|                            |   918K|  8075K|   557   (8)| 00:00:07 |     1 |    28 ||   4 |     TABLE ACCESS FULL | SALES                      |   918K|  8075K|   557   (8)| 00:00:07 |     1 |    28 |

Result Cache Information (identified by operation id):

   1 - column-count=2; dependencies=(SH.SALES); name="select /*+ result_cache */ prod_id,sum(amount_sold) from sales group by prod_id order by prod_id"

You can see that the result cache is not using the result cache:

  Select Prod_id,sum (amount_sold) from the sales group by prod_id order by prod_id; Execution Plan----------------------------------------------------------plan hash value:4109827725--------------- -------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart|   Pstop |----------------------------------------------------------------------------------------------| 0 |       SELECT STATEMENT |    |   72 |   648 | 680 (24) |       00:00:09 |       |   ||  1 |       SORT GROUP by |    |   72 |   648 | 680 (24) |       00:00:09 |       |   ||   2 |       PARTITION RANGE all|   |  918k|   8075k| 557 (8) |     00:00:07 |    1 |   28 | |    3 | TABLE ACCESS Full |   SALES |  918k|   8075k| 557 (8) |     00:00:07 |    1 | |  

You can also force objects to use result cache:
alter table sales result_cache(mode force);
Cancel:
alter table sales result_cache(mode default);
4 related views
(G) V$result_cache_statistics: Lists various cache settings and memory usage statistics
Select from v$result_cache_statistics
(G) V$result_cache_memory: Lists all memory blocks and corresponding statistics
Select from V$result_cache_memory
(G) V$result_cache_objects: List all objects (cache results and dependencies) and their properties
Select Cache_id,lru_number,db_link,status,bucket_no,hash,name,namespace,type,status
From gv$result_cache_objects where name is like '%sales% ';
(G) V$result_cache_dependency: Lists dependency details and dependencies between cache results
Select b.owner,b.object_name,a.* from v$result_cache_dependency a,all_objects b where a.object_no=b.object_id;

5 Precautions
1 binding variables are different and cannot be hit
2 is best for statements that require access to a large number of rows and return only a few of them, and are recommended for use in OLAP systems/reporting systems

Oracle 11.2 Result_cache Description