Session monitoring

Active sessions usage by CPU

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds,
   ss.machine
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc

SELECT
   s.username,
   t.sid,
   s.serial#,
   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#

Top10 Active sessions

/* top10active.sql
shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132
set verify off
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'ACTIVE'
order by 4 desc)
where rownum < 11;

Session/SQL monitoring

SELECT SESION.SID,
       SESION.SERIAL#,
       SESION.USERNAME,
       OPTIMIZER_MODE,
       HASH_VALUE,
       ADDRESS,
       CPU_TIME/1000000000,
       ELAPSED_TIME/1000000000,
       DISK_READS,
       --DIRECT_WRITES,
       SQL_TEXT
  FROM V$SQLAREA SQLAREA, V$SESSION SESION
 WHERE SESION.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
   AND SESION.SQL_ADDRESS    = SQLAREA.ADDRESS
   AND SESION.USERNAME IS NOT NULL
   AND ROWNUM < 100
   ORDER BY CPU_TIME, DISK_READS DESC,ELAPSED_TIME DESC;