Home » RDBMS Server » Performance Tuning » OCI client result cache (11.2.x)
OCI client result cache [message #573706] Wed, 02 January 2013 09:57 Go to next message
John Watson
Messages: 4567
Registered: January 2010
Location: Global Village
Senior Member
Hello - I'm trying to demonstrate the working of the OCI client result cache. I've set some parameters,
orcl> sho parameter result_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 1000000
result_cache_max_result              integer     5
result_cache_max_size                big integer 1984K
result_cache_mode                    string      FORCE
result_cache_remote_expiration       integer     0


If I understand the docs correctly, that should be all that is needed.

I've complied and run the cdemoqc and cdemoqc2 OCI demos, but I never get anything in the V$CLIENT_RESULT_CACHE_STATS or CLIENT_RESULT_CACHE_STATS$ views. This is probably because the sessions exit after running the queries.

So I've also tried repeating arbitrary queries through cdemo2, which gives a persistent session, but there is still nothing in those views and furthermore the v$result_cache_objects.scan_count for my queries keeps increasing. So I don't think think the client side cache is working.
Is anyone using this? Or (here's hoping) does anyone have a demo that definitely works?

Thank you for any insight.
Re: OCI client result cache [message #573711 is a reply to message #573706] Wed, 02 January 2013 10:18 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
I think, you have to disable the server result cache.
Re: OCI client result cache [message #573712 is a reply to message #573706] Wed, 02 January 2013 10:32 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Server result cache will be used beginning with the third execution of sql. May be it is the same with the client result cache.
Re: OCI client result cache [message #573816 is a reply to message #573712] Thu, 03 January 2013 05:55 Go to previous messageGo to next message
John Watson
Messages: 4567
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying.
I've tried all the combinations of enabling/disabling the client and the server cache, but I don't ever get client side caching. At least, I don't think I do. The evidence for this is that when I disable the server cache by setting result_cache_max_size=0 every query (I'm just running select * from scott.emp, from within the cdemo2 shell) results in incrementing the logical reads statistic for emp in v$segment_statistics; when I enable the server cache, that logical reads statistic remains constant but the v$result_cache_objects.scan_count increments with every execution. So it looks to me as though the query result is coming either from the server cache or from the buffer cache.

I've found note 864736.1 which looks like an easier way to test it, perhaps that will do what I want.
Re: OCI client result cache [message #573826 is a reply to message #573816] Thu, 03 January 2013 07:03 Go to previous message
John Watson
Messages: 4567
Registered: January 2010
Location: Global Village
Senior Member
Update, in case anyone is interested:

The code in note 864736.1 does work, I can demonstrate client side caching enabled with a hint in a query, or by decorating the tables, or by setting the result_cache_mode=force for the system.
Previous Topic: Sql Running Slowly
Next Topic: Memory_target and sga_target in oracle 11gR2
Goto Forum:
  


Current Time: Fri Sep 19 21:11:16 CDT 2014

Total time taken to generate the page: 0.09936 seconds