- Systems with large amounts of memory can take advantage of the result cache to improve response times of repetitive queries.
- The result cache stores the results of SQL queries and PL/SQL functions in an area called
Result Cache Memory in the shared pool. When these queries and functions are executed repeatedly, the results are retrieved directly from the cache memory. This results in a faster response time. The cached results stored become invalid when data in the dependent database objects is modified. The use of the result cache is database-wide decision. Result cache itself is instance specific and can be sized differently on different instances.
- Documentation stats "To disable the result cache in a cluster, you must explicitly set the
RESULT_CACHE_MAX_SIZE initialization parameter to 0 during every instance startup " but thats not the case setting (11.2.0.1)result_cache_max_size=0 with scope=both and sid='*' will disable it permanently.
Using the Result Cache Mode
You can control the result cache mechanism using the RESULT_CACHE_MODE parameter in the server initialization (init.ora) file. This parameter can be set at the system, session, or table level to the following values:
MANUAL
In MANUAL mode, the results of a query can only be stored in the result cache by using the /*+ result_cache */ hint.FORCE
In FORCE mode, all results are stored in the result cache. When set to FORCE, all results use the cache, if possible. You can use the no_result_cache hint to bypass the cache when using the FORCE mode.
If the RESULT_CACHE_MODE parameter is set to MANUAL, and you want to store the results of a query in the result cache, then you must explicitly use the /*+ result_cache */ hint in your query, as shown in the following example:
SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
When you execute the query, OCI will look up the result cache memory to verify if the result for the query already exists in the OCI client cache. If it exists, then the result is retrieved directly out of the cache. Otherwise, the query is executed on the database, and the result is returned as output and stored in the client result cache memory.
If the RESULT_CACHE_MODE parameter is set to FORCE, and you do not want to include the result of the query in the result cache, then you must use the /*+ no_result_cache */ hint in your query, as shown in the following example:
SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno
Restrictions on Using the SQL Query Result Cache
You cannot cache results when you use the following database objects or functions in your SQL query:
- Dictionary and temporary tables
- Sequence
CURRVAL and NEXTVAL pseudo columns
- SQL functions
current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, andsys_timestamp
- Non-deterministic PL/SQL functions
Cached results are parameterized with the parameter values if any of the following constructs are used in the query:
- Bind variables.
- The following SQL functions:
dbtimezone, sessiontimezone, userenv/sys_context (with constant variables), uid, and user.
- NLS parameters.
SQL> set linesize 180
SQL> set timing on
SQL> set autot on explain stat
SQL> SELECT
2 PRODUCT_ID,sum(QUANTITY),avg(sales_amount)
3 from sh.order_items
4 group by product_id;
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
52 80 100
72 6 72000
32 6 700
1 134 14700
6 2 70
2 45 2066.66667
21 1 900
90 18 16400
87 4 6200
12 40 12000
74 12 8000
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
8 20 8400
45 2 80
13 rows selected.
Elapsed: 00:00:26.37
Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
963 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
923 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL>
=====================================With Result Cache Hint =========================
select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id;
SQL> 2
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
32 6 700
1 134 14700
2 45 2066.66667
6 2 70
87 4 6200
52 80 100
72 6 72000
21 1 900
8 20 8400
45 2 80
90 18 16400
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
74 12 8000
12 40 12000
13 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
926 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
====================
session 2 - update -
update sh.order_items set QUANTITY=100 where ORDER_ID=5 and PRODUCT_ID=1;
1 row updated.
SQL> commit;
=====================
Session 1:
SQL> /
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
32 6 700
52 80 100
1 194 14700
72 6 72000
6 2 70
2 45 2066.66667
87 4 6200
90 18 16400
21 1 900
45 2 80
12 40 12000
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
74 12 8000
8 20 8400
13 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
926 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL> /
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
1 134 14700
32 6 700
72 6 72000
52 80 100
6 2 70
2 45 2066.66667
8 20 8400
21 1 900
45 2 80
90 18 16400
87 4 6200
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
12 40 12000
74 12 8000
13 rows selected.
Elapsed: 00:00:06.29
Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"
Statistics
----------------------------------------------------------
576 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
923 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
-----
SQL> /
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
1 134 14700
32 6 700
72 6 72000
52 80 100
6 2 70
2 45 2066.66667
8 20 8400
21 1 900
45 2 80
90 18 16400
87 4 6200
PRODUCT_ID SUM(QUANTITY) AVG(SALES_AMOUNT)
---------- ------------- -----------------
12 40 12000
74 12 8000
13 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 2831044826
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 2 (0)| 00:00:01 | | | | | |
| 1 | RESULT CACHE | 7mk3vfypbbvp90axh2xw10pzja | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 13 | 130 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| ORDER_ITEMS | 19 | 190 | 2 (0)| 00:00:01 | 1 | 13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(SH.ORDER_ITEMS); name="select /*+ result_cache */
PRODUCT_ID,sum(QUANTITY),avg(sales_amount) from sh.order_items group by product_id"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
923 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
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 = 10496K bytes (10496 blocks)
Maximum Result Size = 524K bytes (524 blocks)
[Memory]
Total Memory = 176680 bytes [0.015% of the Shared Pool]
... Fixed Memory = 12704 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 163976 bytes [0.014% of the Shared Pool]
....... Overhead = 131208 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 26 blocks
........... Used Memory = 6 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 4 blocks
................... SQL = 2 blocks (2 count)
................... Invalid = 2 blocks (2 count)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
SQL> begin
dbms_result_cache.flush;
end; 2 3
4
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
--- Invalidate the Indivisual TABLE/OBJECT rather full RESULT cACHE.
SQL > begin
dbms_result_cache.invalidate('ARUP','CUSTOMERS');
end;
OWNER, OBJECT_NAME ....
