On Oct 9, 2:38 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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/- Hide quoted text -
>
> - Show quoted text -
Thanks. I might post the article in the wrong forum, because we just
found out that the stored proc execution time is under 10 ms. I will
repost to java forum.
Received on Tue Oct 09 2007 - 14:51:36 CDT
