Home » RDBMS Server » Performance Tuning » CPU usage has been 100% consistently
CPU usage has been 100% consistently [message #259718] Thu, 16 August 2007 05:49 Go to next message
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




Re: CPU usage has been 100% consistently [message #259727 is a reply to message #259718] Thu, 16 August 2007 06:12 Go to previous messageGo to next message
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

Re: CPU usage has been 100% consistently [message #260708 is a reply to message #259718] Mon, 20 August 2007 14:42 Go to previous messageGo to next message
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
Re: CPU usage has been 100% consistently [message #261480 is a reply to message #259718] Wed, 22 August 2007 14:18 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Carl already wrote:

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

etc.
Re: CPU usage has been 100% consistently [message #262989 is a reply to message #259718] Tue, 28 August 2007 13:36 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member


Hi

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


Re: CPU usage has been 100% consistently [message #263228 is a reply to message #262989] Wed, 29 August 2007 07:49 Go to previous messageGo to next message
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';
Re: CPU usage has been 100% consistently [message #266513 is a reply to message #259718] Tue, 11 September 2007 01:31 Go to previous message
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 1981 times)
Previous Topic: Query running slow .. a day before it was fine
Next Topic: How many connection are on line
Goto Forum:
  


Current Time: Fri Dec 06 16:55:49 CST 2024