Performance issue in multi-level Oracle Object/thin JDBC

From: jacksu <jacksuyu_at_gmail.com>
Date: Tue, 09 Oct 2007 10:45:00 -0700
Message-ID: <1191951900.636326.111610@50g2000hsm.googlegroups.com>

Hi All,

We are trying to call Oracle 9i Stored Procedure with ThinJDBC, the parameter in Stored procedure is multilevel object:

Table of Object1

     +------- Object1
                   +------ Table of Object2
                                     +------ Object2
                                                  +----- attribute in

simple oracle types, such as varchar.

The query for that call is very light way, just get two records from a hundred records table. But the whole execution time is around 600ms to 1000ms.

If we change to one level Oracle Object

+---- Object

            +------- attributes

with the same query data, the execution time is around 100 ms.

I am wondering where to start to troubleshoot the performance problem, 500 ms is not acceptable in the production environment.

Thank you in advance. Received on Tue Oct 09 2007 - 12:45:00 CDT