OraFAQ Forum: Performance Tuning » CPU usage has been 100% consistently

Home » RDBMS Server » Performance Tuning » CPU usage has been 100% consistently

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CPU usage has been 100% consistently [message #259718]

Thu, 16 August 2007 05:49

sreehari
Messages: 101
Registered: May 2006

Senior Member

Hi all

we have a test server where QA guys run WinRunner Scripts everyday.Recently, they are complaining about the slow performance with the database/server.

When i checked the CPU usage in Windows Task Manager,i found the oracle process was consuming 99% of the CPU resource for a longer period.

The configuration of ther server is

CPU speed - 3GHZ
Database size - 10GB
RAM size - 2GB out of which 1GB for SGA

Some of the parameters i have in the database are.

open_cursors = 2000
database is dedicated server mode.
SQL> SHOW SGA
Total System Global Area 1048576000 bytes
Fixed Size 1252976 bytes
Variable Size 104858000 bytes
Database Buffers 931135488 bytes
Redo Buffers 11329536 bytes

Please let me know is there any paremeters that i can change to see the the improvement in the performance.

Thanks

Report message to a moderator

Re: CPU usage has been 100% consistently [message #259727 is a reply to message #259718]

Thu, 16 August 2007 06:12

cbruhn2
Messages: 41
Registered: January 2007

Member

Hi sreehari,

I think from what you have provided it would be pure guess working.
If I where you I would start by installing statspack and take snapshots during the run time of your queries.

You have to give much more details about version, traces and the like (look at sticky) if we should provide some meaningful advices to you.

best regards
Carl Bruhn

Report message to a moderator

Re: CPU usage has been 100% consistently [message #260708 is a reply to message #259718]

Mon, 20 August 2007 14:42

sreehari
Messages: 101
Registered: May 2006

Senior Member

I Ran ADDM and found the following info.

Host CPU consumption was 99%. CPU runqueue statistics are not available from the host's OS. This disables ADDM's ability to estimate the impact of this finding. The instance spent significant time on CPU. However, there were no predominant SQL statements responsible for the CPU load.

Can some one tell me the rootcause of this.the server was doing well untill one week ago.

Thanks in advance

Report message to a moderator

Re: CPU usage has been 100% consistently [message #261480 is a reply to message #259718]

Wed, 22 August 2007 14:18

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Carl already wrote:

Post some meaningfull info:
1. Statspack reports
2. Heavy queries,

etc.

Report message to a moderator

Re: CPU usage has been 100% consistently [message #262989 is a reply to message #259718]

Tue, 28 August 2007 13:36

vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY

Senior Member

Hi

Try to kill the oracle session which consume 99% cpu usage.

Report message to a moderator

Re: CPU usage has been 100% consistently [message #263228 is a reply to message #262989]

Wed, 29 August 2007 07:49

cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA

Senior Member

Using the select statement to view the users list by

select * from v$session order by machine;

Then Kill the session by enter serialno and audsid to kill it.

alter system kill session '13,36';

Report message to a moderator

Re: CPU usage has been 100% consistently [message #266513 is a reply to message #259718]

Tue, 11 September 2007 01:31

rupaliss
Messages: 6
Registered: July 2007
Location: Mumbai

Junior Member

Hi,

I have SAP R/3 4.7 on HP-UX 11.23 & Oracle 9i. Users are facing slow performance issues while working on these SAP servers...what are the checks at the Oracle Database level, if there is any tuning can be done to improve the overall performance. Please Help.

Its 1TB of Database.
Hit Ratio is above 95 %, Swaps - 0%
CPU Idle time above 30-40%
Response Time - 6-10 ms

When I tried to run DB Check through DB16, I got some warnings & Errors...
IN_WRONG_TABLESPACE
MISSING_INDEX
MISSING_STATISTICS
TABLESPACE_FULL
ARCHIVE_TOO_OLD
BACKUP_TOO_OLD
DB_WRITER_PROCESSES
LOG_CHECKPOINT_INTERVAL
OPTIMIZER_FEATURES_ENABLE
01555
TSP_FULL
60
Checkpoint not complete

Thanx
Rupali S

Attachment: statsco.xls
(Size: 92.00KB, Downloaded 1980 times)

Report message to a moderator

Previous Topic:	Query running slow .. a day before it was fine
Next Topic:	How many connection are on line

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 07:30:25 CST 2024