Oracle CPU Analysis
-- CPU used by this session

SELECT
 s.sid, s.command, s.username, s.program, ss.value  cpu_usage,  
 TO_CHAR(s.logon_time,'mm/dd/yyyy hh24:mi:ss')  logon_time
FROM v$session s
JOIN v$sesstat ss
  ON s.sid = ss.sid
WHERE ss.statistic# IN 
  (SELECT  statistic#
   FROM    v$statname
   WHERE   INSTR(name,'CPU used by this session')>0
  )
AND  s.username IS NOT NULL
AND  s.sid > 6
ORDER BY s.sid
;


WITH q_cpu AS
(SELECT
   sql_text,
   cpu_time/1000/1000/1000  cpu_time,
   elapsed_time/1000/1000/1000  elapsed_time,
   disk_reads,
   buffer_gets,
   rows_processed 
 FROM v$sqlarea
 ORDER BY cpu_time DESC, disk_reads DESC
)
SELECT *
FROM  q_cpu
WHERE cpu_time >= 1
;


Sever (Unix/Linux) CPU Analysis

CPU Analysis Queries

Oracle registered trademark of Oracle Corporation.

Last Revised On: February 20th, 2015

  381