Re: Strange Performance Problem

"Neil McCrossin" <nmcg_at_gil.dot.com.dot.au> wrote in message news:<ZplO8.336$cJ2.9966_at_ozemail.com.au>...
> Just to clarify further:
>
> If I run a particular app multiple times, it may start up quite quickly one
> time (i.e. all queries run quickly), the next time all queries run quickly
> except query X which takes just over 30 seconds instead of its usual 0.1
> seconds, the next time all queries run quickly except query Y which takes
> just over 30 seconds instead of its usual 0.2 seconds, the next time all
> queries run quickly, and so on in a seemingly random fashion.
>
> "Neil McCrossin" <nmcg_at_gil.dot.com.dot.au> wrote in message
> news:f6lO8.335$cJ2.9909_at_ozemail.com.au...
> > We recently got a new database server and ever since we have been getting
> a
> > strange performance problem. The apps that we run load up data from quite
> a
> > few different tables when they start up, and some of these queries are
> > taking much longer than before, making the overall start-up time for the
> > apps highly variable and often much longer than in the past. Most of the
> > queries, however, are performing quite well, and it seems like there is a
> > set of about 6 queries that play up. If an app takes a long time to load
> up
> > it will turn out that one or more of these particular 6 queries took a lot
> > longer than expected. Most of the time the offending query takes just over
> > 30 seconds or just over 60 seconds to execute (as opposed to an expected
> > time which may only be a fraction of a second), which makes me think that
> it
> > is waiting for something which times out after 30 or 60 seconds.
> >
> > Some other facts that may or may not be relevant:
> > 1. The apps execute the queries using ODBC.
> > 2. The server is currently running Oracle 9 and the client machines are
> > running Oracle 8.1.7 (the client machines will be changed over to Oracle 9
> > soon). However, we have been told there should be no problem with this
> > setup.
> >
> > Any help would be greatly appreciated - we would really like to know where
> > to even start looking for the cause of this problem, or if there are any
> > diagnostic tools available that may help.
> >
> >

Hi Neil,

to figure what your problem is it is necessary to know on which wait events we are waiting. Before running your queries, run the following statement in the same database session to trace the wait events of this session:
alter session set events '10046 trace name context forever, level 8'; This will create a trace file and tell you on what events you are waiting.

Regards
Steve Received on Sat Jun 15 2002 - 20:37:39 CDT