Hi,

ConnectionReleaseMode.AFTER_STATEMENT is the default strategy for JTASessionContext. Doesn't this force the recreation of prepared statements again and again and also prevents the batching?

Indeed, after each statement is completed, the session is released, forcing the closure of each prepared statement.

This is shown in Oracle reports by having as many parse as we have statements execution (exemple: Loader.doQuery opens the connection, prepare the statement, executes it and finally close it and release the connection). This means that each execution will force a new prepared statement.

A side effect of this is that the batching is also not working as expected (we do a lot of insert using hibernate batching, but we see in Oracle as many (soft) parse as we execute inserts)

We are in a JTA context, so I wouldn't have any problem to keep the connection and the prepared statements open for the whole JTA transaction.

Any suggestion on the way to do that?

Thanks!

Thierry

I believe that ConnectionReleaseMode.AFTER_STATEMENT is meant only for non-transactional data access,
so batch inserts or batch updartes should not be break up due this ReleaseMode.
Anyway I will check it ...

What I see on Oracle is that I have as many parse as execute. I tried with ConnectionReleaseMode.AFTER_TRANSACTION and it did not changed anything.

Most probably I was wrong with this presumption, anyway
which session pool implementation are you using ?
Various session pool implementations have a optional configuration for caching prepared statements.
Check if your pool implementation has such facility.

Another thing to consider is when you perform inserts more than one table.
Then it is not enough to configure a hibernate.jdbc.batch_size >0.
In such case you must configure also

We are using WebSphere Process Server 6, so we are using the IBM connection pool implementation.

I will give a try to order insert = true.

Maybe I can contact you straight to explain the issue and provide you some extra info I cannot post here

Unfortunately I have no experience neither with WebSphere nor with IBM connection pool implementation.
All I can say is, that I have a Hibernate environment where I use JTA integration with BitronixTm
and there was necessary to set PreparedStatementCacheSize to a value > 0 on the bitronix pool configuration,
in order to avoid parsing round-trips.
(see http://jira.codehaus.org/browse/BTM-66 comments of Brett Wooldridge, for more infos)

Other 2 things you have to consider regarding batch update/insert:
1. not all jdbc-drivers do support batching
please verify it on your driver with


2. be aware that batch insert is not applicable when using native id generator

Hi,

I found out the root cause of this behavior. It is caused by an Oracle bug on JDBC. I reported it and Oracle is working on a resolution for the moment.

High level as soon as there is a column of type XMLType, the prepared statement does, for each addToBatch, one parsing and executes the single query immediately.
So bottom line, on top of loosing the batching, there is a lot of overhead by the parsing of the query for each row inserted/updated. I suspect other types like CLOB/BLOB and so on also trigger that bug.

both JDBC 11.1.0.7 and 12.1.0.1 are affected.

See Oracle Bug 9394224: INSERTING A XMLTYPE FIELD PREVENT BATCHING AND FORCES SOFT PARSE (may not be visible yet)

Best regards,

Thierry

Hi,

We have received the patch from Oracle and the issue is fixed. If you face this issue, I recommend you ask for this patch!

The patch is available in 12.1 (yes this is the trunk of JDBC development) and backported already on 12.1.0.1 and 11.1.0.7.

Best regards,

Thierry