CODEWITHSUNDEEP
oracle-databaseoracle-call-interface
Andy Dufresne
Andy Dufresne

Reputation: 6180

Oracle OCI Client Cache Not Working

I am using Oracle Database 11g Release 11.2.0.2.0 and trying to enable the oci client cache. It isn't working. The configuration changes I made to enable it are -

  1. Enabled the client result set cache by setting the server side parameter 'client_result_cache_size' to 10485760 (10 MB)
  2. Restarted the oracle instance after setting the above parameter
  3. Added a table annotation by executing the statement ALTER TABLE emp RESULT_CACHE (MODE FORCE). I verified that the annotation is applied by query the user table later.
  4. Enabled statement caching on the client side i.e. on the JDBC driver.
  5. Used prepared statements to execute the query so that statement caching kicks in. From the driver logs I verified that execution of subsequent queries after the first one used the same statement handle.

After executing the select prepared statement query for three times I checked the CLIENT_RESULT_CACHE_STATS$ view. But this view didn't result in any rows.

As part of troubleshooting I even tried adding the /*+ RESULT_CACHE */ hint to the query but the view didn't gave any result.

From the profiler (flextracer) I could see that OCI calls are still made to the server to fetch the result set of the select query. Also on enabling sql trace I could see from tkprof that every execution of the query increased the number of rows fetched on the server which indicates that the client result set caching in OCI isn't working.

Are there any steps which I have missed?

Thanks in advance.

Upvotes: 2

Views: 1374

Answers (1)

Andy Dufresne
Andy Dufresne

Reputation: 6180

I realized that the caching functionality I am trying to enable is not available in Standard Edition. It's only available in Enterprise Edition - http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/editions.htm#BABDJGGI

Upvotes: 3

Related Questions