Below I present pool configuration. By the way we use our id setting policy for Hibernate object under Oracle. We decrase time of insert from 30h to 2min, only using our id policy (listing below pool configuration)

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration
PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>
<session-factory>

<!-- configuration pool via c3p0-->
<property name="c3p0.maxPoolSize">1</property>
<property name="c3p0.minPoolSize">1</property>

<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="c3p0.max_size">1</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.timeout">100</property> <!-- seconds -->

<!-- Mapping files -->
<mapping resource="hbm/Processing.hbm.xml"/>
<mapping resource="hbm/Currency.hbm.xml"/>
<mapping resource="hbm/CustomerCode.hbm.xml"/>
<mapping resource="hbm/FeeType.hbm.xml"/>
<mapping resource="hbm/Fee.hbm.xml"/>
<mapping resource="hbm/Product.hbm.xml"/>
<mapping resource="hbm/Contract.hbm.xml"/>
<mapping resource="hbm/Instalment.hbm.xml"/>
<mapping resource="hbm/RepaymentSchedule.hbm.xml"/>
<mapping resource="hbm/Correction.hbm.xml"/>
<mapping resource="hbm/Posting.hbm.xml"/>
<mapping resource="hbm/ExchangeRate.hbm.xml"/>
<mapping resource="hbm/HFee.hbm.xml"/>
<mapping resource="hbm/HRepaymentSchedule.hbm.xml"/>
<mapping resource="hbm/HInstalment.hbm.xml"/>
<mapping resource="hbm/HContract.hbm.xml"/>
<mapping resource="hbm/ClassificationCode.hbm.xml"/>
</session-factory>
</hibernate-configuration>

OUR ID GENERATOR
==============================================
/** save current value of Id, starting value is startingId+NO_OF_ID-1 */
private long currentDbId_ = 0;
/** starting value selected from sequence */
private long endDbId_ = 0;

// DO NOT CHANGE THIS VALUE THIS VALUE MUST
// BE EQUAL WHAT IS IN cursorInit.sql !!!!!
private static final long NO_OF_ID = 10000;

/**
* Find uniqe Id using Oracle sequence
* @return unique id
* @throws DaoException thrown if DB error happens
*/
public long getUniqueId() throws DaoException {

// check if we have still free id's
if (currentDbId_ != endDbId_) {
currentDbId_++;
return currentDbId_;
}

// find next set of Id's
Connection connection_ = null;
try {
Session session_ = SessionTxManager.getSession();
if ( session_== null ) {
LOG.error("SessionTxManager.getSession() zwrocil null");
throw new DaoException("GetSession zwrocil null");
}
connection_ = session_.connection();
if ( connection_ == null ) {
LOG.error("session_.connection() zwrocil null");
throw new DaoException("session_.connection() zwrocil null");
}
} catch (HibernateException e) {
LOG.error("Blad w pozyskaniu Connection " + e.getMessage());
throw new DaoException("Blad w pozykaniu connection");
}

// finding free id in DB (sequence must exist in DB!!!)
String query = "select finca_sequence.nextval from dual";
ResultSet result = null;
try {
Statement stmt = connection_.createStatement();

result = stmt.executeQuery(query);
if (!result.next()) {
LOG.error("ExecuteQuery nie zwrocilo indeksu");
throw new DaoException("ExecuteQuery nie zwrocilo indeksu");
}
// get first Id
endDbId_ = result.getLong(1) + NO_OF_ID -1 ;
currentDbId_ = result.getLong(1);
connection_.commit();
stmt.close();

return currentDbId_;
} catch (SQLException e1) {
LOG.error("Blad w czytaniu Id z bazy - " + e1.getMessage());
throw new DaoException("BLad w czytaniu Id z bazy");
}
}

This part of logs also display setting of pool connection:

net.sf.hibernate.connection.C3P0ConnectionProvider - C3P0 using driver: oracle.jdbc.driver.OracleDriver at URL: jdbc:oracle:thin:@gdndev102.lido-tech:1521:finca
INFO 21.10.2004 17:04:31 net.sf.hibernate.connection.C3P0ConnectionProvider - Connection properties: {user=szymic1, password=szymic1}
INFO 21.10.2004 17:04:31 net.sf.hibernate.transaction.TransactionManagerLookupFactory - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
INFO 21.10.2004 17:04:32 net.sf.hibernate.cfg.SettingsFactory - Use scrollable result sets: true
INFO 21.10.2004 17:04:32 net.sf.hibernate.cfg.SettingsFactory - Use JDBC3 getGeneratedKeys(): false
INFO 21.10.2004 17:04:32 net.sf.hibernate.cfg.SettingsFactory - Optimize cache for minimal puts: false
INFO 21.10.2004 17:04:32 net.sf.hibernate.cfg.SettingsFactory - Query language substitutions: {}
INFO 21.10.2004 17:04:32 net.sf.hibernate.cfg.SettingsFactory - cache provider: net.sf.hibernate.cache.EhCacheProvider
INFO 21.10.2004 17:04:32 net.sf.hibernate.cfg.Configuration - instantiating and configuring caches

<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="c3p0.max_size">1</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.timeout">100</property> <!-- seconds -->

to
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">1800</property> <!-- seconds -->
<property name="c3p0.max_size">1</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.timeout">3600</property> <!-- seconds -->

I've changed it but still in Oracle tracle I see many:

PARSING IN CURSOR #2 len=43 dep=0 uid=21 oct=3 lid=21 tim=1072630711581466 hv=2476267719 ad='4cc310'
select hibernate_sequence.nextval from dual
END OF STMT
PARSE #2:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072630711581459
EXEC #2:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072630711581629
FETCH #2:c=0,e=89,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1072630711583201
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SEQUENCE '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '


In our internal solution we select nextval only one for 10000 not for each. I think it is good idea to change a "nativ" id generator for Oracle to achive hyper speed of inserting.

alse, you property file doesn't set the connection provider....

Our current property file is:

# hibernate properties used for dev test for Oracle
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.connection.url=jdbc:oracle:thin:@gdn:1521:finca
hibernate.connection.username=xxx
hibernate.connection.password=xxx
hibernate.dialect=net.sf.hibernate.dialect.Oracle9Dialect
hibernate.show_sql=false
hibernate.jdbc.batch_size=10000
hibernate.statement_cache.size=0

What should I change ?

I've added folllowing line:

hibernate.connection.provider_class=net.sf.hibernate.connection.C3P0ConnectionProvider

But still hibernate sends many:
select hibernate_sequence.nextval from dual