Oracle – Find sql Id from process id

If CPU usage high due to Oracle Process, then we can use respective PID to identify SQL id, SQL_text and much more.

Query-

SELECT SQL_TEXT

FROM v$sql

WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (SELECT addr

FROM v$process

WHERE spid = '&process_id'));

We can find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

To get more detail we can use below query-

SELECT    'USERNAME : ' || s.username  || CHR (10)   

        || 'SCHEMA : '  || s.schemaname || CHR (10) 

        || 'OSUSER : '  || s.osuser    || CHR (10)  

        || 'PROGRAM : ' || s.program   || CHR (10)  

        || 'MACHINE : ' || s.machine   || CHR (10)  

        || 'TERMINAL : ' || s.terminal  || CHR (10)  

        || 'SPID : '    || p.spid      || CHR (10)  

        || 'SID : '     || s.sid       || CHR (10)  

        || 'SERIAL# : ' || s.serial#   || CHR (10)  

        || 'TYPE : '    || s.TYPE      || CHR (10)  

        || 'SQL ID : '  || q.sql_id    || CHR (10)  

        || 'CHILD_NUMBER : '  || q.child_number    || CHR (10)  

        || 'SQL TEXT : ' || q.sql_text 

          RESULT

  FROM v$session s, v$process p, v$sql q

 WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';