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 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?
Honored Contributor

Re: High CPU and PRI


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.

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%
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.

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.

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