top resources consumption

SELECT
   s.username,
   t.sid,
   s.serial#,
   s.SQL_ADDRESS,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#,s.SQL_ADDRESS
order by 5 desc

/* ---------------------------------------------------

   select *
   from
   v$sql s
   where s.ADDRESS='00000010BEC15AA8'

--------------------------------------------------- */