time of serialization in Oracle’s dbms_pickler package

Hi

Please help and guide to resolve the below performance issue coming in one of our projects.

We are facing performance problem in our project. Details are below -

Implementation Detail

We are using Oracle object as a parameter to Stored Procedure. The Oracle object is very complex, there are many hierarchies inside Oracle object. This procedure we are calling from JAVA using JDBC and JPublisher generated java classes. For passing this JPub generated object to the procedure via JDBC, we are using OracleConnection and OracleCallableStatement.setOraData method. Application is deployed in weblogic and the JDBC connection is taken using weblogic connection pool.

Environment

1. Java 6
2. Weblogic 11g
3. Oracle 10g
4. Ojdbc6 (11.0)

Problem Description

The very first call to the stored procedure is taking 40 sec while subsequent calls takes 4-5 sec to execute. We need to reduce this delay to the first call. We have observed that the delay takes only in case of the first initialization of Connection from connection pool. Ex. If we have 5 connections in the connection pool, this delay will occur 5 times at any execution cycle, whenever the call needs new connection from pool.

Findings

We have observed that this delay happening at the time of serialization in Oracle’s dbms_pickler package.

Option tried

1. We have tried with different JDBC version, ojdbc14 and ojdbc6.
2. Replce OracleCallable.setOraData method with OracleCallable.setOracleObject method.
3. Creating JDBC connection directly instead of taking it from pool.
4. Replacing OracleConnection with java.sql.Connection.
5. Pre-Initializing connection.

Thanks
Sunil