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

From: <karsten_schmidt8891_at_my-deja.com>
Date: Tue, 25 Jan 2000 13:44:50 GMT
Message-ID: <86k9ch$b18$1@nnrp1.deja.com>

Hi,

look at the parse/execute ratio for your statements: parsing costs cpu.

are you using triggers ? stored procedures ? some 7.x releases used to re-compile triggers each time they are executed.

the changes you are considering are mostly targeted at disk i/o. this won't help if the database is cpu-bound.

do you know, which processes are using most cpu time peak/elapsed ?

are you using software raid ? - this might account for cpu-usage as well.

Karsten

In article <388C7D46.7A254FF7_at_hotmail.com>, Stephen Hurrell <hurrells_at_hotmail.com> wrote:
>
> --------------A419D1327AD05E43CEE1CF2B
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> 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
>
> --------------A419D1327AD05E43CEE1CF2B
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> <html>
> 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;
> <ul>
> <li>
> moved redo logs and archiving out of the raid-5 area into extra SCSI
disk.</li>
>
> <li>
> Analyzed, dropped and rebuilt indexes via a nightly cronjob</li>
> </ul>
> I am considering the following ideas, what order should then be in,
and
> which do people consider more profitable;
> <ul>
> <li>
> Resize and (add or remove) redo and  rollback segments.</li>
>
> <li>
> 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?</li>
>
> <li>
> Analyze validate and export to test for data corruption.</li>
>
> <li>
> explain plan on the offending SQL</li>
>
> <li>
> Drop and recreate the primary key on the offending tables</li>
>
> <li>
> Look for table locking issues (I use TOAD)</li>
>
> <li>
> Adjust the SGA parameters like db_block_buffers.</li>
>
> <li>
> More / Suggested Solaris Tunings. BTW: Does anybody have any good
Solaris/Oracle
> tuning guides or pointers?</li>
>
> <li>
> Raid-5 adjustments. Break out some disk to Raid-3 (stripe w parity)
perhaps?</li>
>
> <li>
> There is no funding for Oracle's Performance Tuning Pack yet. Pigs
could
> fly.....</li>
>
> <li>
> What else should I try and how do you do it?</li>
> </ul>
> Thanks in Advance -- Please also E-Mail
> STeve
> hurrells_at_hotmail.com
>  </html>
>
> --------------A419D1327AD05E43CEE1CF2B--
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jan 25 2000 - 07:44:50 CST