High CPU and PRI

[Stephen Kereliuk]

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?

[Yogeeraj_1]

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