How to find out what a oracle process is currently doing

Question

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

question from:https://stackoverflow.com/questions/65887108/how-to-find-out-what-a-oracle-process-is-currently-doing

Answer 1

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';