OraFAQ Forum: Performance Tuning » OCI client result cache

Home » RDBMS Server » Performance Tuning » OCI client result cache (11.2.x)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OCI client result cache [message #573706]

Wed, 02 January 2013 09:57

John Watson
Messages: 8977
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.

Report message to a moderator

Re: OCI client result cache [message #573711 is a reply to message #573706]

Wed, 02 January 2013 10:18

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I think, you have to disable the server result cache.

Report message to a moderator

Re: OCI client result cache [message #573712 is a reply to message #573706]

Wed, 02 January 2013 10:32

LNossov
Messages: 318
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.

Report message to a moderator

Re: OCI client result cache [message #573816 is a reply to message #573712]

Thu, 03 January 2013 05:55

John Watson
Messages: 8977
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.

Report message to a moderator

Re: OCI client result cache [message #573826 is a reply to message #573816]

Thu, 03 January 2013 07:03

John Watson
Messages: 8977
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.

Report message to a moderator

Previous Topic:	Sql Running Slowly
Next Topic:	Memory_target and sga_target in oracle 11gR2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 01:17:12 CDT 2025