11G
The Result Cache
User Rating: 
/ 0
- Details
-
Parent Category: Articles
-
Created on Sunday, 21 March 2010 19:58
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Sunday, 21 March 2010 19:58
-
Written by Guy Lambregts
-
Hits: 4201
The Result Cache
A DBA can configure a server site SQL result cache also used by the PL/SQL Function result cache and which is yet another configurable pool in the Oracle' s instance architecture. a DBA can also configure a client result cache assuming the client is using OCI for its connection. The client result cache can be configured at the database instance level, however its settings can be overwritten by configuring the sqlnet.ora
The SQL result cache
We take a deeper look at this select statement
SQL> select max(er_rate) "maximum",pm_pk,pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm group by pm_pk,pm_name;
maximum PM PM_NAME
184.963623 PD Palladium
10.9999493 Ag Silver
919.99072 PT Platinium
879.999964 Au Gold
Execution Plan
Plan hash value: 29312987
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 4 | 112 | 232 (4)| 00:00:03 |
| 1 | HASH GROUP BY | | 4 | 112 | 232 (4)| 00:00:03 |
| 2 | NESTED LOOPS | | 4 | 112 | 231 (4)| 00:00:03 |
| 3 | VIEW | VW_GBC_5 | 4 | 64 | 231 (4)| 00:00:03 |
| 4 | HASH GROUP BY | | 4 | 112 | 231 (4)| 00:00:03 |
| 5 | TABLE ACCESS FULL| T_EXCHANGERATE_ER | 125K| 3423K| 225 (1)| 00:00:03 |
|* 6 | INDEX UNIQUE SCAN | SYS_IOT_TOP_75299 | 1 | 12 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
6 - access("ITEM_1"="PM_PK")
Statistics
24 recursive calls
0 db block gets
830 consistent gets
822 physical reads
0 redo size
704 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
If we repeat this select a few times we 'll notice the value for consistent gets does not decrease significantly.
Let us now use the client result cache, note we use the /*+ result_cache */ hint.
SQL> select /*+ result_cache */ max(er_rate) "max",pm_pk,pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm group by pm_pk,pm_name;
max PM PM_NAME
184.963623 PD Palladium
10.9999493 Ag Silver
919.99072 PT Platinium
879.999964 Au Gold
Execution Plan
Plan hash value: 29312987
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 4 | 112 |232 (4)| 00:00:03 |
| 1 | RESULT CACHE | 722dnc59tpj6ag59dz4kuxss1p | | | | |
| 2 | HASH GROUP BY | | 4 | 112 |232 (4)| 00:00:03 |
| 3 | NESTED LOOPS | | 4 | 112 |231 (4)| 00:00:03 |
| 4 | VIEW | VW_GBC_5 | 4 | 64 |231 (4)| 00:00:03 |
| 5 | HASH GROUP BY | | 4 | 112 |231 (4)| 00:00:03 |
| 6 | TABLE ACCESS FULL| T_EXCHANGERATE_ER | 125K| 3423K|225 (1)| 00:00:03 |
|* 7 | INDEX UNIQUE SCAN | SYS_IOT_TOP_75299 | 1 | 12 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
7 - access("ITEM_1"="PM_PK")
Result Cache Information (identified by operation id):
1 - column-count=3; dependencies=(PM.T_PRECIOUSMETALS_PM, PM.T_EXCHANGERATE_ER); parameters=(nls); name="select /*+ result_cache */ max(er_rate) "max",pm_pk,
pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm grou"
Statistics
1 recursive calls
0 db block gets
827 consistent gets
822 physical reads
0 redo size
700 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
OK still the same value for consistent gets, but what when we execute it a second time
SQL> select /*+ result_cache */ max(er_rate) "max",pm_pk,pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm group by pm_pk,pm_name;
max PM PM_NAME
184.963623 PD Palladium
10.9999493 Ag Silver
919.99072 PT Platinium
879.999964 Au Gold
Execution Plan
Plan hash value: 29312987
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 4 | 112 |232 (4)| 00:00:03 |
| 1 | RESULT CACHE | 722dnc59tpj6ag59dz4kuxss1p | | | | |
| 2 | HASH GROUP BY | | 4 | 112 |232 (4)| 00:00:03 |
| 3 | NESTED LOOPS | | 4 | 112 |231 (4)| 00:00:03 |
| 4 | VIEW | VW_GBC_5 | 4 | 64 |231 (4)| 00:00:03 |
| 5 | HASH GROUP BY | | 4 | 112 |231 (4)| 00:00:03 |
| 6 | TABLE ACCESS FULL| T_EXCHANGERATE_ER | 125K| 3423K|225 (1)| 00:00:03 |
|* 7 | INDEX UNIQUE SCAN | SYS_IOT_TOP_75299 | 1 | 12 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
7 - access("ITEM_1"="PM_PK")
Result Cache Information (identified by operation id):
1 - column-count=3; dependencies=(PM.T_PRECIOUSMETALS_PM, PM.T_EXCHANGERATE_ER); parameters=(nls); name="select /*+ result_cache */ max(er_rate) "max",pm_pk,
pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm grou"
Statistics
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
700 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
Still there ? we notice there is not a second execution of the statement anymore since the number of consistent gets is zero.
We had to use the hint /*+ result_cache */ since the instance initialization parameter result_cache_mode was set to manual.
SQL> show parameter result_cache
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
You can also set the parameter result_cache_mode to FORCE
SQL> alter system set result_cache_mode=force;
System altered.
The reverse hint /*+ no_result_cache */ can be when you absolutely never wants to cache the result of a sql statement.
There are obvioulsy a range of v$views
V$RESULT_CACHE_STATISTICS, V$RESULT_CACHE_MEMORY, V$RESULT_CACHE_OBJECTS, V$RESULT_CACHE_DEPENDENCY
SQL> select * from v$result_cache_objects;
ID TYPE STATUS BUCKET_NO HASH
NAME
NAMES CREATION_ 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
1 Dependency Published 1681 182625937
PM.T_EXCHANGERATE_ER
21-MAY-09 86 2 1 2596719 0
0 0 0 0 0 0
0 0 75306 0 0 0
PM.T_EXCHANGERATE_ER
PM.T_EXCHANGERATE_ER
0 Dependency Published 3812 1104277220
PM.T_PRECIOUSMETALS_PM
21-MAY-09 86 2 1 2596719 0
0 0 0 0 0 0
0 0 75299 0 0 0
PM.T_PRECIOUSMETALS_PM
PM.T_PRECIOUSMETALS_PM
3 Result Published 1458 3987105202
select /*+ result_cache */ max(er_rate) "max",pm_pk,pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm grou
SQL 21-MAY-09 86 2 1 2596952 3
0 6 4 39 34 37
30 3 0 0 320 568
722dnc59tpj6ag59dz4kuxss1p
aw31wv4xmjw8m3rqf0873nu9gc
5 Result Published 2679 3973511799
SELECT DECODE('A','A','1','2') FROM DUAL
SQL 21-MAY-09 86 0 1 2620556 0
0 1 1 0 0 0
0 2 0 0 224 800
0y8dgk314f9f8bz05qsrrny8u8
9hvtcbmtgay38bkft5ff0gdhh1
4 Result Published 366 309633390
SELECT USER FROM DUAL
SQL 21-MAY-09 86 0 1 2620556 0
0 1 1 0 0 0
0 1 0 0 205 819
96nffc5z9a85b04xqd6k87ucjh
5vpvw17m3xs2ra0jrcgvmq4t14
2 Result Published 947 3478975411
select /*+ result_cache */ max(er_rate) "maximum",pm_pk,pm_name from t_exchangerate_er,t_preciousmetals_pm where pm_pk=er_fk_pm
SQL 21-MAY-09 86 2 1 2596719 3
0 4 4 39 34 37
30 0 0 0 320 568
08kz62fpr7bzq11acy74t7cjht
2980qhsn6cj446k9jdd9su4hxz
6 rows selected.
The PL/SQL Function Result Cache
The PL/SQL Function Result Cache
The Client Result Cache
The client result cache stores the result of queries in the client cache. If queries are often repeated, like selects from static lookup tables they benefit from the client result cache. Less round trips to the database are needed.
The client result cache is for read only tables or for almost read only tables. If the table data is often subject to DML then there are additional round trips to the database needed, thus decreasing the added value of the client cache.
To enable the client result cache the static instance initialization parameter client_result_cache_size must have a non zero value.
SQL> show parameter client_result
NAME TYPE VALUE
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
SQL> alter system set client_result_cache_size=3M scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 486542436 bytes
Database Buffers 41943040 bytes
Redo Buffers 5828608 bytes
Database mounted.
Database opened.
SQL> show parameter client_result
NAME TYPE VALUE
client_result_cache_lag big integer 3000
client_result_cache_size big integer 3M
Settings at the database level can though be overwritten by configuring the $ORACLE_HOME/network/admin/sqlnet.ora file at the client site. More precisely one can use parameters like : oci_result_cache_max_size, oci_result_cache_max_rset_size, oci_result_cache_rset_rows
The dynamic performance view client_result_cache_stats$ can be queried.
There is also a new package dbms_result_cache
SQL> desc dbms_result_cache;
PROCEDURE BYPASS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BYPASS_MODE BOOLEAN IN
SESSION BOOLEAN IN DEFAULT
FUNCTION FLUSH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
PROCEDURE FLUSH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA 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> 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 = 103536 bytes 0.043% of the Shared Pool
... Fixed Memory = 5140 bytes 0.002% of the Shared Pool
... Dynamic Memory = 98396 bytes 0.040% of the Shared Pool
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 7 blocks
........... Used Memory = 25 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 24 blocks
................... SQL = 8 blocks (8 count)
................... Invalid = 16 blocks (10 count)
PL/SQL procedure successfully completed.
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
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 = 5140 bytes 0.002% of the Shared Pool
... Fixed Memory = 5140 bytes 0.002% of the Shared Pool
... Dynamic Memory = 0 bytes 0.000% of the Shared Pool
PL/SQL procedure successfully completed.
Also the the dbms_result_cache.invalidate can be of interest.
SQL> select id,name from v$result_cache_objects;
ID
NAME
2
PM.T_EXCHANGERATE_ER
4
select avg(er_rate),er_fk_pm,extract(month from er_time) Month from t_exchangera
te_er group by extract(month from er_time),er_fk
3
select * from (select er_rate,er_fk_pm,extract(month from er_time) Month from t_
exchangerate_er) pivot (min(er_rate) for er_fk_p
1
SELECT DECODE('A','A','1','2') FROM DUAL
0
SELECT USER FROM DUAL
SQL> exec dbms_result_cache.invalidate_object(3);
PL/SQL procedure successfully completed.
