Oracle - solving performance problem

I am a sysadmin tasked with developing a solution to improve our Oracle 10g performance. I inherited the following server and problem. I'm not a DBA, so if my terminology is off please forgive me, if you are kind enough to help but need more info, I can get you that info.

Server Spec:
Red Hat Enterprise Server 3.0 2.4.21-27.0.4.ELsmp Dual Intel Xeon CPU @ 3.00GHz w/ hyper threading 12 gigs of RAM
3 x RAID 5 arrays with 7 disks each, attached externally. Each array has its own SCSI channel.

Problem:
We have a 230 gig database that contains 101 million rows. Indexes are placed on mount point/SCSI channel, data on another mount point/SCSI channel. I'm told that the size of this database can't be changed by our development staff. In fact it will be growing by about 20-30% each year. Certain queries are taking a dogs age to run. One is a mass load of data each day, 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.

Things we have tried:
- Enable and use Async I/O

• Increase SGA to 2.7, jump to 3.7 not done yet.
• Upgrade SCSI driver, saw some minor improvement
• Other minor DB/query changes

Consultant was hired to look at our environment they recommended:
- We need 5-10% of db size in SGA, therefore 23 gigs of SGA for today's
size.
- 64 bit architecture, to allow for huge SGA

• Scrap the RAID 5 arrays and build RAID 10 arrays instead, more platters the better.
• Recommended some query and index changes, the fixes will help, but won't be overly significant.

I need to figure out what we can do to achieve our goals and have it last at least 2 years of increasing data. I've been researching the following.

• Change hardware platform to a large Sun box that can take more than 32 gigs of RAM running Solaris. (v890 or something)
• Some type of high performance platform from IBM with AIX
• Intel/Sun Oracle grid with several nodes, blade server perhaps
• Scrap SCSI and use a fiber channel SAN instead, in standalone or Grid setup.

Anyone have any opinions on the best course of action? How should one handle this amount of data and maintain performance?

Grid seems like a good option, but perhaps not necessary because I/O is our problem, not really CPU. Dumb question, but how do multiple grid members access the same data efficiently? Can the SGA be spread around the members of the Grid .Any caveats?

Thanks for any advice you may be able to provide.

Mark Received on Fri Oct 07 2005 - 16:42:52 CDT