CODEWITHSUNDEEP
oracle-databaseprocess
DarkTranquility
DarkTranquility

Reputation: 13

How to find out what a oracle process is currently doing

I have an Oracle process which is running with 100% CPU since hours.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 75508 oracle 20 0 102.1g 1.7g 39284 R 100.0 1.1 399:52.21 oracle_75508_mp

Is there any possibility to show what especially this process is currently doing?

Thanks in advance :)

Regards, Steffen

Upvotes: 0

Views: 3864

Answers (1)

Paul C.
Paul C.

Reputation: 177

You should be able to find out everything you need to know with the help of V$SESSION and V$PROCESS.

It seems that you already know your PID. With that PID, you can use the following SELECT Statement and identify the SQL_ID.

select sid,serial#,username,sql_id,schemaname,osuser,process,machine,port,program,type,paddr
from v$session
where PADDR = (select ADDR from V$PROCESS where SPID = 'YOURPID')

With the SQL_ID known, you can go and retrieve the performed action from within V_$SQLAREA. This system view will provide you with the exact statement which is currently running.

SELECT * FROM SYS.V_$SQLAREA WHERE sql_id='SQL_ID_FROM_THE_PREVIOUS_SELET';

Upvotes: 1

Related Questions