Hello friends -

Hibernate 3.2./C3P0 .9/Oracle 10g

OK. I have a low frequency, high volume operation; i.e., no data, no data, no data, several thousand rows of data. This has worked fine (at least within a context of c3po/Hibernate/Oracle connects) in the past when I got up to 2K records in one alert (i.e., per minute). Essentially I get an array of N values, then for each value, must make two lookups from a different database.

Today I got 7K in one swipe and I started seeing ORA-01000 errors after some work got done. Here's my .cfg.xml file:

<session-factory>
<property name="connection.url">jdbc:oracle:thin:@whatever:1521:sid</property>
<property name="connection.username">user</property>
<property name="connection.password">pw</property>
<property name="dialect">org.hibernate.dialect.Oracle9iDialect</property>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>



<!-- CONNECTION POOLING PROPERTIES -->
<!-- configuration pool via c3p0-->
<property name="hibernate.c3p0.acquire_increment">5</property>
<property name="hibernate.c3p0.idle_test_period">300</property> <!-- seconds -->
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.max_statements">25</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">50</property>
<property name="hibernate.c3p0.timeout">300</property> <!-- seconds -->

<mapping resource="LookupRecord.hbm.xml" />

</session-factory>

For each record, I perform two lookups each with a query I create; i.e.,

At a high level, my psuedocode looks like this.

getSession();
beginTransaction();

Loop through array. For each

Query query1 = getSession().createQuery(sql.toString());
do work;

Query query2 = getSession().createQuery(sql2.toString());
do work;

tx.commit();
session.close();

The internet tells me that I am not appropriately closing my statements / resultsets, that eventually lands me in ORA-01000 land, but I don't really (seem to) have access to those objects. Can I force GC on my Query objects to free things up? What am I doing wrong?

This process works fine in situations if I get 2K records to process one minute and 2K records three minutes later.

Any insight is <i>greatly</i> appreciated.

brian

Are you creating Query in the loop? If yes then this might be the cause. A new statement is created for each Query that you create, it seems like Hibernate is not closing statement when Query object is no longer used by your code.

Ideally you should not be creating Query in a loop as it is expensive, the same Query object should be used & you should just be setting params for the object in loop, this way a single Statement object will be used.

Hi Rishi Shehrawat -

Thanks for your response. Sorry for not respondning more quickly, long weekend and I turned off the work portion of my brain. Unfortunately, moving my query outside of the loop and performing the parameter replacement within loop had no effect.

If the bump gets any new eyeballs on this thread, I have also tried the following:

1) Removing '<property name="hibernate.c3p0.max_statements">25</property>' from the Hibernate.cfg.xml file. Running this several times did seem to give me the ability to get further through a large result set (i.e., instead of bailing out with cursors at 3500 records, it might get up to 9000 records before complaining about cursors). This change was made as a result of a FAQ entry regarding Oracle and cursor error messages.

2) Kicking the max_statements value up to 250, or 500. This had the effect of making my response time before cursor failure much improved; i.e,. my debug statements indicating I was at 5000 records processed came much faster than with a 25 value for max_statements. Eventually, however, I would eventually hit a cursor problem.

I'm struggling here because this has to be a solution that has been solved a bazillion times by other people. What's the trick?

Thanks

brian

Hello friends -

Just in case someone out there finds themselves with this problem, I'm throwing out a possible solution for Karma sake. In my instance, I had something that looked like this:

***************************************
Iterator it = theQuery.iterate();

while (it.hasNext()){
Object = it.next();
doSomeWork. . .
if (conditionIsMet()){
break;
}

}
***************************************

This is where the application was creating cursors that never got released! It was as if there was a problem between the authentication between Oracle and Hibernate; i.e., Oracle thought that Hibernate had access to everything necessary to release the prepared statement, but Hibernate didn't actually have what it needed.

So, instead, if you try something like this:

***************************************
boolean keepWorking = true;
Iterator it = theQuery.iterate();

while (it.hasNext()){
Object = it.next();

if (keepWorking){
doSomeWork;

if (conditionIsMet()){
keepWorking=false;
}
}

}
***************************************

You will be golden. You can utilize Prepared Statement caching with C3P0 and Oracle.

BAM!

brian