What are the pros and cons of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE

cacheoraclescalability

I have multiple questions

For Oracle Database 11g Release 11.2.0.1.0

what are the benefits of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE.
How will the CLIENT side cache be kept sync if data changes at server side.

Do we have a AUTO mode for RESULT_CACHE_MODE? What is the recommended mode for this?

If i execute a single query 10 times with CLIENT_RESULT_CACHE enabled will the query be run 10 times at the server?

Please point me towards any documentation regarding this.Thanks in advance

Best Answer

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.

Related Question