RIYOSU

SELECT
sess.inst_id,
sess.sid,
sess.serial#,
sess.sql_id||'::'||vsa.plan_hash_value,
sess.STATUS,
sess.username,
sess.type,
sess.program,
sum(case when statn.name =  'CPU used by this session' then nvl(sql.value/1000000,0) end) as "cpu",
sum(case when statn.name  = 'user I/O wait time' then nvl(sql.value/1000000,0) end) as "I/O",
sum(case when statn.name  = 'application wait time' then nvl(sql.value/1000000,0) end) as "App",
sum(case when statn.name  = 'concurrency wait time' then nvl(sql.value/1000000,0) end) as "Conc",
sum(case when statn.name  = 'cluster wait time' then nvl(sql.value/1000000,0) end) as "Clus",
sum(case when statn.name  = 'redo synch time' then nvl(sql.value/1000000,0) end) as  "RedoSync",
sum(case when statn.name  = 'parse time elapsed' then nvl(sql.value/1000000,0) end) as "Parse"
FROM gV$SESSION sess, gv$sesstat sql, gv$sqlarea vsa, gv$statname statn
where
Sess.AUDSID <> userenv('SESSIONID') and
sess.inst_id = sql.inst_id and
sess.sid = sql.sid and
sql.statistic# = statn.statistic# and
statn.name in
(
'CPU used by this session',
'cluster wait time',
'concurrency wait time',
'application wait time',
'user I/O wait time',
'redo synch time',
'parse time elapsed') and
sess.sql_address=vsa.address(+) and
sess.inst_id = vsa.inst_id(+)
group by
sess.inst_id,
sess.sid,
sess.serial#,
sess.sql_id||'::'||vsa.plan_hash_value,
sess.STATUS,
sess.username,
sess.type,
sess.program
having sess.status = max(sess.status)
order by sess.inst_id,
sess.sid,
sess.serial#

Download Speedway to graphically plot the above script results.