open_cursor count keeps growing
Moe
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
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
damo...@x.washington.edu
(replace x with u to respond)
Moe
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
connectivity and not JDBC. Does oracle support OJB?
DA Morgan
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.
DA Morgan
> 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.
Moe
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
>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?
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
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.