Comments below ...

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.


<snip>

OK, but in the thread you posted from asktom.oracle.com, the response to
the poster was:

"v$open_cursor can be misleading -- it is cursors that have been opened
at some 
point and may (or may not be) still open.  It is useful in helping to
track down 
cursor leaks -- but it shows you more then just "really truely open"
cursors."

and later...

"The issue is v$open_cursor will show you MORE open cursors then you
really have 
(due to some cursor caching done by plsql and other environments).  You
need to 
goto v$sessstat to get the REAL currently opened number of cursors.

There may be cursors in v$open_cursor that are not counted in the
v$sesstat view 
for stat #3, they will be really closed if we need those slots again."

I'm not citing that article to prove you wrong, but because I don't know
anything at all about Oracle and am getting confused by reading
different articles that seem to say slightly different things.

[rr]  I was testing on a DB instance where I was the only user at that time.  I 
did not bother to check v$sesstats (I probably should have), but I will leave 
this to your testing.

So my question is, when Oracle tells me I've exceeded the maximum
cursors limit, am I exceeding a limit of "really open" cursors or a
limit of "have been opened" cursors?

[rr]  From the 9.2 docs:

OPEN_CURSORS

Parameter type    Integer

Default value     50

Parameter class   Dynamic: ALTER SYSTEM

Range of values   1 to 4294967295 (4 GB -1)

OPEN_CURSORS specifies the maximum number of open cursors (handles to private 
SQL areas) a session can have at once. You can use this parameter to prevent a 
session from opening an excessive number of cursors. This parameter also 
constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid 
having to reparse as statements are reexecuted by a user.

It is important to set the value of OPEN_CURSORS high enough to prevent your 
application from running out of open cursors. The number will vary from one 
application to another. Assuming that a session does not open the number of 
cursors specified by OPEN_CURSORS, there is no added overhead to setting this 
value higher than actually needed.

> When I called disconnect() on my database handle, the cursors were
> closed.
> 
> I have searched metalink, google, and otn for some reference on this
> issue and found nothing that indicates there is a way to close a ref
> cursor object prior to disconnect.

What about this:

http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Binding_Cursors

At the end of the "Binding Cursors" section it says that cursors need to
be (and can be) closed with code like this:

  $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
  $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET }
  );
  $sth3->execute;

That's where I got one of the methods I've tried for closing the cursor.
I've also had other people who know Oracle but not Perl tell me there's
no way that should work, but it's in the docs. So that's why I'm
confused. Some people tell me I can't close my cursors this way, others
say that I can't close cursors at all, and others say that cursors get
closed automatically by the Perl garbage collector.

Is anyone able to shed some light on the situation?

Thanks!

Mike

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is 
intended 
to be for the use of the individual or entity named above. If you are not the 
intended recipient, please be aware that any disclosure, copying, distribution 
or use of the contents of this information is prohibited. Please notify the
sender  of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Reply via email to