Re: "Ora-01000 maximum open cursors exceeded" after update from 10.2.0.1 to 10.2.0.3

On Jun 3, 11:25 pm, poschma <marti..._at_utanet.at> wrote:
> Hi all!
>
> After updating our Database from 10.2.0.1 to 10.2.0.3 I get the error
> Ora-01000 in our application (we use the proC precompiler).
>
> When I look at the v$open_cursor, I see the number of cursors
> increasing all the time.
> In our application we have a lot of select statements and every
> statement is a cursor. But the cursor is always closed after the
> statement. And we also have a lot of transactions. And after a
> rollback the cursors should be closed automatic (am I right?).
>
> It seams that neither a "EXEC SQL CLOSE ..." nor a "rollback" close
> the cursors any more!
>
> The parameter session_cached_cursors is 20, the parameter open_cursors
> is set to 300.
>
> With 10.2.0.1 we have no problems.
> Has anyone an idea whats running wrong? Is there a new parameter?
>
> Thanks in advance

There has been work done on error handling of cursors, for example, see metalink Note:3091541.8 which references a non-published bug, and bug 5053820. Hard to tell what is going on, of course, but I'm speculating your app has some invalid cursors which were handled differently before some fix. Also see Note:2055810.6 , I don't see how internal changes in cursor handling _wouldn't_ affect this stuff.

If you have the test environments available, you might try running in both environments and watching the cursors. Also see Note:296377.1, it's possible you are running into an optimizer change that necessitates more child cursors.

Nothing really wrong with setting the cursors way up, anyways. My particular environment (cursor-profligate vendor OCI code) runs well with session_cached_cursors 250, open_cursors 2500. It's been quite a while since I've even thought about them.

jg

--
@home.com is bogus.
http://www.informationweek.com/shared/printableArticle.jhtml?articleID=199900170