12 | 12 | 2017
Latest Articles
Popular Articles

11G

The Result Cache

User Rating:  / 0
PoorBest 

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.