Re: Oracle - solving performance problem

Mark wrote:

>One is a mass load
>of data each day,

Please provide more specifics on how this is done, there may be some easy fixes such as using a hint or changing commands.

>the other a more critical one is a client query that
>takes about 2 minutes. We want to bring this value down to something
>like 5-10 seconds, if thats not possible at least 30-45 seconds or so.
>We know our bottle necks are I/O in both reads and writes.

While tracing and statspack are the proper way to go, we might be able to provide some help with an Explain Plan. A proper tuning analyst may be the best investment you make. Also, if you are doing full table or index scans (even other queries than the problem ones), you might need to put those objects into the recycle pool. I saw one complicated OLTP system completely change its behavior during month-end bottleneck processing just by placing one key table (and it's indices) into the recycle pool.

Listen to Sybrand.

Are you using parallel queries? It is possible putting those together might take longer than not using parallel.

By the way, separating indices and data does not necessarily lead to better performance, in most cases it has been proved to be a myth, google this group for myths. Oracle gets the index first, then gets the data, and it all usually becomes irrelevant with multiuser access - you wind up wanting to place both across as many spindles as possible. Google for SAME (Stripe And Mirror Everything) and BAARF (I'm serious).

jg

--
@home.com is bogus.
http://www.physics.uq.edu.au/physics_museum/pitchdrop.shtml