I am running into a nasty insert problem:
the code "works" but during stress test, the inserts become slower and slower
from the console output:

-----------------------------------------------------------
Hibernate: select DIGITAL_GIFT_ID_SEQUENCE.nextval from dual
Hibernate: select DG_FULFILLMENT_ID_SEQ.nextval from dual

very long delay here...

Hibernate: insert into DIGITAL_GIFTS (PURCHASER_CUSTOMER_ID, RECIPIENT_CUSTOMER_ID, ORDER_ID, PRODUCT_ID, STATUS, PURCHASE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATED, DIGITAL_GIFT_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into DG_FULFILLMENTS (DIGITAL_GIFT_ID, STATUS, CLAIM_CODE, EMAIL_ADDRESS, RECIPIENT_CUSTOMER_ID, ISSUE_DATE, CLAIM_DATE, EXPIRATION_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATED, DG_FULFILLMENT_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------------------------------------------------------

there is a longer and longer delay between the 2 select from sequence and the actually inserts statements
the CPU usage also peaks.

once I restart the program, everything works fine(fast) again but very soon it become slow.

What does hibernate do between the 2 select statements and 2 inserts above?

Thanks in advance!

Hibernate version:3.0

Mapping documents:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@test123:1521:test</property>
<property name="connection.username">tester</property>
<property name="connection.password">12345</property>
<property name="dialect">org.hibernate.dialect.OracleDialect</property>

<property name="c3p0.min_size">2</property>
<property name="c3p0.max_size">5</property>
<property name="c3p0.max_statement">25</property>


<property name="show_sql">true</property>
<property name="transaction.factory_class">
org.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="cache.provider_class">
org.hibernate.cache.HashtableCacheProvider
</property>
<!--
<property name="hbm2ddl.auto">create</property>
-->

<mapping resource="db/Gift.hbm.xml"/>
<mapping resource="db/Fulfillment.hbm.xml"/>
<mapping resource="db/CurrentFulfillment.hbm.xml"/>

</session-factory>

</hibernate-configuration>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="db.Fulfillment" table="DG_FULFILLMENTS">
<id name="id" column="DG_FULFILLMENT_ID" type="long">
<!--
<generator class="increment"/>
-->
<generator class="sequence">
<param name="sequence">DG_FULFILLMENT_ID_SEQ</param>
</generator>
</id>
<property name="giftID" column = "DIGITAL_GIFT_ID" type="long" not-null="true"/>
<property name="status" column = "STATUS" type="java.lang.String" not-null="true"/>
<property name="claimCode" column = "CLAIM_CODE" type="java.lang.String" not-null="true"/>
<property name="recipientEMail" column = "EMAIL_ADDRESS" type="java.lang.String" not-null="true"/>
<property name="recipientID" column = "RECIPIENT_CUSTOMER_ID" type="long" />
<property name="issueDate" column = "ISSUE_DATE" type="timestamp" not-null="true"/>
<property name="claimDate" column = "CLAIM_DATE" type="timestamp" />
<property name="expirationDate" column = "EXPIRATION_DATE" type="timestamp" />
<property name="createdBy" column = "CREATED_BY" type="java.lang.String" not-null="false"/>
<property name="creationDate" column = "CREATION_DATE" type="timestamp" not-null="false"/>
<property name="lastUpdatedBy" column = "LAST_UPDATED_BY" type="java.lang.String" />
<property name="lastUpdateDate" column = "LAST_UPDATED" type="timestamp" />
</class>
</hibernate-mapping>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="db.Gift" table="DIGITAL_GIFTS">
<id name="id" column="DIGITAL_GIFT_ID" type="long" >
<!--
<generator class="native"/>
-->
<generator class="sequence">
<param name="sequence">DIGITAL_GIFT_ID_SEQUENCE</param>
</generator>
</id>
<property name="purchaserID" column = "PURCHASER_CUSTOMER_ID" type="long" not-null="true"/>
<property name="recipientID" column = "RECIPIENT_CUSTOMER_ID" type="long"/>
<property name="orderID" column = "ORDER_ID" type="long" not-null="true"/>
<property name="PRODUCT_ID" column = "PRODUCT_ID" type="java.lang.String" not-null="true"/>
<property name="status" column = "STATUS" type="java.lang.String" not-null="true"/>
<property name="purchaseDate" column = "PURCHASE_DATE" type="timestamp" not-null="true"/>
<property name="createdBy" column = "CREATED_BY" type="java.lang.String" not-null="false"/>
<property name="creationDate" column = "CREATION_DATE" type="timestamp" not-null="false"/>
<property name="lastUpdatedBy" column = "LAST_UPDATED_BY" type="java.lang.String"/>
<property name="lastUpdateDate" column = "LAST_UPDATED" type="timestamp"/>
</class>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():

Session session = HibernateUtil.currentSession();
Transaction tx = session.beginTransaction();
Date now = new Date();
//create a new gift record
db.Gift g = new db.Gift();
g.setPurchaserID(AmzUid.decryptCustomerID(getPurchaserID()));
g.setOrderID(getOrderID());
g.setPRODUCT_ID(getPRODUCT_ID());
g.setStatus(Const.GIFT_STATUS_ACTIVE);
g.setPurchaseDate(getPurchaseDate());

//let audit trigger handle these audit fields
//g.setCreatedBy(Const.DGS_NAME);
//g.setCreationDate(now);
session.save(g);

//create a new fulfillment record
db.Fulfillment f = new db.Fulfillment();
f.setGiftID(g.getId());
f.setStatus(Const.FULFILLMENT_STATUS_NEW);
f.setClaimCode(DigitalGiftingUtil.generateClaimCode());
f.setRecipientEMail(getRecipientEMail());
f.setIssueDate(now);
f.setExpirationDate(getExpirationDate());

//let audit trigger handle these audit fields
//f.setCreatedBy(Const.DGS_NAME);
//f.setCreationDate(now);
session.save(f);
tx.commit();




Full stack trace of any exception that occurs:

Name and version of the database you are using: Oracle 9i

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

You say stress test so am I safe to assume that you open a session and then end up pounding a bunch of data in using the same session?

If that's the case then it's your session object holding a large amount of data and you may have to clear() it periodically.

This fixed the problem, Thanks!

a question here, is there a configuration that I can set so hibernate will automatically free these data? I could call clear(), but since hibernate maintains all the data, it should have better idea when it's time to clear()?