Re: query takes 10 times longer when running other oracle processes

From: Michael Vilain (vilain_at_spamcop.net)
Date: 08/19/04 

Date: Wed, 18 Aug 2004 23:22:56 -0700

In article <ca194270.0408181935.43105d98@posting.google.com>,
 sunsystem8@yahoo.com (Sun System) wrote:

> We are running Solaris 2.7 on E4500 machine. Lately we have been
> running a large queries on Oracle database (8i) and because of these
> queries other developers are experencing very slow response when they
> run very simply query.
>
> Our system has 6G Memory/6 CPUs 400MHz.
>
> Could some one please suggest a way for me to isolate to see if this
> is unix related issue or Oracle database design/implementation issue.
> I was thinking if I run command like (vmstat and some others) I may be
> able to see what is causing it.
>
> Any help or suggestion would be appreciated.

Years ago, an instructor in a system performance and capacity planning
course told the class that tuning an application will increase
performance in 90% of the cases. Unless a system is mistuned or grossly
misconfigured, he said that at best you could get a 10% performance
increase by tuning the system or mucking with hardware. This was before
the days of SMP where "buy a bigger horse" or "buy more memory" were
usually the biggest bang for the hardware buck.

Why not have your DBA look at the tables and columns being hit by this
large query? Or perhaps the query is poorly designed and is relating
non-indexed columns in a join. If you're lucky, maybe all that's needed
is indexing various columns to prevent full table scans. There are also
internal performance metrics you can enable and monitor to help figure
out what's going on inside Oracle.

After you have a sense of the "hot" tables, find out what disks they're
on. If they're all in the same tablespace, perhaps it's time to spread
them out to other spindles and other controllers. This might not be the
problem, but it's a good idea to help "spread the load".

These are the simple, obvious things. After this, you'll have to do
detailed analysis of Oracle's metrics, memory, I/O, and CPU to find
where the bottleneck is. This is why Oracle DBAs get the big bucks.
The good ones know their stuff and can take a mistuned database,
application and queries and increase performance dramatically.

If all this is greek to you, hire a consultant. 

-- 
DeeDee, don't press that button!  DeeDee!  NO!  Dee...