Re: Monitoring DB cursor leaks
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:@:1521:Z username=u password= batch-mode=false useAutoCommit=1 ignoreAutoCommitExceptions=false -- Forwarded message -- From: Danilo Tommasina [EMAIL PROTECTED] Date: Oct 9, 2006 6:08 PM Subject: Re: Monitoring DB cursor leaks To: OJB Users List ojb-user@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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Monitoring DB cursor leaks
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 [EMAIL PROTECTED] 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:@:1521:Z username=u password= batch-mode=false useAutoCommit=1 ignoreAutoCommitExceptions=false -- Forwarded message -- From: Danilo Tommasina [EMAIL PROTECTED] Date: Oct 9, 2006 6:08 PM Subject: Re: Monitoring DB cursor leaks To: OJB Users List ojb-user@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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Monitoring DB cursor leaks
Hi,
You could write your own ConnectionFactory
http://db.apache.org/ojb/docu/guides/connection.html
And execute the appropriate statement in the lookup method.
E.g.
public class MyConnectionManager extends ConnectionFactoryDBCPImpl {
public Connection lookupConnection(JdbcConnectionDescriptor jcd)
throws LookupException {
Connection con = super.lookupConnection(jcd);
if(con != null){
/* Execute the ALTER stataement here */
}
};
}
This would be executed every time a new connection was requested for the
Broker; this is probably overkill, but it is simple.
The more correct way of doing it would be to execute the SQL statement
when the connection is actually *created* - but where that is, depends
on which connection factory you are using.
Hope that's a pointer in the right direction for you...
Cheers,
Charles.
-Original Message-
From: Bruno CROS [mailto:[EMAIL PROTECTED]
Sent: 10 October 2006 09:34
To: OJB Users List
Subject: Re: Monitoring DB cursor leaks
http://oracleandy.blogspot.com/2006/03/vopencursor-find-cursor
s-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 [EMAIL PROTECTED] 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:@:1521:Z
username=u
password=
batch-mode=false
useAutoCommit=1
ignoreAutoCommitExceptions=false
-- Forwarded message --
From: Danilo Tommasina [EMAIL PROTECTED]
Date: Oct 9, 2006 6:08 PM
Subject: Re: Monitoring DB cursor leaks
To: OJB Users List ojb-user@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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Cassio Dos Santos is out of the office
I will be out of the office starting 10/10/2006 and will not return until 10/23/2006. Please contact Kevin Shank or Amy Wong for any emergency. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Specific sequence implementation for a table
Hi Ozkan, zkn wrote: Hi, I use the SequenceManagerMySQLImpl for my application. But for only one table/object I want to use the SequenceManagerInMemoryImpl. Is that possible? Currently this is not supported by default. You have to implement your own SequenceManager to handle this (e.g. a SM which handle a SequenceManagerMySQLImpl and a SequenceManagerInMemoryImpl instance and decide on a custom attribute flag defined in field-descriptor or class-descriptor which one to use). More details about custom attributes can be found here http://db.apache.org/ojb/docu/guides/repository.html#custom+attribute I'm currently locally testing per field sequence manager support and shortcut names for the supported sequence manager implementations. Will check in the new features tomorrow (if the test-suite pass without failures). Then it will be possible to set field-descriptor name=id column=ID jdbc-type=INTEGER primarykey=true autoincrement=true sequence-manager className=memory/ /field-descriptor for each autoincrement field and if not set, the sequence manager defined in jdbc-connection-descriptor will be used. regards, Armin Ozkan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Problems generating sql with the SqlGenerator
Hi Josef,
could you clarify your problem: The generated sql is only invalid when
you add your extension or is the generated sql invalid in general?
regards,
Armin
Josef Wagner wrote:
Hi List,
i have written my one SqlGenerator, because i have to extend each query
width the criteria, that the status of the entry, which is described in
a seperate log-table is not 'DELETED'.
--
Problem
--
Now, my problem is, that the generated statemts are incorecct.
I extend from the SqlGeneratorDefaultImpl and override the Method
getPreparedSelectStatement as followed:
public String getPreparedSelectStatement(Query query, ClassDescriptor
cld) {
try {
if ((cld.getClassOfObject().newInstance() instanceof
LogableAbstractPoIfc)) {
query.getCriteria().addEqualTo(log.log_table,
OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
query.getCriteria().addNotEqualTo(log.log_status, D);
}
} catch(Exception iaex) {
logger.warn(Statement wurde evtl. nicht korrekt
aufbereitet); logger.warn(iaex); }
SqlCacheKey key = new SqlCacheKey(query, cld,
SqlCacheKey.TYPE_SELECT);
String result = (String) m_sqlCacheMap.get(key);
if (result == null) {
SqlStatement sql = new SqlSelectStatement(getPlatform(), cld,
query, logger);
result = sql.getStatement();
if (logger.isDebugEnabled()) {
logger.debug(SQL: + result);
}
m_sqlCacheMap.put(key, result);
}
return result;
}
The following incorrect statment will be generated, wenn calling
new User(1).getGroups().size();
-
Incorrect generated sql-statement
-
SELECT count(*) FROM groups A0,users_groups INNER JOIN log
A1,users_groups ON A0.obj_id=A1.log_table_obj_id WHERE
(((users_groups.users_obj_id = 1) AND users_groups.groups_obj_id =
A0.obj_id) AND log_table = 'GrOUPS') AND log_funktion 'D'
statement i would expect
SELECT count(*) FROM groups A0 INNER JOIN log A1 ON
A0.obj_id=A1.log_table_obj_id INNER JOIN users_groups ON A0.obj_id=
users_groups.groups_obj_id WHERE (((users_groups.users_obj_id = 1) AND
users_groups.groups_obj_id = A0.obj_id) AND log_table = 'GROUPS') AND
log_funktion 'D'
---
Description to the tables
---
### ##
# groups ## groups_users # # users #
### ##
# int obj_id ## int groups_obj_id # # int obj_id #
### int users_obj_id # ##
#
# log #
#
# int obj_id#
# int log_table_obj_id #
# string log_table #
# string log_status #
#
- table groups and table users are joined width the m-n-table groups_users
- for each data-entry, there is a entry in the log-table, which
describes the status of the data-entry (deleted, inserted...) the
repository.xml of groups and users have an collection-descrpitor to the
log-table with a query-optimizer vor verifing the source-table with
string log_table
!-- 1-n to Log --
collection-descriptor
auto-retrieve=true
auto-delete=object
auto-update=object
proxy=true
name=log
element-class-ref=de.on_ergy.lakon.data.model.Log
inverse-foreignkey field-ref=logTableObjId/
query-customizer
class=de.on_ergy.lakon.data.QueryCustomizerLakonImpl
attribute
attribute-name=logTable
attribute-value=GROUPS
/
/query-customizer
/collection-descriptor
the query-customizer sets an and-criteria width log_table equals for
example GROUPS
--
groups_repostory.xml, users_repository is equal, only width other
parameters
-
class-descriptor class=de.on_ergy.lakon.data.model.Groups
table=groups
field-descriptor name=objId column=obj_id
jdbc-type=INTEGERprimarykey=true
autoincrement=true/field-descriptor
!-- m - n users_groups to users --
collection-descriptor
name=users
collection-class=org.apache.ojb.broker.util.collections.ManageableArrayList
element-class-ref=de.on_ergy.lakon.data.model.Users
auto-retrieve=true
auto-update=false
auto-delete=link
proxy=true
indirection-table=users_groups
fk-pointing-to-this-class column=groups_obj_id/
fk-pointing-to-element-class column=users_obj_id/
/collection-descriptor !-- 1-n to log --
collection-descriptor
auto-retrieve=true
auto-delete=object
auto-update=object
proxy=true
name=log
Re: Problems generating sql with the SqlGenerator
Hi Armin,
yes, I have the problem only when adding my extension. Generally, all
works fine.
We have tried a other solution like this:
-
public String getPreparedSelectStatement(Query query, ClassDescriptor
cld) {
try {
if ((cld.getClassOfObject().newInstance() instanceof
LogableAbstractPoIfc)) {
logQuery.setCriteria(new Criteria());
logQuery.getCriteria().addEqualTo( log_table,
OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
logQuery.getCriteria().addEqualTo(log_funktion, D);
logQuery.getCriteria().addEqualToColumn(log_table_obj_id,
A0.obj_id);
query.getCriteria().addNotExists(logQuery);
-
Except of one Query, all works fine. In this one query, ojb works with
two outer joins instead of in join and i can not set in join as
preffered like query.setPathOutterJoin() :o/
In my opinion, the old solution in my generator must also work, any idea??
Thanks Armin for time you spend
Josef
Armin Waibel schrieb:
Hi Josef,
could you clarify your problem: The generated sql is only invalid when
you add your extension or is the generated sql invalid in general?
regards,
Armin
Josef Wagner wrote:
Hi List,
i have written my one SqlGenerator, because i have to extend each
query width the criteria, that the status of the entry, which is
described in a seperate log-table is not 'DELETED'.
--
Problem
--
Now, my problem is, that the generated statemts are incorecct.
I extend from the SqlGeneratorDefaultImpl and override the Method
getPreparedSelectStatement as followed:
public String getPreparedSelectStatement(Query query,
ClassDescriptor cld) {
try {
if ((cld.getClassOfObject().newInstance() instanceof
LogableAbstractPoIfc)) {
query.getCriteria().addEqualTo(log.log_table,
OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
query.getCriteria().addNotEqualTo(log.log_status, D);
}
} catch(Exception iaex) {
logger.warn(Statement wurde evtl. nicht korrekt
aufbereitet);
logger.warn(iaex); }
SqlCacheKey key = new SqlCacheKey(query, cld,
SqlCacheKey.TYPE_SELECT);
String result = (String) m_sqlCacheMap.get(key);
if (result == null) {
SqlStatement sql = new SqlSelectStatement(getPlatform(),
cld, query, logger);
result = sql.getStatement();
if (logger.isDebugEnabled()) {
logger.debug(SQL: + result);
}
m_sqlCacheMap.put(key, result);
}
return result;
}
The following incorrect statment will be generated, wenn calling
new User(1).getGroups().size();
-
Incorrect generated sql-statement
-
SELECT count(*) FROM groups A0,users_groups INNER JOIN log
A1,users_groups ON A0.obj_id=A1.log_table_obj_id WHERE
(((users_groups.users_obj_id = 1) AND users_groups.groups_obj_id =
A0.obj_id) AND log_table = 'GrOUPS') AND log_funktion 'D'
statement i would expect
SELECT count(*) FROM groups A0 INNER JOIN log A1 ON
A0.obj_id=A1.log_table_obj_id INNER JOIN users_groups ON A0.obj_id=
users_groups.groups_obj_id WHERE (((users_groups.users_obj_id = 1)
AND users_groups.groups_obj_id = A0.obj_id) AND log_table = 'GROUPS')
AND log_funktion 'D'
---
Description to the tables
---
### ##
# groups ## groups_users # # users #
### ##
# int obj_id ## int groups_obj_id # # int obj_id #
### int users_obj_id # ##
#
# log #
#
# int obj_id#
# int log_table_obj_id #
# string log_table #
# string log_status #
#
- table groups and table users are joined width the m-n-table
groups_users
- for each data-entry, there is a entry in the log-table, which
describes the status of the data-entry (deleted, inserted...) the
repository.xml of groups and users have an collection-descrpitor to
the log-table with a query-optimizer vor verifing the source-table
with string log_table
!-- 1-n to Log --
collection-descriptor
auto-retrieve=true
auto-delete=object
auto-update=object
proxy=true
name=log
element-class-ref=de.on_ergy.lakon.data.model.Log
inverse-foreignkey field-ref=logTableObjId/
query-customizer
class=de.on_ergy.lakon.data.QueryCustomizerLakonImpl
attribute
attribute-name=logTable
attribute-value=GROUPS
/
/query-customizer
