General
cancel
Showing results for 
Search instead for 
Did you mean: 

CPU consumption by one oracle process

SOLVED
Go to solution
Christian Marquardt_1
Regular Advisor

CPU consumption by one oracle process

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
6 REPLIES
jpcast_real
Regular Advisor

Re: CPU consumption by one oracle process

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
Here rests one who was not what he wanted and didn't want what he was
Yogeeraj_1
Honored Contributor
Solution

Re: CPU consumption by one oracle process

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Christian Marquardt_1
Regular Advisor

Re: CPU consumption by one oracle process

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
Eric Antunes
Honored Contributor

Re: CPU consumption by one oracle process

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
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: CPU consumption by one oracle process

Hi Christian,

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

thanks
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
ram chaitanya muppalla_1
Occasional Advisor

Re: CPU consumption by one oracle process

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