CPU consumption by one oracle process

[Christian Marquardt_1]

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

[jpcast_real]

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

[Yogeeraj_1 · Accepted solution]

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

[Christian Marquardt_1]

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]

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

[Yogeeraj_1]

Hi Christian,

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

thanks
yogeeraj

[ram chaitanya muppalla_1]

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