topic Re: CPU consumption by one oracle process in Operating System - HP-UX

CPU consumption by one oracle process

Christian Marquardt_1 — Thu, 01 Sep 2005 00:44:24 GMT

Hello,
I've a problem on our HP/UX 11.00 machine running a oracle 9.2.0.5.0 database. There's one oracle process from the database showing almost 100% user time on one cpu (there are 4 cpu's).
Running tusc on the process' pid the logfile doesnt contain some infos. Tracing the process with oradebug doesnt create a tracefile. It seems the process is doing nothing but getting 100% user time from one cpu.
Can anyone help me find out the reason of this cpu consumption?

regards
Christian

Re: CPU consumption by one oracle process

jpcast_real — Thu, 01 Sep 2005 00:49:57 GMT

I would suggest that probably someone is accessing the database and making a big query . You can have a look and see if this is an oracle process launched by the oracle listener . I suggest also to use to free tool lsof to know where does the process comes from

Re: CPU consumption by one oracle process

Yogeeraj_1 — Thu, 01 Sep 2005 01:20:57 GMT

hi,

On the Oracle Database side, you can do the following:

a. Find more information about the Oracle session linked to the OS process id (OSPID)

e.g.
select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info from v$session b, v$process c where b.paddr = c.addr and c.sPID = &OSPID

b. Trace the session and get more information about what statements it is running.

At the OS level, you can use glance to further drill down into what resources the process is using.

hope this helps!

regards
yogeeraj

Re: CPU consumption by one oracle process

Christian Marquardt_1 — Thu, 01 Sep 2005 03:23:04 GMT

Hi yogeeraj,
I've traced the session and there's nothing in the trace files for further information. There are no statements or other things ... the process has nothing to do but get 100% cpu time.
I've tried to trace it with TUSC (almost equal to glance) and with the Oracle oradebug tool. There are no informations in any trace files.
Maybe the HP/UX system "thinks" the process is running some statements and give some cpu ressources.

regards
Christian

Re: CPU consumption by one oracle process

Eric Antunes — Thu, 01 Sep 2005 05:57:46 GMT

Hi Christian,

Can you post the output of the following SQL script:

select s.username "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
s.program "Program",
s.machine "Machine",
s.logon_time "Connect Time",
p.program "P Program",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by status,si.consistent_gets+si.block_gets desc

Best Regards,

Eric Antunes

Re: CPU consumption by one oracle process

Yogeeraj_1 — Thu, 01 Sep 2005 06:17:24 GMT

Hi Christian,

can you please post the output of the SQL statement i mentioned above as well as that of Eric?

thanks
yogeeraj

Re: CPU consumption by one oracle process

ram chaitanya muppalla_1 — Fri, 02 Sep 2005 02:44:07 GMT

Hello Christian,

Firstly, please do run the sql statement provided by members, as it provides us more finer details on wht's happening.

Then, check if the process is a foreground process i.e user process or a background process and if it's waiting for some resource.

If it's a user process then, I believe u know..

If it's a background process, then, I guess it may be due to a bug - if this remains constant (need to work with oracle support).

Further these questions are popping up in my mind.

Is this a new db instance or an existing one.. if existing for sometime, does it occur frequently or it's the first time?

Do you have any more db's running of the same box? If yes, do they have a similar occurance?

Any new OS patches or oracle patches are installed on this box recently?

If it's NOT a PRODUCTION m/c and u can try to bounce and see if it's still persistent.

Normally, it's due to a user process who is running a query retrieving huge amount of data and sometimes it can be even a bug as well.

That's my two cents.

Regards,
Ram