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

On Oct 9, 12:51 pm, jacksu <jacks..._at_gmail.com> wrote:
> 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

Maybe yes, maybe no. A good SP execution time means all that mumbojumbo  I posted previously might help you figure out where the real problem lies. Of course, it might be entirely java's fault (or more precisely, the java code), but you have a stack of interacting technology here. At some point Oracle is dealing with java, so the Oracle tools that tell you what Oracle thinks may still point you in the right direction. For example, the db has an init.ora file that includes configuration for java - you wouldn't want to blindly start dinking with that. If nothing else, you should post the output of show parameter java while in sqlplus. You might consider posting the code that shows how you return the data to java.

jg

--
@home.com is bogus.
"My golden rule of tuning is:  The less time spent tuning - the more
time available for talking about tuning in a local pub!" - Tanel Poder