OTN Discussion Forums : OCI and Statement Caching ...

oracls

Posts: 2
Registered: 07/03/07

OCI and Statement Caching
Posted: Jul 3, 2007 7:14 AM

Hi,

I'm trying to understand statement caching and its usage via OCI as described in the OCI documentation (Oracle 9.2.0, http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci09adv.htm#464310).

1. The documentation states "Statement caching refers to the feature, first introduced in release 9.2, that provides and manages a cache of statements for each session. In the server, it means that cursors are ready to be used without the need to parse the statement again."
As far as I know, SQL statements are always cached in Oracle, although the corresponding cursors might be closed again. If I understand the OCI documentation correctly, the term "statement caching" really means cursor caching. Is that correct? Or is there additional caching of statements within the oci, i.e. on the client side?

2. The OCI documentation states that the following calls implement statement caching: OCIStmtPrepare2() and OCIStmtRelease()
From this I gather that there will be no statement caching when using OCIStmtPrepare() and OCIHandleFree(). So I changed our application code to use OCIStmtPrepare2/OCIStmtRelease instead of the former two.
Then I tried to measure whether using OCIStmtPrepare2/OCIStmtRelease had any effect. For this I run a testcase of my application and watched the cursor cache using the following statement which I found on the web and which supposedly tells how many cursors are currently cached in a session:

SELECT a.VALUE,
s.username,
s.sid,
s.serial#
FROM v$sesstat a,
v$statname b,
v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'session cursor cache count'
AND s.sid = <the sid of my application's session>;

And really, the count steadily increased during the test run until it reached 100 which is the configured maximum of cached cursors in our database.

But when I changed the application code back to using OCIStmtPrepare/OCIHandleFree there was no difference when running the test again! I would have expected the count to stay at zero?! Probably I'm misunderstanding something or the query does not do what I'm expecting?

3. I further tried to measure the cache hit ratio during the same test run by using the following statement (again found on the web, in the same blog entry as the previous one):

select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= <the sid of my application's session> and prs.sid= cach.sid;

When running with OCIStmtPrepare2/OCIStmtRelease I got 70 cache hits (out of about 390). When running with OCIStmtPrepare/OCIHandleFree I got 51 cache hits. This I do not understand either. Can anyone explain this to me?

4. When calling OCIStmtRelease I'm setting the mode parameter to 0x0010. The OCI documentation mentions the mode OCI_STMTCACHE_DELETE which means to delete the stmt from the cache. The oci.h does not contain this parameter at all, but just the following which I assumed to be the same, so I used it:
/* OCIStmtRelease Modes */
#define OCI_STRLS_CACHE_DELETE 0x0010 /* Delete from Cache */

What I do not really understand is the meaning of "Delete from Cache". As I'm trying to use caching, I'd just like to release the statement/cursor to the cache for reuse, not delete it from the cache. Curiously when using OCI_DEFAULT instead, the measurement statements above detect no caching, i.e. the cache count stays at zero and there are no cache hits. This seems to be contrary to the documentation. Can anyone shed light on this?

I'm grateful for any help!

Thanks a lot.

Jonah H. Harris

Posts: 786
Registered: 04/24/01

Re: OCI and Statement Caching
Posted: Jul 7, 2007 9:04 PM

in response to: oracls

Reply

Hello,

I can answer all of your questions... but I'm quite tired and there's a lot to cover. If someone else doesn't catch it in the next day or so, I'll get you all the info.

cj2

Posts: 759
Registered: 01/10/01

Re: OCI and Statement Caching
Posted: Jul 9, 2007 3:56 PM

in response to: oracls

Reply

I look forward to Jonah's reply, but briefly the OCI statement cache
feature is a client-side cache and an adjunct to the standard
server-side cache. Using the OCI cache means the text of a
re-executed statement doesn't need to be transmitted more than once to
the server - some kind of hash value is sent instead. This hash value
allows the server to index directly into its cache, so it doesn't have
to do a soft parse of a re-executed statement.

Overall, for some extra parsing at the client side you have reduced
network traffic and reduced database load.

To show the oci statement cache is working, look at the hard and soft
(i.e. total - hard) parses done by the database when statements are
re-executed. The soft parse count should be less when the oci
statement cache is enabled.

OCI_STRLS_CACHE_DELETE means "don't store the statement I just
executed in the client side cache".

-- cj

oracls

Posts: 2
Registered: 07/03/07

Re: OCI and Statement Caching
Posted: Jul 23, 2007 2:15 AM

in response to: oracls

Reply

@Jonah: I am looking forward for your reply, too.

@cj: Thanks for your reply, it has really helped me a lot to understand at least some of my problems.

I'll try to answer my questions myself, repeating or rephrasing those which are still open (all errors in applying cj's explanations are mine):

1. As cj explained, the OCI statement cache is a client side cache whose contents are associated with statements in the shared pool on the database server. The cursors of statements within the oci statement cache remain open. They are closed when a statement is deleted from the cache (or if the server needs the space for other things). Statements outside the oci statement cache will be cached, too, but their cursors will not remain open (they might be moved to the session cursor cache, though, if used several times http://cf. next item).

2. The session cursor cache is something different from oci statement caching. Remember that the cursor remains open for statements in the oci statement cache. The session cursor cache is used for other statements which get parsed as usual. When Oracle detects that a cursor is used for the third time (or something like that), it puts the cursor in the session cursor cache.
Therefore when making use of oci statement caching ideally all statements reside in the cache and their cursors remain open, so the usage of the session cursor cache should drop to zero.
The reason for seeing the session cursor cache to be used completely (100 cursors) in my tests is the small default size of the oci statement cache (20) which is way too small for my app which uses about 300 different statements during the test (note: all are using binding variables).
As cj described, the parse count (total) is dropping towards zero when repeating the test run and making the oci statement cache big enough (I had to use 300. Using 200 was still too small to have a noticeable effect). Then the parse count (total) drops from about 300 in the first run to about 30 in the second and 5 in the third.
An open question remains: is there really a difference between the second and third run to be expected or shouldn't they be approximately the same?

3. The same argumentation holds for the session cursor cache hit rate. The hit rate increases when using oci statement caching, because fewer cursors must be cached in the session cursor cache thereby reducing cache thrashing.

4. As cj explained, OCIStmtRelease deletes the statement from the oci cache. This is indeed what I want at the place where I am calling it, because I'm holding the application's statement objects in a cache of their own (which has the same size as the oci statement cache) to reuse these relative expensive objects. The call to OCIStmtRelease is done when removing a statement object from the application's cache.

5. This is a new question: I've tried all this with the old code in our application which uses OCIStmtPrepare/OCIHandleFree instead of OCIStmtPrepare2/OCIStmtRelease. This showed the same drop of parse count (total) although the oci documentation states that OCIStmtPrepare2 is neccessary to use oci statement caching. How can this be?

I'd appreciate corrections of my explanations and of course answers to the remaining questions. Thanks in advance!

cfarrell

Posts: 44
Registered: 04/17/06

Re: OCI and Statement Caching
Posted: Jul 23, 2007 11:18 AM

in response to: oracls

Reply

Some clarification on #4 (excuse me if I'm reading your analysis wrong): OCIStmtRelease does not delete the statement from the OCI statement cache but rather releases the statement back to the cache for reuse by later OCIStmtPrepare2 calls. Each call to OCIStmtPrepare2 should be matched by a call to OCIStmtRelease for the OCIStmt pointer.

If a call to OCIStmtPrepare2 does not have a corresponding call to OCIStmtRelease then the statement will not only be parsed again, it is also a memory leak because the next prepare will allocate another statement handle without freeing the first one. You can check this by printing the value of the OCIStmt pointer returned by OCIStmtPrepare2 with and without a matching call to OCIStmtRelease; for a given SQL text the pointer value should be the same after the first prepare if caching is working.

Fundamentally what the OCI statement cache seeks to minimize is soft parsing. It ends up being the difference between a soft parse and no parse. Incidentally you can simulate client cursor caching on the server with the SESSION_CACHED_CURSORS parameter for applications that do not cache on the client side.

user608118

Posts: 1
Registered: 11/29/07

Re: OCI and Statement Caching
Posted: Nov 29, 2007 10:45 AM

in response to: oracls

Reply

Were there any further thoughts on this topic? Some of the questions seem still to be open.

A related question I have regards the closing of cursors that may be implicitly opened. If I use OCIStmtPrepare, is there a cursor opened (even if the statement is just "commit")? And if so, how is it closed? Particularly, how is it closed if I use OCIStmtPrepare multiple times for the same OCIStmt without executing OCIHandleFree and OCIHandleAlloc in between to recreate OCIStmt?

Could this practice of OCIStmt reuse lead to the following error?
ORA-01000: maximum open cursors exceeded

And finally, if I do re-use the OCIStmt in this fashion, can I force any implicitly opened cursor to close by executing OCIStmtFetch2 with the "number of rows" parameter set to 0?

I appreciate your thoughts.

Jonah H. Harris

Posts: 786
Registered: 04/24/01

Re: OCI and Statement Caching
Posted: Nov 30, 2007 12:16 PM

in response to: user608118

Reply

Were there any further thoughts on this topic? Some
of the questions seem still to be open.

Yes, I guess I'll have to answer them

A related question I have regards the closing of
cursors that may be implicitly opened. If I use
OCIStmtPrepare, is there a cursor opened (even if the
statement is just "commit")?

Yes.

And if so, how is it closed? Particularly, how is it closed
if I use OCIStmtPrepare multiple times for the same
OCIStmt without executing OCIHandleFree and
OCIHandleAlloc in between to recreate OCIStmt?

IIRC, reusing OCIStmt will leave the previously prepared query cursors open until your connection ends.

Could this practice of OCIStmt reuse lead to the
following error?
ORA-01000: maximum open cursors exceeded

Yes.

And finally, if I do re-use the OCIStmt in this
fashion, can I force any implicitly opened cursor to
close by executing OCIStmtFetch2 with the "number of
rows" parameter set to 0?

The end-of-fetch cancells the cursor and frees most resources on the server-side. To close the cursor, you can use OCIHandleFree, but I believe the actual close won't occur until the next server round-trip. I'd have to check, and it's not elegant, but I believe the old OCI7 oclose call still works.

Let me look into this stuff to make sure I get my answers 100%

kmohan

Posts: 320
Registered: 01/10/01

Re: OCI and Statement Caching
Posted: Dec 3, 2007 12:31 AM

in response to: Jonah H. Harris

Reply

-You need to use OCIStmtPrepare2 and OCIStmtRelease calls for using OCI statement cache. Do not try OCIHandleAlloc and OCIHandleFree on the handles that you get/pass from/to these two functions.
- The cursors are kept open for all the statements that are cached. The number of those cursors is governed by the statement cache size that is set. You need to set it in accordance with maximum open cursors allowed by the session. If you set the cache size larger than the maximum cursors allowed, you would run into ORA-01000 if the cache is full.
- When the cache is operating at its full size, the unused statements are removed from the cache and the most recent ones are cached. So the number of cursors kept open the statement cache is limited by the cache size setting.

Jonah H. Harris

Posts: 786
Registered: 04/24/01

Re: OCI and Statement Caching
Posted: Dec 3, 2007 7:31 AM

in response to: kmohan

Reply

This is pretty much correct, but I think they're also asking about server-side cursor caching. OCI8 removed the ability to specifically manage cursors as Oracle thought it would make life, "easier".

However, as OCI reuses cursors internally, if the developer isn't mindful of this, many times it will cause too many open cursors per session resulting in ORA-01000. The reason is, if a cursor is not in a cancel state, OCI assumes it cannot be reused and creates another one. The easiest way to limit the number of open cursors is by forcing a cursor to cancel (via end-of-fetch), or by freeing the statement handle and recreating it.

You are definitely correct in that you want to make sure to use OCIStmtRelease on any statement prepared via OCIStmtPrepare2.