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

-- Session and query (sql) details
SELECT  s.*
 ,sa.sql_text
 ,sa.executions
 ,sa.optimizer_mode
 ,sa.disk_reads
 ,sa.buffer_gets
 ,sa.rows_processed 
 ,sa.cpu_time/1000/1000/1000  cpu_time
 ,sa.elapsed_time/1000/1000/1000  elapsed_time
FROM  v$session s, 
  v$sqlarea sa
WHERE s.sql_address = sa.address
;