cancel
Showing results for 
Search instead for 
Did you mean: 

High CPU and PRI

Stephen Kereliuk
Occasional Visitor

High CPU and PRI

I'm running Oracle 8.1.7.3 on HP-UX 11.0 and I has a situation each day where an oracle process 'runs away' and uses 99.5% of the CPU and has a PRI (from top) of 233. This process locks the entire instance but still allows me to login to the second instance on the server. How can I find out what this process is doing in terms of SQL statement, when in essence it has the oracle instance locked?
1 REPLY
Yogeeraj_1
Honored Contributor

Re: High CPU and PRI

hi,

Below are the steps for one of the ways:

Step 1: Identify the OS PID
========
Using Glance or ps identify the OS PID of the process.

E.g.
=============================
B3692A GlancePlus
--------------------------------------------------------------------------------
CPU Util SSU U | 17% 49% 100%
Disk Util F F | 10% 46% 100%
Mem Util S SU UB B | 68% 68% 71%
Swap Util U UR R | 18% 18% 19%
--------------------------------------------------------------------------------
PROCESS LIST Users= 1
User CPU Util Cum Disk Thd
Process Name PID PPID Pri Name ( 100% max) CPU IO Rate RSS Cnt
--------------------------------------------------------------------------------
oracleyddb 5513 1 154 ora817 5.9/ 0.3 1.7 22.3/ 0.4 4.8mb 1

=============================
Here =>> 5513

Step 2: Identify the Oracle Process (SID, Serial#)
=======
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 = '5513';

Here: (212,6156)


Step 3: Trace the process
=======
exec sys.dbms_system.set_sql_trace_in_session(212,6156,TRUE);

Step 4: Identify the log being generated.
========
../app/oracle/admin/pfs/udump

e.g. ora_29291_yddb.trc

Step 5: After some time
=======
exec sys.dbms_system.set_sql_trace_in_session(212,6156,FALSE);

[this will stop the trace of the session]

Step 6: Convert the trace file into a more readable form
=======
tkprof ora_29291_yddb.trc yddb.txt


Step 7: Analyse the yddb.txt
=======

This will allow you to identify the process.

Good luck!

If you need any further clarification, please let us know.

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)