RSS Feed

Method R Discussion Group

This isn't exactly performance-related other than it deals with increased memory consumption.  We have a home-grown web-based app that uses OCI.  We use scrollable cursors for some queries.  We recently switched from using the 9i client to the 10g Instantclient and it seems that PGA memory is not being released like it is with the 9i client. 

I apologize in advance for providing OCI-specific function calls but I want any OCI gurus to understand what we're doing in our attempt to release resources.  After fetching the data we call OCIStmtFetch2() with the nrows parameter set to 0 to cancel the cursor (per the OCI documentation on scrollable cursors).  We then call OCIHandleFree() and OCIPing() to free the statement handle which should also free up the storage associated with that handle.  With the 9i client we see the PGA memory increase while the statement is being executed and data is fetched and then shrink after the request is completed.  With the 10g client the PGA memory increases but does not decrease once the request is complete.

So, having said all that, I have a few questions:

- From an OCI perspective, are we doing the right things to close the scrollable cursor and release the associated memory immediately?
- Are there some optimizations happening behind the scenes when using the 10g client where Oracle retains the memory, perhaps because it thinks we might reuse it even though we've said we're done with it?
- Is this just the price we pay for using scrollable cursors? 

Thanks,
Riley

Oh, but your question IS about performance. Your application may not be at this moment suffering from protracted response time (and maybe that's why you say this quesiton is not about performance) but it could suffer in the future.

W.r.t. cursor management, you're doing exactly what I do. I haven't executed 9i v. 10g tests in the manner you describe and so I can't confirm your statement.

If you haven't found this bug in metalink and you're sure that your test definition is valid, then consider doing this:

1. modify cdemosc.c to execute a query that consumes a large amout of PGA.
2. build cdemosc
3. run it while connect to 9i and record PGA statistics before and after the execution
4. repeat step #3 connecting to 10g

Then, if your results were repeated, then create an service request attaching cdemosc.c and the statistics to it.

My guess is this is probably a port specific bug but if it's not, then as more customers migrate their OCI apps to the 10g client, then they are going to thank you for doing this work now.

I just realized I left something out of my prior post.

I haven't used OCIPing and I probably would never use it. Besides that, I can't find any Oracle references to using OCIPing in relation to releasing PGA memory.

I suspect your reference says to use OCIPing because OCIHandleFree doesn't perform a round trip to the server. But I'll bet that reference doesn't say whether other round trip inducing functions would suffice. Besides that, there's another more pressing issue.

If the duration of the execute+fetches is short, then incurring an additional round trip (via OCIPing) could be a major contributor to the task's response time.

Let's say that your program looks something like this:

loop
  OCIHandleAlloc(envhnd, &stmhnd, OCI_HTYPE_STMT, 0, 0)
  OCIStmtPrepare(stmhnd, errhnd, stmttxt, strlen(stmttxt)+1, OCI_NTV_SYNTAX, OCI_DEFAULT);
  OCIStmtExecute(svc, stmhnd, errhnd, 1, 0, 0, 0, OCI_STMT_SCROLLABLE_READONLY);
  OCIStmtFetch2(stmhnd, errhnd, 1, OCI_FETCH_LAST, OCI_DEFAULT);
  OCIStmtFetch2(stmhnd, errhnd, 0, OCI_FETCH_CURRENT, OCI_DEFAULT);
  OCIHandleFree(stmhnd, OCI_HTYPE_STMT);
  /*OCIPing(svchnd, errhnd, OCI_DEFAULT);*/
end loop;

The body of this loop should require one round trip if (1) the length of stmttxt is relatively small and (2) the number of rows returned by the first OCIStmtFetch2 call is small enough to fit into one SQL*Net packet. This is due to the bundling of OCI calls and the prefetching of rows that it performs by default.

The closer the server side FETCH call's response time goes to zero, then adding OCIPing makes the response time of the loop body come closer to doubling. Yikes.

My gut reaction says that PGA memory for row source execution should be deallocated as soon as  OCIStmtFetch2(stmhnd, errhnd, 0, OCI_FETCH_CURRENT, OCI_DEFAULT) is executed and so freeing the handle should not be necessary.

But even if it isn't, then the next execution of the loop should free the PGA row source memory when the next OCIStmtExecute begins. Therefore, the above loop (with OCIPing commented out) should never leak memory. It might have memory from one outstanding execution but it should definitely not leak.