Solaris/7.x: High CPU Load: Looking for solutions & fix approach.

Hello.

Looking for further ideas and approaches towards identifying my performance problem and then ideas for solutions. If you know of anything else I should be looking at then please add it to my lists below. Thanks in advance.

I am starting to look at a Solaris/7.x performance issue where the CPU pegs in at 100% during period of heavy inserts/updates. The server is an UltraSparc SMP, 256Mb and Raid-5 disk. The database is about 30Gb with about a dozen large tables that have several indexes each. It is not a 24x7 so I can do some serious changes. I have;

• moved redo logs and archiving out of the raid-5 area into extra SCSI disk.
• Analyzed, dropped and rebuilt indexes via a nightly cronjob

I am considering the following ideas, what order should then be in, and which do people consider more profitable;

• Resize and (add or remove) redo and rollback segments.
• Export and rebuild the database with a larger than default (2k?) db_block_size of perhaps 8k. BTW: What view contains the current db_block_size?
• Analyze validate and export to test for data corruption.
• explain plan on the offending SQL
• Drop and recreate the primary key on the offending tables
• Look for table locking issues (I use TOAD)
• Adjust the SGA parameters like db_block_buffers.
• More / Suggested Solaris Tunings. BTW: Does anybody have any good Solaris/Oracle tuning guides or pointers?
• Raid-5 adjustments. Break out some disk to Raid-3 (stripe w parity) perhaps?
• There is no funding for Oracle's Performance Tuning Pack yet. Pigs could fly.....
• What else should I try and how do you do it?

Thanks in Advance -- Please also E-Mail STeve
hurrells_at_hotmail.com Received on Mon Jan 24 2000 - 10:26:46 CST