Re: Strange Performance Problem

Steve,

Thanks for this info. I turned on the tracing as you suggested. The trace file was produced ok, but the problem stops happening when the tracing is on! I'll keep look into it.

Regards,
Neil

"Steve" <sroesch3_at_attbi.com> wrote in message news:efc3f20b.0206151737.49008a91_at_posting.google.com... > "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