100% CPU Utilization on HP UNIX 11.11

[umeryounis]

Hi All,
We have a DB(10.2.0.3.0) running on HP UX 11.11.Their was a new service launch five days ago, due to high hits from application end towards DB, server CPU utilization went to 100% from last five days.Galance output is showing maximum processes of DB. Can you suggest some way out???like, how can we tune kernel parameters etc to reduce its CPU utilization.

Server Specs:
server model is 9000/800/rp3440.
Number of CPU's: 4
CPU Version: PA 8800 CPU Module 3.1
Clock Frequency: 1000 MHz
RAM:32GB


BR,
Umer

[raiden]

what service is that? Wasnt there any recommendations by the Service provider about the Kernel param settings.

[Turgay Cavdar]

Hi,
Check cpu usage is in user mode or kernel mode. If the high cpu usage belongs to oracle processes and this all results from new service launch, first think about tuning on application side.

[Ganesan R]

Hi,

There is no way to tune kernel parameters to bring down the cpu utilisation if the resources are consumes by application processes.

It could be due to poor coded application. You need to work with application vendor to find out if it is poorly coded or these processes really needs that much amount of cpu cycles. If those processes needs high cpu cycles, you need to upgrade the cpu's.

[umeryounis]

Thanks all for thier prompt response.
Lemme add that, application vendor has not provided any kernel parametere for tunning but we tuned kernel parameters as recommended by oracle. But issue remained the same. Actually hits from application end to DB are very high.
Thats why,application is putting so much CPU load.

[Eric Antunes]

Hi Umer,

Run a statspack report and reply with the output:

sqlplus perfstat/**** @$ORACLE_HOME/rdbms/admin/spreport.sql

If you don't have statspack installed I recomend you install it but, in the meanwhile, you can always run the following query when this high CPU utilization happens:

select s.serial#, s.process client_pid, nvl( s.action, nvl( s.username, substr( s.program, instr( s.program, '(') ))) username_or_action
, s.schemaname, s.osuser, s.status, s.lockwait, s.program, s.machine
, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#
, sw.sid, sw.wait_time, sw.seconds_in_wait, sw.event, sw.p1text, sw.p1 p1value, sw.p2text, sw.p2 p2value, sw.p3text, sw.p3 p3value
, q.module, q.sql_text
from v$session_wait sw, v$session s, v$sql q
where sw.event not like 'rdbms%'
and sw.event not like 'SQL%'
and sw.event not like '%time%'
and sw.event not like '%pipe get%'
and sw.event not like '%dle%'
and sw.event not like '%queue%'
and sw.event not like 'jobq%'
and sw.event not like 'PX Deq%'
and s.sid = sw.sid
and q.hash_value = s.sql_hash_value
and q.address = s.sql_address
order by seconds_in_wait desc

Eric

[Steven E. Protter]

Shalom,

I suggest you install the June 2009 Gold patch set into the OS.

Also I recommend you consider patching the database with patches from http://metalink.oracle.com

This behavior is fairly typical of Oracle.

SEP

[Bill Hassell]

You can create a 100% load on the CPU with a 4 line script:

while :
do
:
done

In other words, it is a trivial task to create a 100% load. To get rid of the load (as the system administrator), stop running Oracle.

The real fix is to hire an Oracle and also a specialist that knows the application. Most database issues like this are fixable with SQL and parameter changes. You cannot fix bad applications and database designs with HP-UX changes.

[Manu Singh]

Hi,
Work with developer or Sr. Oracle DBA and try to get stats from v$sql and v$sqlstats and then check it out where is the problem.It could be your disk issue as well as oracle stats,Index and may be poor code.But you can start first at disk speed and whether it is fibre channel disk or so on.
You can also look at how the fibre cable and HBA have been designed on ur server.Best way try to look into Glance and try to catch the culprit.If lot of application is hitting to same san storage disk then try to minimise this load by migrating some application to new faster SAN disk.

Thanks,
Manish