open_cursor count keeps growing

[Moe]

Hi,

I have a web application that accesses an oracle 10 g database. I
noticed under home tab an alert saying: current open cursor count is
2000. category is database limit.
My system is getting slower and when i do select count(*) from
v$open_cursor i get 4200 and keeps growing.
Im not sure how open cursor works, does oracle close them when they
are not in use? or do i have something wrong in my application and its
not closing them?
My opne_cursor parameter is set to 500.
Thanks

Moe

[DA Morgan]

Moe wrote: Hi, I have a web application that accesses an oracle 10 g database. I noticed under home tab an alert saying: current open cursor count is 2000. category is database limit. My system is getting slower and when i do select count(*) from v$open_cursor i get 4200 and keeps growing. Im not sure how open cursor works, does oracle close them when they are not in use? or do i have something wrong in my application and its not closing them? My opne_cursor parameter is set to 500. Thanks Moe

Who wrote the application? Your choice is to either fix the application
or buy one that is far better designed. This version of this app. was
obviously never tested: Or tested well.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Moe]

A developer at my company. I believe he is missing to close the cursors
somewhere.
is oracle supposed to take care of that automatically or the
application should close it?.
I know that i can flush shared_pool but open_cursor are located in the
PGA, is that correct?
Is it possible to flush the PGA instead of restarting the database
untill we figure out the problem with the application?

Moe

[Moe]

I just found out that the application uses OJB apache for database
connectivity and not JDBC. Does oracle support OJB?

[DA Morgan]

Moe wrote: A developer at my company. I believe he is missing to close the cursors somewhere. is oracle supposed to take care of that automatically or the application should close it?. I know that i can flush shared_pool but open_cursor are located in the PGA, is that correct? Is it possible to flush the PGA instead of restarting the database untill we figure out the problem with the application? Moe

Depends on the way the code was written. Without seeing the code it
is impossible to tell.

What I can tell you is that any developer that doesn't test their
code ... should be escorted to the nearest Burger King and given
an application that is more in tune with their skill set.

Flush nothing. Make the developer fix it and test it.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[DA Morgan]

Moe wrote: I just found out that the application uses OJB apache for database connectivity and not JDBC. Does oracle support OJB?

Don't know but versions are important. We don't know what version
of your database or what version of any other tool in the stack.

Ask the vendor you got it from to prove certification. It is not
an Oracle responsibility.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Moe]

I talked to oracle support and they were telling me that open cursor
count = 4000 does not mean they are in use and that oracle leave them
for reuse. so my question was to the support guy is how do i find out
what cursors are being really used. He will get back to me on that
cause he did not have an answer.
Is this how oracle behaves? . Should the open cursor count drop when
the sql command is done executing?
Im doing select * from v$open_cursor to check count and it seems that
this does not tell me what cursors are being used. Any suggestions?

[Sybrand Bakker]

On 15 Sep 2005 11:28:21 -0700, "Moe" <mwahidi@emanagelaw.com> wrote:
Is this how oracle behaves? . Should the open cursor count drop whenthe sql command is done executing?Im doing select * from v$open_cursor to check count and it seems thatthis does not tell me what cursors are being used. Any suggestions?

The cursor count shouldn't necessarily drop as Oracle can hold and
reopen cursors. However, especially if you are using the lower levels
of interaction like Pro*C, and OCI, it is up to you whether you hold
or release cursors.
Also be aware the open_cursor parameter is a *per session* limit, not
a database wide limit. As far as I'm concerned, if the open_cursor
parameter is 1000, and the users are still hitting ora-1000 maximum
open cursors exceeded, there is something wrong in the application.
Also be aware cursors opened by dbms_sql are not automatically closed.

I have a script to differentiate between used and un-used cursors. I
got this from Metalink, the Oracle Support Site. I currently can't
access it.

--
Sybrand Bakker, Senior Oracle DBA

[Moe]

I never get the ORA-1000 error telling me that my session reached the
limit. All i got is a warning in dbconsole (category database limit)
and that my open_cursor threshold is 1200 and im at 4000. Oracle
support told me you can disable this warning. I had like 4000 open
cursors an hour ago and now its at 5200, this means that my application
is using the 4000 and had to open more or it's not releasing the
cursors, otherwise it would have reused the released cursors as oracle
say.