You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Bruno CROS <br...@gmail.com> on 2006/10/09 17:22:25 UTC
Monitoring DB cursor leaks
Hi all,
Experiencing some MAX OPEN CURSOR oracle errors (ORA -01000) , i'm looking
for the cursors leaks ( unclosed ResultSet it seems) in code.
I guess that broker queries well close the result set. So when report
queries results iteration are closed ? on HasNext()==false ?
Is it possible to check open cursors when releasing connections ? and by the
way, close them if needed ?
Note : MAX_OPEN_CURSOR Oracle parameter is actually set at 500 by connection
( and 1000 sooner )
Oracle 10, OJB 1.0.4 with some litle updates
Regards.
Re: Monitoring DB cursor leaks
Posted by Bruno CROS <br...@gmail.com>.
http://oracleandy.blogspot.com/2006/03/vopencursor-find-cursors-that-are.html
Perhaps i found something to detect bad code, but i don't known how to set
the session parameter in OJB connections. The equivalent of :
alter session set "_close_cached_open_cursors" = TRUE
Did someone already do ?
Thanks
On 10/10/06, Bruno CROS <br...@gmail.com> wrote:
>
> Hi,
>
> Here is my connector, Oracle9i yet...
>
> May be i have to set the jdbc-level at 3.0 ?
>
> Referring to this article, it seems that it is a hard to resolve trouble.
>
> http://www.orafaq.com/node/758
>
> Except looking into code, how to check "real" cursors leak (if they are)?
>
> Regards
>
> <jdbc-connection-descriptor
> jcd-alias="default"
> default-connection="true"
> platform="Oracle9i"
> jdbc-level=" 2.0"
> driver="oracle.jdbc.driver.OracleDriver"
> protocol="jdbc"
> subprotocol="oracle"
> dbalias="thin:@XXXX:1521:ZZZZZ"
> username="uuuuuuuuu"
> password="pppppppp"
> batch-mode="false"
> useAutoCommit="1"
> ignoreAutoCommitExceptions="false"
> >
>
>
>
> ---------- Forwarded message ----------
> From: Danilo Tommasina <dtommasina@risksys.com >
> Date: Oct 9, 2006 6:08 PM
> Subject: Re: Monitoring DB cursor leaks
> To: OJB Users List <oj...@db.apache.org>
>
> Hi,
>
> we were having that problem too, long time ago. You should use the
> 'Oracle9i' Platform setting in your jdbc-connection-descriptor instead of
> the Oracle
> 'Platform', that should fix the problem. IIRC the 'Oracle' Platform does a
> caching of PreparedStatements, however the Oracle Driver has its own
> PreparedStatements cache, having this double caching causes too many
> cursors to stay open.
> The Oracle9i Platform should not make use of the local PreparedStatements
> cache.
> Furhtermore the 'Oracle9i' offers better performance and correct handling
> for BLOBs > 4kB, for 9i or later Oracle versions.
>
> Good luck
> cheers
> Danilo
>
> Bruno CROS wrote:
> > Hi all,
> >
> > Experiencing some MAX OPEN CURSOR oracle errors (ORA -01000) , i'm
> looking
> > for the cursors leaks ( unclosed ResultSet it seems) in code.
> >
> > I guess that broker queries well close the result set. So when report
> > queries results iteration are closed ? on HasNext()==false ?
> >
> > Is it possible to check open cursors when releasing connections ? and by
>
> > the
> > way, close them if needed ?
> >
> > Note : MAX_OPEN_CURSOR Oracle parameter is actually set at 500 by
> > connection
> > ( and 1000 sooner )
> >
> > Oracle 10, OJB 1.0.4 with some litle updates
> >
> > Regards.
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
Re: Monitoring DB cursor leaks
Posted by Bruno CROS <br...@gmail.com>.
Hi,
Here is my connector, Oracle9i yet...
May be i have to set the jdbc-level at 3.0 ?
Referring to this article, it seems that it is a hard to resolve trouble.
http://www.orafaq.com/node/758
Except looking into code, how to check "real" cursors leak (if they are)?
Regards
<jdbc-connection-descriptor
jcd-alias="default"
default-connection="true"
platform="Oracle9i"
jdbc-level="2.0"
driver="oracle.jdbc.driver.OracleDriver"
protocol="jdbc"
subprotocol="oracle"
dbalias="thin:@XXXX:1521:ZZZZZ"
username="uuuuuuuuu"
password="pppppppp"
batch-mode="false"
useAutoCommit="1"
ignoreAutoCommitExceptions="false"
>
---------- Forwarded message ----------
From: Danilo Tommasina <dt...@risksys.com>
Date: Oct 9, 2006 6:08 PM
Subject: Re: Monitoring DB cursor leaks
To: OJB Users List <oj...@db.apache.org>
Hi,
we were having that problem too, long time ago. You should use the
'Oracle9i' Platform setting in your jdbc-connection-descriptor instead of
the Oracle
'Platform', that should fix the problem. IIRC the 'Oracle' Platform does a
caching of PreparedStatements, however the Oracle Driver has its own
PreparedStatements cache, having this double caching causes too many cursors
to stay open.
The Oracle9i Platform should not make use of the local PreparedStatements
cache.
Furhtermore the 'Oracle9i' offers better performance and correct handling
for BLOBs > 4kB, for 9i or later Oracle versions.
Good luck
cheers
Danilo
Bruno CROS wrote:
> Hi all,
>
> Experiencing some MAX OPEN CURSOR oracle errors (ORA -01000) , i'm
looking
> for the cursors leaks ( unclosed ResultSet it seems) in code.
>
> I guess that broker queries well close the result set. So when report
> queries results iteration are closed ? on HasNext()==false ?
>
> Is it possible to check open cursors when releasing connections ? and by
> the
> way, close them if needed ?
>
> Note : MAX_OPEN_CURSOR Oracle parameter is actually set at 500 by
> connection
> ( and 1000 sooner )
>
> Oracle 10, OJB 1.0.4 with some litle updates
>
> Regards.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Monitoring DB cursor leaks
Posted by Danilo Tommasina <dt...@risksys.com>.
Hi,
we were having that problem too, long time ago. You should use the 'Oracle9i' Platform setting in your jdbc-connection-descriptor instead of the Oracle
'Platform', that should fix the problem. IIRC the 'Oracle' Platform does a caching of PreparedStatements, however the Oracle Driver has its own
PreparedStatements cache, having this double caching causes too many cursors to stay open.
The Oracle9i Platform should not make use of the local PreparedStatements cache.
Furhtermore the 'Oracle9i' offers better performance and correct handling for BLOBs > 4kB, for 9i or later Oracle versions.
Good luck
cheers
Danilo
Bruno CROS wrote:
> Hi all,
>
> Experiencing some MAX OPEN CURSOR oracle errors (ORA -01000) , i'm looking
> for the cursors leaks ( unclosed ResultSet it seems) in code.
>
> I guess that broker queries well close the result set. So when report
> queries results iteration are closed ? on HasNext()==false ?
>
> Is it possible to check open cursors when releasing connections ? and by
> the
> way, close them if needed ?
>
> Note : MAX_OPEN_CURSOR Oracle parameter is actually set at 500 by
> connection
> ( and 1000 sooner )
>
> Oracle 10, OJB 1.0.4 with some litle updates
>
> Regards.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org