Override Client Cache Configuration from the Client

The client can optionally override the database settings by maintaining a client configuration file.  However, if the client cache is disabled at the server level, the client configuration settings will be ignored.   

The following settings can be overridden by the client using the sqlnet.ora:

• oci_result_cache_max_size - maximum size of the result cache 

• oci_result_cache_max_rset_size - maximum size of any result set

• oci_result_cache_max_rset_rows - maximum number of rows in any result set

The server settings are able to be overridden by managing the client cache on the database level. However, the settings are controlled for all OCI clients in a centralized manner.  These settings should only be overridden after consulting with the DBA.

Using the Client Cache

The client cache can be utilized from the OCI client application by using the hint result_cache in the query.  Alternatively, the result_cache_mode initialization parameter can be set to FORCE in order to force the use of the result cache whenever possible.  When the client cache has been enabled, using the result cache hint will store the query results in the client's process memory upon the first query on the table.  Subsequent executions of that query will return the results using the client cache.  Consider a table static_lookups that contains a listing of lookup names and values.  If the OCI client uses this table to retrieve values of a multi-user application, the client cache can improve the efficiency and speed of querying this table.  The following is the autotrace output for a query without using the client cache:

SQL> select lookup_value from static_lookups where lookup_name = 'CA';

Execution Plan
----------------------------------------------------------
Plan hash value: 53038468

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     2 |    38 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| STATIC_LOOKUPS |     2 |    38 |    22   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - filter("LOOKUP_NAME"='CA') 

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         77  consistent gets
          0  physical reads
          0  redo size
        497  bytes sent via SQL*Net to client
        434  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

SQL> 

By using the result cache hint, the query results are cached in the client's process memory on the first execution, and in subsequent executions, there is a significant reduction in resource consumption:

SQL> select /*+ result_cache */ lookup_value from static_lookups where lookup_name = 'CA'; 

Execution Plan
----------------------------------------------------------
Plan hash value: 53038468 

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT   |                            |     2 |    38 |    22   (0)|
00:00:01 |
|   1 |  RESULT CACHE      | f1bdppb5chzgtaxuxw70nq49hz |       |       |           
|          |
|*  2 |   TABLE ACCESS FULL| STATIC_LOOKUPS             |     2 |    38 |    22   (0)|
00:00:01 |
--------------------------------------------------------------------------------------
----------

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

   2 - filter("LOOKUP_NAME"='CA')
 

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

   1 - column-count=1; dependencies=(VJ.STATIC_LOOKUPS); parameters=(nls);
name="select
   /*+ result_cache */ lookup_value from static_lookups where lookup_name = 'CA'" 

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

SQL> 

The result cache is utilized to return the results with minimal overhead.  Even in a brand new session, the client result cache is used to return results for the same query.  This improvement can also be observed for operations on larger tables.  Consider a query that computes a count of order_lines_nov07, which has about one million rows and is used for mostly read-only reporting purposes:

SQL> select count(*) from ORDER_LINES_NOV07; 

  COUNT(*)
----------
   1000000

Execution Plan
----------------------------------------------------------
Plan hash value: 336444469 

------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |   512   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C0010418 |  1162K|   512   (1)| 00:00:07 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1898  consistent gets
          0  physical reads
          0  redo size
        510  bytes sent via SQL*Net to client
        434  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

In a reporting application, this count might be continually recalculated even though the result will not change since it is used for historical information.  By using the client cache, this query can be greatly improved:

SQL> select /*+ result_cache */ count(*) from ORDER_LINES_NOV07; 

  COUNT(*)
----------
   1000000

Execution Plan
----------------------------------------------------------
Plan hash value: 336444469
--------------------------------------------------------------------------------------
------

| Id  | Operation              | Name                       | Rows  | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT       |                            |     1 |   512   (1)|
00:00:07 |
|   1 |  RESULT CACHE          | 072j3kqba3bwb91a78u11zj2tn |       |           
|          |
|   2 |   SORT AGGREGATE       |                            |     1 |           
|          |
|   3 |    INDEX FAST FULL SCAN| SYS_C0010418               |  1162K|   512   (1)|
00:00:07 |
--------------------------------------------------------------------------------------
------

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

   1 - column-count=1; dependencies=(VJ.ORDER_LINES_NOV07); attributes=(single-row);
name="select /*+ result_cache */ count(*) from ORDER_LINES_NOV07" 

Note
-----
   - dynamic sampling used for this statement 

Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        434  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>

The improvement for this query, by using the result cache, is very significant.  This reduction in resources to return the results improves the response time and greatly reduces the CPU and I/O on the server.