increasing ram to counter slow query

Printable View

05-03-2005, 03:24 AM
yls177

increasing ram to counter slow query

Hi

Recently, the users experience slowness in running oracle queries. Without hesitation, my management decided to go ahead with getting more ram.

1) With more ram, will the slowness go away?

2) even with more ram, one still need to configure , ie, the shared pool, data buffer,redo buffer in order to take advantage of this increase in memory?

3) anyway, i think the crux is not the hardware but rather the examiniation of the slowness. Without the latter, my management went straight away to getting more ram. A more realistic method will be getting more CPUs instead.

Please advise.
05-03-2005, 04:06 AM
davey23uk

why is more CPU's realistic? It's as stupid an idea as more memory is.

You may be i/o bound. How about finding the cause of the problem and fixing it, which is surely where you come in?
05-03-2005, 04:13 AM
DaPi

If I remember correctly, Cary Millsap gives an example in his book where upgrading hardware made things run slower!

Do as davey23uk says - find the cause of the problem.
05-03-2005, 04:18 AM
KenCunningham

You could maybe try analyzing your database on a regular basis too. This has had a dramatic effect on a couple of our applications. There may be other tools available to investigate at the OS level, but what OS is it?
05-03-2005, 04:29 AM
abhaysk

pick up few slow running queries and i am sure u will see bad plans for those..

Abhay.
05-03-2005, 06:59 AM
KenEwald

I agree to a facts-driven, knowledge-based systematic approach to tuning.

Throwing hardware or regularly computing statistics "at a problem" will fix the problem some times, but knowing where your bottleneck is will save your career.

Buy a book on tuning your specific version. Read it and apply it.

-Ken

PS. Sometimes adding memory and caching a table is the right approach.
05-03-2005, 08:38 AM
marist89

Re: increasing ram to counter slow query

Quote:

Originally posted by yls177
Hi

Recently, the users experience slowness in running oracle queries. Without hesitation, my management decided to go ahead with getting more ram.

1) With more ram, will the slowness go away?

and if it does, what does that mean the next time?

Quote:

2) even with more ram, one still need to configure , ie, the shared pool, data buffer,redo buffer in order to take advantage of this increase in memory?

Unless you have paging/swapping problems.

Quote:

3) anyway, i think the crux is not the hardware but rather the examiniation of the slowness. Without the latter, my management went straight away to getting more ram. A more realistic method will be getting more CPUs instead.

How so? If you don't know what the problem is how can you suggest more hardware?
05-03-2005, 08:41 AM
slimdave

I can't believe that I'm the first to say "statspack"! world's smnallest hooray for me.

But anyway, get statspack running for a fifteen minute interval when the database appears to be under pressure, then pull the top SQL's from it. You could easily have a handful of badly tuned SQL in there that is the source of 90% of your problems.