Find the CPU intensive SQL Query : UNIX Level Mapping of PID and SPID

The process to find the CPU intensive query requires UNIX as well as Database level mapping.

Step 1: The first step is to find the PID of the most CPU consuming process from UNIX level.

SQL> ! prstat -s cpu -n 5

 PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
 633 oracle 8617M 8350M cpu0 0 0 1:53:13 1.6% oracle/13
 4782 oracle 8614M 8348M cpu33 0 0 2:35:20 1.6% oracle/14
 740 oracle 8618M 8351M cpu46 0 0 2:44:13 1.6% oracle/11
 812 oracle 8617M 8349M cpu55 0 0 1:33:05 1.6% oracle/11
 4720 oracle 8583M 8318M sleep 31 0 9:11:13 0.2% oracle/15
Total: 188 processes, 3185 lwps, load averages: 5.52, 9.14, 10.79

Step 2: The next step is to combine two views ie v$session and v$process substituting the value of PID obtained in SPID field.

SQL> select a.serial#, sid, a.serial#, b.username from v$session a, v$process b where b.spid=633 and a.paddr=b.addr;

SERIAL# SID SERIAL# USERNAME
---------- ---------- ---------- ---------------
 17988 501 17988 oracle

Step 3: The third step is to find SQL_ID from V$session by subsituting SERIAL#

SQL> select SQL_HASH_VALUE, SQL_ID, SID from v$session where SERIAL#=17988 ;

SQL_HASH_VALUE SQL_ID SID
-------------- ------------- ----------
 3405470801 c7nmxy85gqr2j 501

The above SQL query should be the culprit for consuming high CPU. You can verify the same using other similar queries.

Other queries which does the same mapping function:

ps -eaf -o pcpu,pid,user,tty,comm | grep ora |
 grep -v \/sh | grep -v ora_ | sort -r | head -20

column username format a9
 column sql_text format a70
SELECT a.username, b.sql_text
 FROM v$session a, v$sqlarea b, v$process c
 WHERE (c.spid = '&PID' OR a.process = '&PID')
 AND a.paddr = c.addr
 AND a.sql_address = b.address
 /

select p.SPID UnixProcess ,s.SID,s.serial#,s.USERNAME,s.COMMAND,s.MACHINE,s.SQL_ADDRESS,s.SQL_HASH_VALUE ,s.program, status, cpu_time,fetches,disk_reads,buffer_gets,rows_processed,executions,child_latch,event, sql_text,COMMAND_TYPE from gv$session sleft outer join gv$process p on p.ADDR = s.PADDR and s.inst_id=p.inst_id left outer join gv$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS and s.inst_id=sa.inst_id where p.spid=

SELECT a.username, a.osuser, b.spid
 FROM v$session a, v$process b
 WHERE a.paddr = b.addr
 AND a.username IS NOT null;

select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
 i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
 s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
 w.seconds_in_wait "Idle Time", P.SPID "PROC",
 name "Stat CPU", value
 from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
 where s.sid = i.sid
 and s.sid = w.sid (+)
 and 'SQL*Net message from client' = w.event(+)
 and s.osuser is not null
 and s.username is not null
 and s.paddr=p.addr
 and n.statistic# = t.statistic#
 and n.name like '%cpu%'
 and t.SID = s.sid
 order by 6 asc, 3 desc, 4 desc;