Cache consistency
– consistency maintained by receiving notifications on subsequent roundtrip to server
– in a lreatively idle client, cache can trail behind DB no more than CACHE_LAG milliseconds
– changes invalidate affected cached results
– cache bypassed if session has outstanding xactions on tables in query
– free application from manually having to check for changes, poll the database, refresh result-sets
– it is very difficult to program these changes because the database does not expose all the elements required.
– you need the 11g client and the 11g server to make this work properly
– you need to enable statement caching to make this work. It can be done in the client or on the mid-tier
OCI consistent client cache enabling
– works with OCI-based drivers. Requires enterprise edition to make this work
– on server set CLIENT_RESULT_CACHE_SIZE, must be non-zero upto 2G, CLIENT_RESULT_CACHE_LAG – 3000ms default. Setting LAG to zero disables lag
– on client (set in sqlnet.ora)
– OCI_RESULT_CACHE_MAX_SIZE (optional)
– OCI_RESULT_CACHE_MAX_RSET_SIZE (optional)
– OCI_RESULT_CACHE_MAX_RSET_ROWS (optional)
the client values override the server settings and can be done temporarily
the query requires /*+ result_cache */ hint in the code. This will be automated at a later date
look for candidate queries in AWR
– frequent queries in Top SQL
– identify candidate queries on r-o/r-mostly tables
– sprinkle the hint on queries and measure
monitor usage
– client_result_cache_stats$