What are the benefits? - Faster data access with less disk access.
How will the client side cache be kept in sync? From the Oracle Call Interface Programmer's Guide:
The client cache transparently keeps the result set consistent with
any session state or database changes that can affect its cached
result sets.
When a transaction modifies the data or metadata of any of the
database objects used to construct that cached result, invalidation is
sent to the OCI client on its subsequent round-trip to the server. If
the OCI application does no database calls for a period of time, then
the client cache lag setting forces the next OCIStmtExecute() call to
make a database call to check for such invalidations.
The cached result sets relevant to database invalidations are
immediately invalidated, and no subsequent OCIStmtExecute() calls can
match such result sets. The OCI statement handles currently fetching
from these cached result sets, at the time such invalidations are
received, can continue fetching from this (invalidated) cached result
set.
The next OCIStmtExecute() call by the process may cache the new result
set if there is space available in the cache. The OCI client result
cache periodically reclaims unused memory.
If a session has a transaction open, OCI ensures that its queries that
reference database objects changed in this transaction go to the
server instead of the client cache.
This consistency mechanism ensures that the OCI cache is always close
to committed database changes. If the OCI application has relatively
frequent calls involving database round-trips due to queries that
cannot be cached, (such as DMLs, OCILob calls, and so on) then these
calls transparently keep the client cache up-to-date with database
changes.
Note that sometimes when a table is modified, a trigger can cause
another table to be modified. OCI client result cache is sensitive to
all such changes.
When the session state is altered, for example, if NLS session
parameters are modified, this can cause the query results to be
different. The OCI result cache is sensitive to such changes and on
subsequent query executions, returns the correct query result set. The
current cached result sets are kept (and not invalidated) for any
other session in the process to match; otherwise, these result sets
get "Ruled" after a while. There are new result sets cached
corresponding to the new session state.
If the application must keep track of all such database and session
changes it can be cumbersome and prone to errors. Hence, OCI result
cache transparently keeps the result sets consistent with any database
or session changes.
The OCI client result cache does not require thread support in the
client.
*Do we have an auto mode for RESULT_CACHE_MODE?* - The Oracle Database Reference shows that the two options for this setting are MANUAL and FORCE.
What is the recommended mode for this? - This would depend on what your needs are.
To understand the benefits of the result cache, look at the Validation of the Client Result Cache of the Oracle Call Interface Programmer's Guide referenced earlier.
