Server Utilization and Bottleneck spotting.

Hi all,
I've got a fairly beefy server running oracle and I'm wondering how to tell what part of the system is slowing things down. The performance is generally ok, but I almost never see the cpu usage above around 5%. Occassionally I'll see a spike up to 20% or so.

My assumption is that this is because it's a database and the bottleneck is the disk access. We are running RAID 10 though so it should be pretty quick. How do I diagnose this sort of thing.

Is there some way I can work out the maximum throughput of the RAID and compare it to what is actually being utilized?

Cheers,
Peter

This is a problem requiring a two-prong attack (I'm assuming unix):

You first want to look at I/O load balancing. You want to see numbers of I/O's spread over volumes, not all clumped on one volume.

Use sar, iostat, or glance (whatever tools you OS has) to look at loads on disks.
If there is a big I/O wait queue for a given volume, you need to split the tablespaces on that volume into several pieces, and spread them across volumes or even filesystems.

Next, You need to see what is going on in Oracle itself. Bad queries and opening a cursor that locks many rows all at once (like select * from table for update; ) all have very negative impacts. I can virtually lock a huge Oracle system by doing something dumb - and on purpose. There are a lot of tools to allow a dba to monitor how various Oracle resources are being used. I would strongly suggest Tom Kyte's Oracle books.
I don't know which kernel you have but try Amazon for starters. He has an 'Expert' series.

Also check his site if you need something in particular:
http://asktom.oracle.com/pls/ask

The server is running on a Windows 2000 server. I haven't been able to find much for measuring drive load.

Any suggestions?

If you are having Oracle performance issues - most issues like that arise from code written by programmers who know SQL and don't know Oracle.

There are products like Equalizer that do load balancing for you, if it's a problem. My experience with vendors is they like to find a problem they can fix....

You can balance load yourself by moving tablespaces around, or spreading tablespaces that get a lot of traffic across disk volumes. You can also look into clustering or partioning really big tables and putting them into their own tablespace.

Please note: I have not Oracle/Windows stuff for about 6 years.... take that into consideration.

I'm not having specific performance issues. As stated in the original post, performance is generally ok.

What I want to know is where the current load is taking place.

Is it not using much CPU because I've got a setting somewhere saying only use so much, or is it being limited by the hard drive.

How fast can I write read/data to the hard drive. How does this compare to the theoretical maximum for the setup I have. How often does oracle peak out on hard drive throughput.

I'm not trying to solve a specific issue here. I'm looking for tools to help me diagnose where we are at as far as server utilization is concerned. Am I going to need a new server sometime soon or will the current one be fine for another few years.

Are you running the mmc console - that's only way I know of to gather statistics on I/O loads