Re: 11g: how to get Client Result Cache to work?

On Oct 12, 1:17 am, DA Morgan <damor..._at_psoug.org> wrote:
> Vsevolod Afanassiev wrote:
> > On Oct 10, 11:02 pm, DA Morgan <damor..._at_psoug.org> wrote:
> >> Vsevolod Afanassiev wrote:
> >>> I am trying to get Client Result Cache to work in Oracle 11g database.
> >>> This feature is described in OCI Programmer's Guide, pages 10-22 -
> >>> 10-27.
> >>> 1. Parameter CLIENT_RESULT_CACHE_SIZE has been set to 1000000 and
> >>> database restarted. I can see new value in V$PARAMETER.
> >>> 2. Parameter RESULT_CACHE_MODE has been set to FORCE.
> >>> I am definitely getting results cached on the server: I can see it in V
> >>> $RESULT_CACHE_* views and performance of large table scans is sub-
> >>> second. However, view CLIENT_RESULT_CACHE_STATS$ is empty, so I assume
> >>> that
> >>> results do not get cached on the client. Any idea what is missing?
> >>> How the teste were done:
> >>> - Using SQL*Plus running on the same server as database. I tried
> >>> Bequeath
> >>> connections and connections though listener (listener is also 11g).
> >>> - Large table is created (no indexes)
> >>> - Full table scan is performed using SELECT COUNT(*)
> >>> so result set consists of one row.
> >>> - There is no sqlnet.ora
> >> Run the RESULT CACHE demos in Morgan's Library atwww.psoug.org.
>
> >> They work.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> >> - Show quoted text -
>
> > Daniel,
>
> > Thanks
>
> > My question was about CLIENT-side result cache, not server-side cache.
> > Please correct me if I am wrong:
> > - Views V$RESULT_CACHE_* describe server-side cache
> > - View CLIENT_RESULT_CACHE_STATS$ describes client-side cache
> > - If this view is empty then client-side cache isn't working
>
> Incorrect. There is only one result cache and it resides on the server
> so that it can be shared by all sessions. CLIENT_RESULT_CACHE_STATS is
> exactly what the names implies. It is statistics related to the client's
> use of that cache.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel,

Let me quote from Oracle Call Interface (OCI) Programmer's Guide, page 10-24
"When client result caching is enabled, the query result can be cached on the client or on the
server or both. The client result caching can be enabled even if the server result cache (that is
enabled by default) is disabled".

There are other statements in this guide that clearly imply that there are two caches: one
on the server and another on the client. See pages 10-22 - 10-27.

Of course it applies only to OCI client. Non-OCI clients (e.g. thin JDBC) don't have client-side cache. Received on Thu Oct 11 2007 - 16:37:52 CDT