On Oct 11, 3:52 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Vsevolod Afanassiev wrote:
> > 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-Hidequoted 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.
>
> Apparently OCI is giving you additional capabilities I've not seen
> before. Something new to look at. Thanks.
> --
> 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 -
Client side cache will work for re-executes, ie statement caching is
needed. Sqlplus doesn't have it now.
Try JDBC OCI as example.
The stats view is updated periodically on future roundtrips to server
as mentioned in OCI docs.
Received on Fri Oct 26 2007 - 00:49:50 CDT
