Re: Performance issue in multi-level Oracle Object/thin JDBC

On Tue, 09 Oct 2007, jacksuyu_at_gmail.com wrote:
> 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.

I would first start by separating the pieces and benchmark them separately to get at the performance culprit. In java, right before you make the call log a timestamp and then, the first time you get something from the database, log a timestamp.

Go to sqlplus and create the multi-level object as an oracle type and pass that to the stored proc and compare the execution times of that to the call from the driver.

-- 
Galen Boyer