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

On Oct 9, 10:45 am, jacksu <jacks..._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.
>
> Thank you in advance.

You probably need to trace. See the performance tuning guide for your version of Oracle. A number of posts on this group have shown how to do that, metalink probably has a how-to if you have access, and there are plenty of references on the net.

You might also get a clue from an explain plan for the sql, if the problem isn't endemic to your way of accessing the db. It is possible you are doing something considered very bad, like using literals instead of bind variables, causing lots of unnecessary hard parsing and context switching, combined with a small table that the optimizer considers worth full-scanning, combined with recursion of your attributes, setting off latching problems... that would be what I mean by endemic. That's the sort of thing that causes DBA's to rail against OO.

If you have some way of looking at the cursors open in the db, that may also give a clue. Details vary by tools, db options and version.

Here's a readme for posting in this group: http://www.dbaoracle.net/readme-cdos.htm

jg

--
@home.com is bogus.
http://www.xkcd.com/195/