Re: Strange Performance Problem

Are you using bind variables? If not do so. What is the explain plan for the offending queries? What is the tkprof for those queries?
Is the sort_area_size too low and you are getting sorts to disk and in the old system the sort_area_size was larger? Are the tables and indexes analyzed?
Are some of the init.ora parameters different from the old system and the new system?

Jim

"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.
>
>
Received on Fri Jun 14 2002 - 07:26:59 CDT